Union tables by mask in PostgreSQL

Question

In database I have many tables like with names:

"t_2015_09_01_z15"
"t_2015_09_02_z15"
"t_2015_09_03_z15"
"t_2015_09_04_z15"
"t_2015_09_05_z15"
...
"t_2015_10_01_z15"
"t_2015_10_02_z15"
"t_2015_10_03_z15"
"t_2015_10_04_z15"
"t_2015_10_05_z15"
...

Please help create function which union tables by mask, for example

SELECT maskunion('public', 'mask', 'tablename');

where public - scheme name, mask - mask name ('t_2015_09_' - union all tables for 09 mounth, 't_2015_' - union all tables for 2015 year), tablename - output table name.

I found a similar function https://stackoverflow.com/questions/4202135/how-to-drop-multiple-tables-in-postgresql-using-a-wildcard footgun, but this function is for deleting tables by mask. Now I manually union each table by query:

CREATE TABLE t_2015_10 AS
SELECT * FROM t_2015_10_10_z15
UNION ALL
SELECT * FROM t_2015_10_11_z15
UNION ALL
SELECT * FROM t_2015_10_12_z15
UNION ALL
SELECT * FROM t_2015_10_13_z15
UNION ALL
SELECT * FROM t_2015_10_14_z15
UNION ALL
SELECT * FROM t_2015_10_15_z15;

score 3 · Answer 1 · edited May 23 '17 at 12:40

Based on footgun function I write next function

DROP FUNCTION maskunion(text,text,text);
--
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 || '
        (
          ogc_fid integer,
          wkb_geometry geometry(Geometry,4326),
          date character varying,
          x character varying,
          y character varying,
          z character varying,
          count character varying
        )';

    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_name);
    END LOOP;
END;
$$;

SELECT maskunion('public', 'tiles_2015_10_', 'newtable');

But there I used pre-created structure of new table. It will be possible I try change this query. If there is another solution, please write answers.

score 3 · Accepted Answer · edited May 23 '17 at 12:40

Here is a safer and more elegant version of your function:

CREATE OR REPLACE FUNCTION maskunion(_schema text, _tblbase text, _target text)
  RETURNS void AS
$func$
DECLARE
   _safe_target text := quote_ident(_schema) || '.' || quote_ident(_target);
BEGIN
   -- EXECUTE 'DROP TABLE IF EXISTS ' || _safe_target;  -- possibly dangerous!
   -- safer alternative:
   IF to_regclass(_safe_target::cstring) IS NOT NULL THEN
      RAISE EXCEPTION 'Target already exists: >>%<<', _safe_target;
   END IF;

   EXECUTE (
      SELECT 'CREATE TABLE ' || _safe_target || ' AS TABLE '
          || string_agg(quote_ident(schemaname) || '.'
                     || quote_ident(tablename), ' UNION ALL TABLE ')
      FROM   pg_tables
      WHERE  schemaname = _schema
      AND    tablename LIKE (_tblbase || '%')
      ORDER  BY tablename  -- better retain order
   );
END
$func$ LANGUAGE plpgsql;

This assumes the same schema for target and source tables.
Same call as in your own answer:

SELECT maskunion('public', 'tiles_2015_10_', 'newtable');

This concatenates and executes a command of the form:

CREATE TABLE newtable AS
          TABLE t_2015_10_10_z15
UNION ALL TABLE t_2015_10_11_z15
UNION ALL TABLE t_2015_10_12_z15
...

Major points

Like @Craig commented: CREATE TABLE AS is much more elegant.
I added a check whether the target table exists. Blindly dropping any table passed as parameter would make the function quite dangerous.
TABLE tbl is just short for SELECT * FROM tbl.
How to check if a table exists in a given schema
Make sure to escape identifiers properly:
- SQL injection in Postgres functions vs prepared queries

BTW, the footgun function you refer to is a bit dated. You can TRUNCATE or DROP multiple tables with one command, and current Postgres has more elegant options for dynamic SQL:

Truncating all tables in a Postgres database

Union tables by mask in PostgreSQL

2 Answers2

Major points