Updating table based on JSON inside PostgreSQL function-CodePudding

I am writing a plpgsql function that should update a table based on a provided JSON object. The JSON contains a table representation with all the same columns as the table itself has.

The function currently looks as follows:

CREATE OR REPLACE FUNCTION update (updated json)

BEGIN

/* transfrom json to table */
WITH updated_vals AS (
    SELECT
        *
    FROM
        json_populate_recordset(NULL::my_table, updated)
),

/* Retrieve all columns from mytable and also with reference to updated_vals table */
cols AS (
    SELECT
        string_agg(quote_ident(columns), ',') AS table_cols,
        string_agg('updated_vals.' || quote_ident($1), ',') AS updated_cols
    FROM
        information_schema
    WHERE
        table_name = 'my_table' -- table name, case sensitive
        AND table_schema = 'public' -- schema name, case sensitive
        AND column_name <> 'id' -- all columns except id and user_id
        AND column_name <> 'user_id'
),

/* Define the table columns separately */
table_cols AS (
    SELECT
        table_cols
    FROM
        cols
),

/* Define the updated columns separately */
updated_cols AS (
    SELECT
        updated_cols
    FROM
        cols)

/* Execute the update statement */
    EXECUTE 'UPDATE my_table'
    || ' SET (' || table_cols::text || ') = (' || updated_cols::text || ') '
    || ' FROM updated_vals '
    || ' WHERE my_table.id = updated_vals.id '
    || ' AND my_table.user_id = updated_vals.user_id';

COMMIT;
END;

I noticed that the combination of the WITH clause combined with the EXECUTE will always trigger the error syntax error at or near EXECUTE, even if those are very simple and straightforward. Is this indeed the case, and if so, what would be an alternative approach to provide the required variables (updated_vals, table_cols and updated_cols) to EXECUTE?

If you have any other improvements on this code I'd be happy to see those for I am very new to sql/plpgsql.

CodePudding user response：

If you wrote table name (my_table) in your function, this means that you will update always only one specified table from JSON data. Because of this, you can write table names and column names in your function manually, not using information_schema. This is the simple and easy way.

For example:

CREATE OR REPLACE FUNCTION rbac.update_users_json(updated json)
RETURNS boolean 
LANGUAGE plpgsql
AS $function$
begin

    update rbac.users usr 
    set 
        username    = jsn.username, 
        first_name  = jsn.first_name, 
        last_name   = jsn.last_name
    from (
        select * from json_populate_recordset(NULL::rbac.users, updated)
    ) jsn  
    where jsn.id = usr.id;
    
    return true;    
   
END;
$function$
;

CodePudding user response：

For dynamic tables:

CREATE OR REPLACE FUNCTION rbac.update_users_json_dynamic(updated json)
RETURNS boolean  
LANGUAGE plpgsql
AS $function$
declare
    f record;
    exec_sql text;
    sep text;
begin

    exec_sql = 'update rbac.users usr set ' || E'\n';
    sep = '';

    for f in 
        select clm.column_name 
        from 
            information_schema."tables" tbl
        inner join 
            information_schema."columns" clm on 
                        clm.table_name = tbl.table_name 
                        and clm.table_schema = tbl.table_schema 
        where 
            tbl.table_schema = 'test' 
            and tbl.table_name = 'users'
            and clm.column_name <> 'id'
    loop 
        exec_sql = exec_sql || sep || f.column_name || ' = ' || 'jsn.' || f.column_name;
        sep = ', ' || E'\n';
    end loop;
   
    exec_sql = exec_sql || E'\n' || 'from (select * from json_populate_recordset(NULL::rbac.users, ''' || 
                updated::text || ''')) jsn ' || E'\n' || 'where jsn.id = usr.id';
    
    execute exec_sql;

    return true; 
   
END;
$function$
;