PostgreSQL，使用查询删除表

Question

我有一个查询，可以列出当前数据库中的表：

SELECT c.relname 
FROM pg_catalog.pg_class c 
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 
WHERE c.relkind 
IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
pg_catalog.pg_table_is_visible(c.oid)

我正在尝试使用类似子查询的大写SELECT来删除所有这些表：

DROP TABLE IF EXISTS (SELECT c.relname 
FROM pg_catalog.pg_class c 
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 
WHERE c.relkind 
IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
pg_catalog.pg_table_is_visible(c.oid)) as tname 

但那不起作用。
如何正确编写查询，以删除由显示的SELECT查询列出的所有表格？
目前我使用DataReader循环遍历查询结果，并逐个删除表格。
但我认为可以一次性完成。

- Wine Too

---

如果你的意图是删除该用户的所有对象，那么可以使用“drop owned by your_pg_username”更轻松地实现。 - user330315

哦，对于那些表，所有者是“postgres”。这样做可以吗？我还想删除我的用户函数，因此使用子查询获取它将更有用于我的目的。 - Wine Too

通常不建议使用超级用户（postgres）来创建自己的内容 - 这就是其中一个原因。另一个更重要的原因是，这意味着您的应用程序会以超级用户权限连接到数据库，这是一个安全漏洞。 - user330315

是的，我必须更改它，我会的。 - Wine Too

Answer 1

您需要使用动态SQL，而动态SQL只能在过程性语言（如PL/pgSQL）中使用，像下面这样：

do
$$
declare
   stmt text;
   table_rec record;
begin
   for table_rec in (SELECT c.relname as tname
                     FROM pg_catalog.pg_class c 
                       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 
                     WHERE c.relkind IN ('r','') 
                       AND n.nspname NOT IN ('pg_catalog', 'pg_toast') 
                       AND pg_catalog.pg_table_is_visible(c.oid))
   loop
     execute 'drop table '||table_rec.tname||' cascade';
   end loop;
end;
$$

- user330315

---

以前从未见过这样的情况，所以我尝试从.NET中运行它并且成功了。这将删除所有想要的表格。因此，我尝试以相同的方式删除我的用户函数“execute 'drop function '||func_rec.fname||' cascade';”，但是出现错误：ERROR: syntax error at or near "cascade" LINE 1: drop function first_agg cascade ^ QUERY: drop function first_agg cascade CONTEXT: PL/pgSQL function inline_code_block line 14 at EXECUTE statement。能否从这里看出为什么会发生这种情况？ - Wine Too

@user973238：drop function 没有 cascade 选项。但是 drop owned 可以为您处理所有内容。 - user330315

这可能会导致同样的问题，因为所有函数都是由 'postgres' 拥有的。我可以通过 pgAdmin 成功地使用 'cascade' 删除函数，否则无法删除。不管怎样，关于删除用户函数的问题我创建了一个新的问题。请随意参与 :) 谢谢你的帮助。 - Wine Too

再说一遍：为什么你要以超级用户的身份创建自己的函数？你应该创建一个普通用户来拥有所有这些。 - user330315

我想要删除从以下查询中获取的触发器 * select event_object_schema as table_schema,event_object_table as table_name,trigger_schema,trigger_name,string_agg(event_manipulation, ',') as event,action_timing as activation, action_condition as condition,action_statement as definition from information_schema.triggers where event_object_table like 'his_%' and trigger_name ='set_timestamp' group by 1,2,3,4,6,7,8 order by table_schema,table_name - not-a-bug

Answer 2

您可以使用SELECT语句来“生成”DROP语句，例如：

SELECT 'DROP TABLE "' + table_name + '"' 
FROM information_schema.tables
WHERE table_name LIKE '[your_prefix_here]%'

将“[your_prefix_here]%”替换为您的条件和通配符

- hackmaxed

---

这对我不起作用。我收到一个错误，说“+”运算符不适用。 - undefined