postgresql 删除 postgres 中共享相同前缀的所有表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27610890/
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
drop all tables sharing the same prefix in postgres
提问by Roy
I would like to delete all tables sharing the same prefix ('supenh_agk') from the same database, using one sql command/query.
我想使用一个 sql 命令/查询从同一个数据库中删除所有共享相同前缀（'supenh_agk'）的表。
回答by Erwin Brandstetter
To do this in onecommand you need dynamic SQL with EXECUTEin a DOstatement (or function):
要在一个命令中执行此操作，您需要EXECUTE在DO语句（或函数）中使用动态 SQL ：
DO
$do$
DECLARE
_tbl text;
BEGIN
FOR _tbl IN
SELECT quote_ident(table_schema) || '.'
|| quote_ident(table_name) -- escape identifier and schema-qualify!
FROM information_schema.tables
WHERE table_name LIKE 'prefix' || '%' -- your table name prefix
AND table_schema NOT LIKE 'pg\_%' -- exclude system schemas
LOOP
RAISE NOTICE '%',
-- EXECUTE
'DROP TABLE ' || _tbl; -- see below
END LOOP;
END
$do$;
This includes tables from allschemas the current user has access to. I excluded system schemas for safety.
这包括当前用户有权访问的所有模式中的表。为了安全起见，我排除了系统模式。
If you do not escape identifiers properly the code fails for any non-standard identifierthat requires double-quoting.
Plus, you run the risk of allowing SQL injection. All user input must be sanitized in dynamic code - that includes identifiers potentially provided by users.
如果您没有正确转义标识符，则任何需要双引号的非标准标识符的代码都会失败。
此外，您还面临着允许SQL 注入的风险。所有用户输入都必须在动态代码中进行清理 - 包括用户可能提供的标识符。
Potentially hazardous!All those tables are dropped for good. I built in a safety. Inspect the generated statements before you actually execute: comment RAISEand uncomment the EXECUTE.
潜在危险！所有这些表都被永久删除了。我内置了一个安全。在实际执行之前检查生成的语句：注释RAISE并取消注释EXECUTE.
If any other objects (like views etc.) depend on a table you get an informative error message instead, which cancels the whole transaction. If you are confident that all dependents can die, too, append CASCADE:
如果任何其他对象（如视图等）依赖于一个表，您会收到一条信息性错误消息，这会取消整个事务。如果您确信所有受抚养人也会死亡，请附加CASCADE：
'DROP TABLE ' || _tbl || ' CASCADE;
Closely related:
密切相关：
- Update column in multiple tables
- Changing all zeros (if any) across all columns (in a table) to... say 1
Alternativelyyou could build on the catalog table pg_class, which also provides the oidof the table and is faster:
或者，您可以在目录表上构建pg_class，它也提供oid表的 并且速度更快：
...
FOR _tbl IN
SELECT c.oid::regclass::text -- escape identifier and schema-qualify!
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname NOT LIKE 'pg\_%' -- exclude system schemas
AND c.relname LIKE 'prefix' || '%' -- your table name prefix
AND c.relkind = 'r' -- only tables
...
System catalog or information schema?
系统目录或信息模式？
How does c.oid::regclassdefend against SQL injection?
如何c.oid::regclass防御SQL注入？
Ordo it all in a single DROPcommand. Should be a bit more efficient:
或做这一切在一个单一的DROP命令。应该效率更高一点：
DO
$do$
BEGIN
RAISE NOTICE '%', (
-- EXECUTE (
SELECT 'DROP TABLE ' || string_agg(format('%I.%I', schemaname, tablename), ', ')
-- || ' CASCADE' -- optional
FROM pg_catalog.pg_tables t
WHERE schemaname NOT LIKE 'pg\_%' -- exclude system schemas
AND tablename LIKE 'prefix' || '%' -- your table name prefix
);
END
$do$;
Related:
有关的：
Using the conveniently fitting system catalog pg_tablesin the last example. And format()for convenience. See:
pg_tables在最后一个例子中使用方便的拟合系统目录。并且format()为了方便。看：
回答by Ryan Augustine
Suppose the prefix is 'sales_'
假设前缀是“sales_”
Step 1:Get all the table names with that prefix
第 1 步：获取所有带有该前缀的表名
SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'sales_%';
Step 2:Click the "Download as CSV" button.
第 2 步：单击“下载为 CSV”按钮。
Step 3:Open the file in an editor and replace "sales_with ,salesand "with a space
第3步：在编辑器中打开该文件并替换“sales_用，销售和”有空间
Step 4:DROP TABLE sales_regist, sales_name, sales_info, sales_somthing;
步骤4：DROP TABLE sales_regist, sales_name, sales_info, sales_somthing;
回答by HaveNoDisplayName
This is sql server command, can you try this one, is it worked in postgres or not. This query wil generate the sql script for delete
这是 sql server 命令，你可以试试这个，它在 postgres 中是否有效。此查询将生成用于删除的 sql 脚本
SELECT 'DROP TABLE "' || TABLE_NAME || '"'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '[prefix]%'
[EDIT]
[编辑]
begin
for arow in
SELECT 'DROP TABLE "' || TABLE_NAME || '"' as col1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '[prefix]%'
LOOP
--RAISE NOTICE '%',
EXECUTE 'DROP TABLE ' || arow ;
END LOOP;
end;
