postgresql中具有相同前缀的并集表

Question

我有很多表的前缀是"tb_"；在postgres数据库的模式(非公共(中。我想创建一个新的表作为所有这些带有前缀"的表的并集；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');