Блог

Я пишу функцию plpgsql, которая должна обновлять таблицу на основе предоставленного объекта JSON. JSON содержит представление таблицы со всеми теми же столбцами, что и в самой таблице.

В настоящее время функция выглядит следующим образом:

 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 lt;gt; 'id' -- all columns except id and user_id  AND column_name lt;gt; '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;  

Я заметил , что комбинация WITH предложения в сочетании с the EXECUTE всегда будет вызывать ошибку syntax error at or near EXECUTE , даже если они очень просты и понятны. Действительно ли это так, и если да, то каков был бы альтернативный подход для предоставления требуемых переменных ( updated_vals , table_cols и updated_cols ) EXECUTE ?

Если у вас есть какие-либо другие улучшения в этом коде, я был бы рад их увидеть, потому что я очень новичок в sql/plpgsql.

Если вы написали имя таблицы ( my_table ) в своей функции, это означает, что вы всегда будете обновлять только одну указанную таблицу из данных JSON. Из-за этого вы можете записывать имена таблиц и столбцов в свою функцию вручную, не используя information_schema . Это простой и легкий путь.

Например:

 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$ ;  

Для динамических таблиц:

 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 lt;gt; '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$ ;