如何使用通配符在PostgreSQL中删除多个表

问题描述

使用分区时，通常需要一次删除所有分区。

When working with partitions, there is often a need to delete all partitions at once.

但是

DROP TABLE tablename*

不起作用。 （不遵守通配符。）

Does not work. (The wildcard is not respected).

是否存在一种优雅的（易于记忆的）方式在一个带有通配符的命令中删除多个表？

Is there an elegant (read: easy to remember) way to drop multiple tables in one command with a wildcard?

推荐答案

使用逗号分隔列表：

DROP TABLE foo, bar, baz;

如果您真的需要一只脚枪，这可以做到：

If you realy need a footgun, this one will do it's job:

CREATE OR REPLACE FUNCTION footgun(IN _schema TEXT, IN _parttionbase TEXT) 
RETURNS void 
LANGUAGE plpgsql
AS
$$
DECLARE
    row     record;
BEGIN
    FOR row IN 
        SELECT
            table_schema,
            table_name
        FROM
            information_schema.tables
        WHERE
            table_type = 'BASE TABLE'
        AND
            table_schema = _schema
        AND
            table_name ILIKE (_parttionbase || '%')
    LOOP
        EXECUTE 'DROP TABLE ' || quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
        RAISE INFO 'Dropped table: %', quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
    END LOOP;
END;
$$;

SELECT footgun('public', 'tablename');

这篇关于如何使用通配符在PostgreSQL中删除多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！