postgresqlunionplpgsqldrop-tablemulti-table

Union tables with same prefix in postgresql


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');

Solution

  • 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');