I have number of tables with prefix "tb_" in a schema (not public) in postgres database. I want to create a new table as union of all these tables with prefix "tb_". All the tables have same structure and no duplicates. I could do it manually using the SQL statement as below .
CREATE TABLE schema1.tb
AS
SELECT *
FROM schema1.tb_1
UNION
SELECT *
FROM schema1.tb_2
UNION
SELECT *
FROM schema.tb_3
However, I would like to automate as there are many tables. Perhaps its possible with PL/pgSQL. I have no knowledge of writing PLSQL code hence asking for help here.
Also after creating the new table I would like to drop all the tables with prefix "tb_".
Below is my attempt based on the answer by 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');
Below is one of the solution based on the answer by 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');