PostgreSQL，使用查询删除表

本文介绍了PostgreSQL，使用查询删除表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有这样的查询来列出当前数据库中的表：

I have such query to list tables in current database:

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删除所有这些表：

I am trying to drop all those tables using upper SELECT like subquery:

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遍历查询结果将表拖放一个。 但是我认为这可能会一次。

But that don't work. How to propery write a query which will drop all tables listed by showed SELECT query? For now I use DataReader for loop through query result and drop tables by one. But I think that may go 'at once'.

推荐答案

您需要为此使用动态SQL， turn只能用于PL / pgSQL之类的过程语言，如下所示：

You need to use dynamic SQL for this, which in turn can only be used in a procedural language like PL/pgSQL, something like this:

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; $$