postgresql plpgsql text[] varchar[] 数组不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16709335/
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
plpgsql text[] varchar[] array not working
提问by mctuna
My purpose is assigning the name of the tables into an array and drop them in the foreachloop via this array.
我的目的是将表的名称分配到一个数组中，并foreach通过这个数组将它们放入循环中。
I am actually trying to do something more complicated but before I try to get the following code working:
我实际上是在尝试做一些更复杂的事情，但在我尝试使以下代码工作之前：
CREATE OR REPLACE FUNCTION delete_auto()
RETURNS void AS
$BODY$DECLARE
t text;
tbl_array text[] = array["ID: 889197824 CH: 0006 Messdaten2","ID: 889197824 CH: 0006 Messdaten3","ID: 889197824 CH: 0006 Messdaten4"];
BEGIN
FOREACH t IN ARRAY tbl_array LOOP
DROP TABLE t;
END LOOP;
END; $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION delete_auto()
OWNER TO postgres;
Function seems to me pretty correct but it does not drop the tables, does nothing actually after I execute it. I just get such an error on the log:
函数在我看来非常正确，但它不会删除表，在我执行它后实际上什么也不做。我只是在日志上收到这样的错误：
Table ??t?? does not exist.
I have also tried the same code with varchar[] instead of text[] but not working either. Any help would be appreciated
我也用 varchar[] 而不是 text[] 尝试了相同的代码，但也没有工作。任何帮助，将不胜感激
回答by Pavel Stehule
There are a few errors:
有几个错误：
you cannot use double quotes for string literals (it is used for case sensitive SQL identifiers)
you should use dynamic SQL for parametrized DDL statements (statement EXECUTE). DDL statements does not have execution plan and these statements doesn't support parametrization (you should not use variables there)
DO $$ DECLARE tables varchar[] := ARRAY['t1','t2']; t varchar; BEGIN FOREACH t IN ARRAY tables LOOP EXECUTE format('DROP TABLE %I', t); END LOOP; END; $$;
您不能对字符串文字使用双引号（它用于区分大小写的 SQL 标识符）
您应该对参数化的 DDL 语句（语句 EXECUTE）使用动态 SQL。DDL 语句没有执行计划并且这些语句不支持参数化（您不应该在那里使用变量）
DO $$ DECLARE tables varchar[] := ARRAY['t1','t2']; t varchar; BEGIN FOREACH t IN ARRAY tables LOOP EXECUTE format('DROP TABLE %I', t); END LOOP; END; $$;
回答by Loek Bergman
What is the version of plpgsql you are using? It is convenient if you add that piece of information whenever you ask something.
您使用的 plpgsql 是什么版本？如果您在询问某事时添加那条信息，会很方便。
The syntax to drop a table is
删除表的语法是
drop table [if exists] **name** [CASCADE] [RESTRICT]
The value of t does not look like the name of a table.
t 的值看起来不像表的名称。
Other possible explanations: Your function is supposed to return void, hence it is correct that it does not show anything. Do you mean that the tables are not removed? Have you tested it first using t and trying to make an insert on one of the tables to find out if the table you tried to remove exists? Is the person executing the function the owner of the tables?
其他可能的解释：您的函数应该返回 void，因此它不显示任何内容是正确的。你的意思是表没有被删除？您是否首先使用 t 对其进行了测试并尝试在其中一个表上进行插入以找出您尝试删除的表是否存在？执行函数的人是表的所有者吗？
