Include parameter in function to UNION multiple PostgreSQL tables-CodePudding

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

CodePudding user response：

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.