Include parameter in function to UNION multiple PostgreSQL tables

Question

I have developed a function to UNION ALL tables from a list of table names (a table called tablelist below) inspired by this SO post.

The initial function just returns a selection, but now I'd like to write a new table with a name taken from a parameter new_table_name.

I'm struggling with the syntax to insert the parameter into the DROP TABLE AND CREATE TABLE statements. Here's one of the attempts which returns ERROR: mismatched parentheses at or near ";"

DROP FUNCTION IF EXISTS f_multi_union(text);

CREATE OR REPLACE FUNCTION f_multi_union(new_tab_name text)
  RETURNS Table (my_id int, metric double precision, geom geometry)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE
   (
   DROP TABLE IF EXISTS working.'' || new_tab_name || '';
   CREATE TABLE working.'' || new_tab_name || '' AS (
   SELECT string_agg(format('SELECT * FROM %s', tbl), ' UNION ALL ')
   FROM (SELECT tbl FROM working.tablelist) sub 
    )
   );
END
$func$;

RETURN QUERY EXECUTE can execute just one single query, not multiple. And you have a DROP and a CREATE statement, that makes two. Place the DROP statement before the RETURN part and you're fine. — Frank Heikens, Jun 29 '22 at 09:10
@FrankHeikens I moved `DROP TABLE IF EXISTS working.'' || new_tab_name || '';` between `BEGIN` and `RETURN QUERY EXECUTE`. Now gives `ERROR: syntax error at or near "''" LINE 8: DROP TABLE IF EXISTS working.'' || new_tab_name || '';` Something wrong with syntax for calling parameter. — mark, Jun 29 '22 at 09:20
Use something like this: EXECUTE format('DROP TABLE IF EXISTS working.%I', new_tab_name); -- avoid SQL injection However, I don't think this functions will do you want it to do, it creates a table with a single record holding a SQL statement. Is that what you want? — Frank Heikens, Jun 29 '22 at 09:41
@FrankHeikens using `format` passes the `DROP` statement but falls over @ `ERROR: syntax error at or near "CREATE" LINE 11: CREATE TABLE working.'' || new_tab_name || '' AS (` My aim is to create a table that holds the contents of multiple `UNION ALL`s from a list of table names `tablelist`. — mark, Jun 29 '22 at 10:25

score 1 · Accepted Answer · answered Jun 29 '22 at 11:37

1

Something like this?

DROP FUNCTION IF EXISTS f_multi_union(text);

CREATE OR REPLACE FUNCTION f_multi_union(new_tab_name text)
  RETURNS void -- nothing to return
  LANGUAGE plpgsql AS
$func$
DECLARE
    _sql            TEXT;
BEGIN
    _sql    := format('DROP TABLE IF EXISTS working.%I;', new_tab_name); -- avoid SQL injection
    EXECUTE _sql;

    _sql    :=  'SELECT string_agg(format(''SELECT * FROM %I'', tbl), '' UNION ALL '')
                FROM (SELECT tbl FROM working.tablelist) sub;';
    EXECUTE _sql 
        INTO _sql; -- overwrite current _sql content

    _sql    := format('CREATE TABLE working.%I AS %s;', new_tab_name, _sql);
    EXECUTE _sql;
   
END
$func$;

I would replace the * in the SELECT statement with the columns that you need.

answered Jun 29 '22 at 11:37

Frank Heikens

117,544
24
142
135

That worked but I had to swap `format(''SELECT * FROM %I'', tbl)` to `format(''SELECT * FROM %s'', tbl)` because the relation wasn't recognised with double quotes wrapping. – mark Jun 29 '22 at 13:36
@mark: That means the content in your table was wrong. Using %s could cause new problems. When the content matches the table name, %I is the only thing you need to be save. – Frank Heikens Jun 29 '22 at 15:22
what do you mean by content of the table? – mark Jun 30 '22 at 16:15
In your table working.tablelist you have a column tbl. The content in there must match the exact name of the tables you want to use in your SELECT statement. If you have a table "foo" (lower case), you should store it as "foo" and not "Foo" with an upper case. Also no additional spaces or other stuff, it has to match 100%. – Frank Heikens Jun 30 '22 at 19:13

Include parameter in function to UNION multiple PostgreSQL tables

1 Answers1