如何一举丢掉很多(但不是全部)桌子? [英] How to drop many (but not all) tables in one fell swoop?
问题描述
我有一个包含很多表的数据库。这些表中的十二个以相同的前缀开头:
I have a database with many tables. Twelve of these tables start with the same prefix:
mystuff_table_1
mystuff_table_2
mystuff_table_3
mystuff_table_4
etc...
我不想键入 DROP TABLE mystuff_table_n CASCADE;
一遍又一遍,特别是因为我不得不反复删除这些表。如何使我的生活更轻松?
I don't want to type DROP TABLE mystuff_table_n CASCADE;
over and over again, especially since I have to repeatedly drop the tables. How can I make my life easier?
推荐答案
首先,您可以在单条语句 中删除许多表:
First of all, you can delete many tables in a single statement:
DROP TABLE mystuff_table_1, mystuff_table_2, mystuff_table_3 CASCADE;
接下来,您可以将所有这些表放入单独的模式。将该架构添加到默认的 search_path
的用户身份,所以这一切都是透明的。
Next, you could put all of those tables into a separate schema. Add that schema to the default search_path
of your user(s), so it's all transparent.
那么您所需要的就是:
DROP SCHEMA foo CASCADE;
如果这还不够短,请创建一个执行命令的函数。
静态SQL函数:
If that's not short enough, create a function that executes the command.
A static SQL function:
CREATE OR REPLACE FUNCTION f_delete12() RETURNS void AS
$func$
DROP TABLE mystuff_table_1, mystuff_table_2, mystuff_table_3, ... CASCADE;
$func$ LANGUAGE sql VOLATILE;
或动态PL / pgSQL函数:
CREATE OR REPLACE FUNCTION f_delete12()
RETURNS void AS
$func$
BEGIN
EXECUTE (
SELECT 'DROP TABLE ' || string_agg('mystuff_table_' || g, ', ')
|| ' CASCADE'
FROM generate_series(1,12) g -- numbers 1 to 12
);
END
$func$ LANGUAGE plpgsql VOLATILE;
致电:
SELECT f_delete12();
对于更动态的语句:
如何删除所有表
这篇关于如何一举丢掉很多(但不是全部)桌子?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!