在postgres中删除所有共享相同前缀的表 [英] drop all tables sharing the same prefix in postgres

问题描述

我想使用一个sql命令/查询从同一数据库中删除所有共享相同前缀('supenh_agk')的表.

I would like to delete all tables sharing the same prefix ('supenh_agk') from the same database, using one sql command/query.

推荐答案

要在一个命令中执行此操作，您需要在

To do this in one command you need dynamic SQL with EXECUTE in a DO statement (or function):

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

这包括当前用户有权访问的 all 模式中的表.为了安全起见，我排除了系统架构.

This includes tables from all schemas 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 identifier that 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.

潜在危险！所有这些表均已删除.我内置了安全装置.在实际执行之前检查生成的语句:注释RAISE并取消注释EXECUTE.

Potentially hazardous! All those tables are dropped for good. I built in a safety. Inspect the generated statements before you actually execute: comment RAISE and uncomment the EXECUTE.

如果其他任何对象(例如视图等)都依赖于表，则会收到一条提示性错误消息，该消息会取消整个事务.如果您确信所有家属也会死亡，请附加 CASCADE :

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:

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

密切相关:

• 更新多个表中的列

• Update column in multiple tables
• Changing all zeros (if any) across all columns (in a table) to... say 1

或者，您可以基于目录表

Alternatively you could build on the catalog table pg_class, which also provides the oid of the table and is faster:

...
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注入?

How does c.oid::regclass defend against SQL injection?

• 表名作为PostgreSQL函数参数

或全部通过单个DROP命令完成.应该更有效率:

Or do it all in a single DROP command. Should be a bit more efficient:

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

相关:

• 是否存在postgres命令列出/删除所有实例化视图?

在最后一个示例中，使用方便安装的系统目录pg_tables.和format()为方便起见.参见:

Using the conveniently fitting system catalog pg_tables in the last example. And format() for convenience. See:

• 如何检查如果表存在于给定的模式中
• 表名作为PostgreSQL函数参数

• How to check if a table exists in a given schema
• Table name as a PostgreSQL function parameter

这篇关于在postgres中删除所有共享相同前缀的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！