postgresql 如何在plpgsql中使用变量作为表名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35559093/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use variable as table name in plpgsql
提问by Steven
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.
我是 plpgsql 的新手。我正在尝试使用变量作为 plpgsql 中的表名在 plpgsql 中运行一个简单的查询。但是变量被解释为表名，而不是被解释为变量名的变量值。
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_employeecontains the same set of data as employee. I use it to restore the employee table at the start of every test.
一些背景资料。对于我数据库中的每个表，我都有另一个以“z_”开头的表。例如，对于一个名为“employee”的表，我有一个名为“z_employee”的相同表。z_employee包含与员工相同的数据集。我用它在每次测试开始时恢复员工表。
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_tablein 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.
我的猜测是我不允许z_table在 SQL 查询中使用该变量。至少不是我在这里使用它的方式。但我不知道它应该如何完成。
回答by Erwin Brandstetter
Use dynamic SQL with EXECUTE, simplify, and escape identifiers properly:
正确使用带有EXECUTE、简化和转义标识符的动态 SQL：
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 DELETEstatement and escape identifiers properly.
可能需要引用表名以防止语法错误甚至 SQL 注入！我使用方便的format()来DELETE正确连接语句和转义标识符。
- A separate
SELECTwould be more expensive. You can do it all with a singleDELETEstatement.
- 单独的
SELECT会更贵。您可以使用一个DELETE语句完成所有操作。
Related:
有关的：
Aside:
在旁边：
You might use the (slightly faster) system catalog pg_tablesinstead:
您可以改用（稍微快一点）系统目录pg_tables：
SELECT tablename
FROM pg_catalog.pg_tables
WHERE schemaname = 'public'
AND tablename NOT LIKE 'z_%'
See:
看：
table_catalogin information_schema.tableshas no equivalent here. Only tables of the currentdatabase are visible anyway. So the above predicate WHERE table_catalog = 'my_database'produces an empty result set when connected to the wrong database.
table_catalogin 在information_schema.tables这里没有等价物。无论如何，只有当前数据库的表是可见的。所以WHERE table_catalog = 'my_database'当连接到错误的数据库时，上面的谓词会产生一个空的结果集。
