postgresql 中具有相同前缀的联合表

Question

我在 postgres 数据库的架构（非公开）中有许多带有前缀“tb_”的表。我想创建一个新表作为所有这些带有前缀“tb_”的表的并集。所有表都具有相同的结构并且没有重复。我可以使用如下 SQL 语句手动完成此操作。

CREATE TABLE schema1.tb
AS
SELECT *
FROM schema1.tb_1
UNION
SELECT *
FROM schema1.tb_2
UNION
SELECT *
FROM schema.tb_3

但是，我想自动化，因为有很多表。也许使用 PL/pgSQL 是可能的。我不知道编写 PLSQL 代码，因此在这里寻求帮助。

另外，在创建新表后，我想删除所有带有前缀“tb_”的表。

以下是我根据spatialhast的答案所做的尝试

CREATE OR REPLACE FUNCTION maskunion(IN _schema TEXT, IN _parttionbase TEXT, TEXT) 
RETURNS void 
LANGUAGE plpgsql
AS
$$
DECLARE
    row     record;
BEGIN
    EXECUTE 'DROP TABLE IF EXISTS ' || $3;
    EXECUTE 'CREATE TABLE ' || $3 || '
        (
          col1 double precision,
          col2 double precision,
          col3 double precision,
          col4 double precision,
          col5 double precision
        )';

    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 'INSERT INTO ' || $3 || ' SELECT * FROM ' || quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
    END LOOP;
END;
$$;


SELECT maskunion('schema1', 'tb_', 'schema1.new_table');

Answer 1

以下是基于 Spatialhast 的答案的解决方案之一。

CREATE OR REPLACE FUNCTION maskunion(IN _schema TEXT, IN _parttionbase TEXT, TEXT) 
RETURNS void 
LANGUAGE plpgsql
AS
$
DECLARE
    row     record;
BEGIN
    EXECUTE 'DROP TABLE IF EXISTS ' || $3;
    EXECUTE 'CREATE TABLE ' || $3 || '
        (
          col1 double precision,
          col2 double precision,
          col3 double precision,
          col4 double precision,
          col5 double precision
        )';

    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 'INSERT INTO ' || $3 || ' SELECT * FROM ' || quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
    END LOOP;
END;
$;


SELECT maskunion('schema1', 'tb_', 'schema1.new_table');