我正在撰寫一個 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 <> '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;
我注意到WITH子句與 the的組合EXECUTE總是會觸發 error syntax error at or near EXECUTE，即使它們非常簡單明了。情況確實如此，如果是這樣，提供所需變數（updated_vals,table_cols和updated_cols）的替代方法是EXECUTE什么？
如果您對此代碼有任何其他改進，我很高興看到這些改進，因為我對 sql/plpgsql 非常陌生。
uj5u.com熱心網友回復：
如果您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$
;
uj5u.com熱心網友回復：
對于動態表：
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$
;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/344803.html
標籤：sql PostgreSQL 功能
下一篇：Dart什么是用于擴展或實作dart內置型別的具體類，例如Float32List、Int32List、Uint8List等