Apply db model changes to booking database

[ngjoh]

create a patch method for each table

To create a patch variant of your proc.update_desk function that selectively updates fields based on an array of name/value pairs, you can modify your function to loop through the array and dynamically update only the specified fields.

Here's how you can structure the patch function:

CREATE OR REPLACE FUNCTION proc.patch_desk(
    p_actor_name character varying,
    p_id integer,
    p_fields jsonb,
    p_koksmat_sync jsonb DEFAULT NULL::jsonb
) RETURNS jsonb
LANGUAGE plpgsql
AS $function$
DECLARE
    v_rows_updated INTEGER;
    v_query TEXT;
    v_param_name TEXT;
    v_param_value TEXT;
    v_set_clause TEXT := '';
BEGIN
    -- Raise a notice with actor and input
    RAISE NOTICE 'Actor: % Input: %', p_actor_name, p_fields;

    -- Loop through the fields to build the dynamic SET clause
    FOR v_param_name, v_param_value IN
        SELECT key, value::text
        FROM jsonb_each(p_fields)
    LOOP
        -- Dynamically build the SET clause
        v_set_clause := v_set_clause || format('%I = %L,', v_param_name, v_param_value);
    END LOOP;

    -- Remove the trailing comma from the SET clause
    v_set_clause := rtrim(v_set_clause, ',');

    -- Build the final query
    v_query := format('UPDATE public.desk SET %s, updated_by = %L, updated_at = CURRENT_TIMESTAMP WHERE id = %L',
                      v_set_clause, p_actor_name, p_id);

    -- Execute the dynamic query
    EXECUTE v_query;

    -- Get the number of rows updated
    GET DIAGNOSTICS v_rows_updated = ROW_COUNT;

    -- If no rows were updated, raise an exception
    IF v_rows_updated < 1 THEN
        RAISE EXCEPTION 'No records updated. desk ID % not found', p_id;
    END IF;

    -- Return success
    RETURN jsonb_build_object(
        'comment', 'patched',
        'id', p_id
    );
END;
$function$;

Key Changes:

1. Input Parameters:

   • p_id: The id of the desk to update.
   • p_fields: A JSONB object containing name/value pairs that specify which fields to update.

2. Dynamic SQL:

   • The function constructs a SET clause dynamically based on the name/value pairs provided in p_fields.
   • It uses jsonb_each to iterate through the key-value pairs in the JSON object.

3. Execution:

   • The dynamic SET clause is used to update only the fields provided in p_fields.

This function allows for partial updates to the desk table by specifying only the fields you want to change.