本文介绍了在Postgresql 9.1中使用动态表名进行循环?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个 plpgslq
函数,该函数进行一些数据处理,并想编写一个 for
循环,但是在设计时未知表名。有什么可能的方法来实现这一目标?这是我想要实现的示例代码片段:
I have a plpgslq
function which does some data processing and would like to write a for
loop, however my table name is not known at design time. Is there any possible way to achieve this? Here is sample code snippet of what I want to achieve:
-- Function: check_data()
-- DROP FUNCTION check_data();
CREATE OR REPLACE FUNCTION check_data()
RETURNS character varying AS
$BODY$declare
dyn_rec record;
tbl_name record;
begin
-- sample dynamic tables
tbl_name := 'cars';
tbl_name := 'trucks';
tbl_name := 'bicycles';
for dyn_rec in select * from format($$s%$$,tbl_name) loop
raise notice 'item is %',dyn_rec.item_no;
end loop;
return 'Processing Ok';
end;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION check_data()
OWNER TO postgres;
推荐答案
您不能将变量用作表或列的标识符plpgsql曾经有过嵌入式SQL。解决方案是动态SQL-EXECUTE或FOR IN EXECUTE语句:
You cannot use a variable as table or column identifier in plpgsql embedded SQL ever. A solution is dynamic SQL - EXECUTE or FOR IN EXECUTE statements:
DO $$
DECLARE
tables text[] = ARRAY['table1','table2'];
table_name text;
rec record;
BEGIN
FOREACH table_name IN ARRAY tables
LOOP
FOR r IN EXECUTE format('SELECT * FROM %I', table_name)
LOOP
RAISE NOTICE '%', rec;
END LOOP;
END LOOP;
END; $$
这篇关于在Postgresql 9.1中使用动态表名进行循环?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!