Union tables by mask in PostgreSQL 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;
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: &gt;&gt;%&lt;&lt;', _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