PostgreSQL,使用查询删除表 [英] PostgreSQL, drop tables with query
本文介绍了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;
$$
这篇关于PostgreSQL,使用查询删除表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文