3

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;
3

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

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:

2

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.

  • 2
    Good on you for making an effort at it. For the definition you can use CREATE TABLE ... LIKE ... rather than repeating the columns, assuming there's another table with the same structure like a partition parent. If you want to empty the table, use TRUNCATE TABLE rather than DROP and CREATE. – Craig Ringer Oct 21 '15 at 12:05

Not the answer you're looking for? Browse other questions tagged or ask your own question.