In a SET assignment, can you use a variable to programmatically define the columnname that should be SET?

user_8534291 0
Viewed 55

Can someone help me with the syntax of the following function?

In short, I want to update an existing JSON array with a new JSON entry in a specified column, with a matching ID ('projectid'). This works when the column name (e.g. my_column) is specified in SET, like this:

create function append_entry_to_dev (projectid int, entry jsonb) 
returns void as 
$$
  update projects_develop
  set my_column = coalesce(my_column, '[]'::jsonb) || to_jsonb(entry)
  where id = projectid
  returning *;
$$ 
language sql;

However, I'd like it if the column name in SET can be specified programmatically when the data is added, something like this — note the columnname is part of the call:

create function append_entry_to_dev (projectid int, entry jsonb, columnname text) 
returns void as 
$$
  update projects_develop
  set columnname = coalesce(columnname, '[]'::jsonb) || to_jsonb(entry)
  where id = projectid
  returning *;
$$ 
language sql;

When I try doing this, I get the error column "columnname" does not exist

If what I'm thinking is possible, can someone help me with the syntax to make it work as expected?

dynamic-sql postgresql sql
2 Answers

Dynamic sql in a plpgsql function will do the job, and BTW returning * does not do much in a function that returns void. I did not look at the logic of your function and tried to keep it as close to your one as possible.

create or replace function append_entry_to_dev (projectid int, entry jsonb, columnname text) 
returns void as 
$body$
declare 
 dynsql constant text := 
 $dynsql$
   update projects_develop
   set %3$I = coalesce(%3$I, '[]'::jsonb) || to_jsonb(%2$L)
   where id = %1$s;
 $dynsql$;
begin
    execute format(dynsql, projectid, entry, columnname);
end;  
$body$ language plpgsql;
user_23020320

If you don't want dynamic SQL then an ugly but possible way exists:

/*
CREATE TEMP TABLE project_develop(
    prj_id int,
    col_a jsonb,
    col_b jsonb,
    col_c jsonb
);
*/

CREATE OR REPLACE function append_entry_to_dev (projectid int, entry jsonb, columnname text) 
returns void as 
$$
UPDATE project_develop PD
    SET 
        col_a = 
            CASE
                WHEN columnname <> 'col_a' THEN col_a
                ELSE coalesce(col_a, '[]'::jsonb) || to_jsonb(entry)
            END,             
        col_b = 
            CASE
                WHEN columnname <> 'col_b' THEN col_b
                ELSE coalesce(col_b, '[]'::jsonb) || to_jsonb(entry)
            END,
        col_c = 
            CASE
                WHEN columnname <> 'col_c' THEN col_c
                ELSE coalesce(col_c, '[]'::jsonb) || to_jsonb(entry)
            END        
    WHERE
        PD.prj_id  = projectid;
$$ 
language sql;

It's for you to decide whether it's appropriate for your case.

Note that:

  • all possible column names must be defined
  • it always will update all fields even if they are not changed

IDK about more elegant way for doing this.

user_11682120
Related