如何在plpgsql中使用变量作为表名 [英] How to use variable as table name in plpgsql
问题描述
我是plpgsql的新手。我正在尝试在plpgsql中使用一个变量作为plpgsql中的表名运行一个简单的查询。但是变量将被解释为表名，而不是变量的值将被解释为变量名。
DECLARE
v_table文本；
z_table文本；
max_id bigint;
BEGIN
FOR v_table IN
选择table_name
FROM information_schema.tables
WHERE table_catalog ='my_database'
AND table_schema ='public'
AND table_name不像'z_％'
LOOP
z_table：='z_'|| v_table;
从z_table INTO max_id中选择max（id）;
从v_table删除，其中id> max_id;
END LOOP；
一些背景信息。对于数据库中的每个表，我都有另一个以 z_开头的表。例如。对于名为 employee的表，我有一个名为 z_employee的表。 z_employee 包含与员工相同的数据集。我在每次测试开始时都使用它来还原雇员表。
运行此函数时，出现以下错误：
错误：关系 z_table不存在
第1行：从z_table
中选择max（id） / pre>
我的猜测是我不允许在SQL查询中使用变量 z_table 。至少不是我在这里使用它的方式。但是我不知道应该怎么做。
解决方案使用使用 EXECUTE 的动态SQL ，正确简化和转义标识符：
创建或替换功能f_test（）
返回值与
相同$ func $
DECLARE
v_table文本;
开始
FOR v_table在
中选择table_name
从information_schema.tables
WHERE table_catalog ='my_database'
AND table_schema ='public'
AND table_name不喜欢'z_％'
LOOP
EXECUTE format（'DELETE FROM％I v WHERE v.id>（SELECT max（id）FROM％I）''
，v_table， 'z_'|| v_table）;
END LOOP；
END
$ func $语言plpgsql;
表名可能需要加引号，以防止语法错误甚至SQL注入！我使用方便的 format（）连接 DELETE 语句并正确地转义标识符。
- 单独的
SELECT 会更昂贵。您可以使用单个 DELETE 语句来完成所有操作。
相关：
此外：
您可以使用（稍微快一些）系统目录 pg_tables 代替：
从pg_catalog.pg_tables
中选择表名
WHERE schemaname ='public'
AND表名不喜欢'z_％'
请参阅：
table_catalog in information_schema.tables 在这里没有等效项。无论如何，仅 current 数据库的表可见。因此，当连接到错误的数据库时，上述谓词 WHERE table_catalog ='my_database'会生成一个空结果集。
I'm new to plpgsql. I'm trying to run a simple query in plpgsql using a variable as table name in plpgsql. But the variable is being interpreted as the table name instead of the value of the variable being interpreted as variable name.
DECLARE
v_table text;
z_table text;
max_id bigint;
BEGIN
FOR v_table IN
SELECT table_name
FROM information_schema.tables
WHERE table_catalog = 'my_database'
AND table_schema = 'public'
AND table_name not like 'z_%'
LOOP
z_table := 'z_' || v_table;
SELECT max(id) from z_table INTO max_id;
DELETE FROM v_table where id > max_id;
END LOOP;
Some background information. For every table in my database, I have another table starting with "z_". E.g. for a table called "employee" I have identical table called "z_employee". z_employee contains the same set of data as employee. I use it to restore the employee table at the start of every test.
When I run this function I get the following error:
ERROR: relation "z_table" does not exist
LINE 1: SELECT max(id) from z_table
My guess is that I'm not allowed to use the variable z_table in the SQL query. At least not the way I'm using it here. But I don't know how it's supposed to be done.
解决方案 Use dynamic SQL with EXECUTE, simplify, and escape identifiers properly:
CREATE OR REPLACE FUNCTION f_test()
RETURNS void AS
$func$
DECLARE
v_table text;
BEGIN
FOR v_table IN
SELECT table_name
FROM information_schema.tables
WHERE table_catalog = 'my_database'
AND table_schema = 'public'
AND table_name NOT LIKE 'z_%'
LOOP
EXECUTE format('DELETE FROM %I v WHERE v.id > (SELECT max(id) FROM %I)'
, v_table, 'z_' || v_table);
END LOOP;
END
$func$ LANGUAGE plpgsql;
Table names may need to be quoted to defend against syntax errors or even SQL injection! I use the convenient format() to concatenate the DELETE statement and escape identifiers properly.
- A separate
SELECT would be more expensive. You can do it all with a single DELETE statement.
Related:
Aside:
You might use the (slightly faster) system catalog pg_tables instead:
SELECT tablename
FROM pg_catalog.pg_tables
WHERE schemaname = 'public'
AND tablename NOT LIKE 'z_%'
See:
table_catalog in information_schema.tables has no equivalent here. Only tables of the current database are visible anyway. So the above predicate WHERE table_catalog = 'my_database' produces an empty result set when connected to the wrong database.
这篇关于如何在plpgsql中使用变量作为表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
