我有一个PostgreSQL数据库，我需要对特定列的值进行更新。 列的数量非常大，我需要对不同的表执行相同的操作，所以更好地动态提取它们。
更具体地说，我想从表中提取名称以“_suffix”结尾的所有列，并对它们的值进行更新。 我开始尝试制作剧本，但我不知道这是否是正确的道路！
SELECT columns.column_name FROM information_schema.columns WHERE columns.table_name = 'myInitialTable' AND columns.column_name like '%\_suffix%' AND columns.table_schema = 'public';我创建了这个查询的视图，我在以下函数中使用它：
CREATE OR REPLACE FUNCTION updatetable() RETURNS int4 AS $BODY$ DECLARE r RECORD; BEGIN FOR r IN SELECT * from v_reduced_table LOOP update myInitialTable set r.column_name = case when r.column_name = '' then NULL when r.column_name = 'value1' or r.column_name = 'value2' then 'xxxxx' else r.column_name end; END LOOP; return 1; END; $BODY$ LANGUAGE plpgsql; SELECT updatetable() as output;此查询在以后缀结尾的每个列上执行循环并更新其值。 但是当我运行它时，我会得到
ERROR: syntax error at or near "$1" LINE 1: update myInitialTable set $1 = case when $2 = '' then NULL when ...任何帮助表示赞赏:)
I have a PostgreSQL database and I need to do an update over values of specific Columns. The number of columns is so big and I need to do the same operation to different table So better to extract them dynamically.
More specifically I want to extract from the table all the columns whose names ends with "_suffix" and do an update on their values. I started trying to make a script but I don't know if it is the right road!
SELECT columns.column_name FROM information_schema.columns WHERE columns.table_name = 'myInitialTable' AND columns.column_name like '%\_suffix%' AND columns.table_schema = 'public';I created a view of this query and I used it in the following function :
CREATE OR REPLACE FUNCTION updatetable() RETURNS int4 AS $BODY$ DECLARE r RECORD; BEGIN FOR r IN SELECT * from v_reduced_table LOOP update myInitialTable set r.column_name = case when r.column_name = '' then NULL when r.column_name = 'value1' or r.column_name = 'value2' then 'xxxxx' else r.column_name end; END LOOP; return 1; END; $BODY$ LANGUAGE plpgsql; SELECT updatetable() as output;this query do a loop on every column ending with suffix and updates its values. but when I run it I get
ERROR: syntax error at or near "$1" LINE 1: update myInitialTable set $1 = case when $2 = '' then NULL when ...Any help is appreciated :)
最满意答案
在你的功能中你需要使用动态命令 。 funcion format()通常非常有用。
示例数据：
create table my_table(col1_suffix text, col2_suffix text, col3_suffix text); insert into my_table values ('a', 'b', 'c');功能示例：
CREATE OR REPLACE FUNCTION update_my_table() RETURNS void AS $BODY$ DECLARE r RECORD; BEGIN FOR r IN SELECT columns.column_name FROM information_schema.columns WHERE columns.table_name = 'my_table' AND columns.column_name like '%\_suffix%' AND columns.table_schema = 'public' LOOP EXECUTE(FORMAT($f$ UPDATE my_table SET %s = CASE WHEN '%s' = 'col1_suffix' THEN 'col1' WHEN '%s' = 'col2_suffix' OR '%s' = 'col3_suffix' THEN 'xxxxx' END;$f$, r.column_name, r.column_name, r.column_name, r.column_name)); END LOOP; END; $BODY$ LANGUAGE plpgsql;用法：
select update_my_table(); select * from my_table; col1_suffix | col2_suffix | col3_suffix -------------+-------------+------------- col1 | xxxxx | xxxxx (1 row)In your function you need to use dynamic commands. The funcion format() is often very helpful.
Example data:
create table my_table(col1_suffix text, col2_suffix text, col3_suffix text); insert into my_table values ('a', 'b', 'c');Example function:
CREATE OR REPLACE FUNCTION update_my_table() RETURNS void AS $BODY$ DECLARE r RECORD; BEGIN FOR r IN SELECT columns.column_name FROM information_schema.columns WHERE columns.table_name = 'my_table' AND columns.column_name like '%\_suffix%' AND columns.table_schema = 'public' LOOP EXECUTE(FORMAT($f$ UPDATE my_table SET %s = CASE WHEN '%s' = 'col1_suffix' THEN 'col1' WHEN '%s' = 'col2_suffix' OR '%s' = 'col3_suffix' THEN 'xxxxx' END;$f$, r.column_name, r.column_name, r.column_name, r.column_name)); END LOOP; END; $BODY$ LANGUAGE plpgsql;Usage:
select update_my_table(); select * from my_table; col1_suffix | col2_suffix | col3_suffix -------------+-------------+------------- col1 | xxxxx | xxxxx (1 row)更多推荐
发布评论