在PostgreSQL中删除所有具有相同前缀的表

Question

在PostgreSQL中删除所有具有相同前缀的表

sqlpostgresqlplpgsqldynamic-sql

25

我想使用一个 SQL 命令/查询，从同一数据库中删除所有具有相同前缀（“supenh_agk”）的表。

- Roy

可能是重复的问题：如何使用通配符在PostgreSQL中删除多个表。 - Eugene Yarmash

3个回答

14

假设前缀为 'sales_'。 步骤 1：获取所有具有该前缀的表名。

SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'sales_%';

步骤 2： 点击“下载CSV”按钮。

步骤 3： 在编辑器中打开文件，并用,sales替换"sales_和用一个空格替换"

步骤 4： DROP TABLE sales_regist, sales_name, sales_info, sales_somthing;

- Ryan Augustine

4

这是SQL Server命令，你可以尝试一下，它是否能在Postgres中正常工作。这个查询将生成用于删除的SQL脚本。

SELECT 'DROP TABLE "' || TABLE_NAME || '"' 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '[prefix]%'

[编辑]

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;

- HaveNoDisplayName

好的，像这样： SELECT 'DROP TABLE ' || TABLE_NAME || ';' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'supenh_agk%';但是生成脚本后有没有一种方法来执行它？我想使用一个命令来完成所有操作，而不是复制结果代码并删除引号（“”）。谢谢！ - Roy

@a_horse_with_no_name：在SQL Server 2008中，“+”也可以用于字符串连接。 - HaveNoDisplayName

在Postgres中无法使用+运算符...（问题涉及Postgres）。谢谢！是否有一种方法可以将“execute”命令作为Postgres查询的一部分使用？-因此实际上执行所得到的脚本（上面建议的输出）？

- Roy

@Roy：将此添加到临时表中，并循环遍历每一行以执行。 - HaveNoDisplayName

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

为了在一个命令中完成这个操作，您需要使用带有EXECUTE的动态SQL语句，在DO语句（或函数）中实现：

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

这包括当前用户可以访问的所有模式中的表。出于安全考虑，我排除了系统模式。

如果您没有正确转义标识符，则对于任何需要双引号的非标准identifier，代码都会失败。此外，您可能会面临允许SQL注入的风险。动态代码中必须对所有用户输入进行清理 - 包括用户可能提供的标识符。 潜在危险！ 所有这些表都将被永久删除。我内置了一个安全机制。在实际执行之前检查生成的语句：注释掉RAISE并取消注释EXECUTE。

如果其他对象（如视图等）依赖于表，则会收到有关错误的信息，该错误会取消整个事务。如果您确信所有依赖项也可以取消，请附加CASCADE：

  'DROP TABLE ' || _tbl || ' CASCADE;

密切相关:

或者，您可以基于目录表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
...

系统目录还是信息模式？

如何检查给定模式中是否存在表

c.oid :: regclass 如何防止 SQL 注入攻击？

将表名作为 PostgreSQL 函数参数

或者在单个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$;