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
– Frank Heikens, Commented Jun 29, 2022 at 9: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
– mark, Commented Jun 29, 2022 at 9: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
– Frank Heikens, Commented Jun 29, 2022 at 9: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 ALLs from a list of table names tablelist. — mark
– mark, Commented Jun 29, 2022 at 10:25

Frank Heikens · Accepted Answer · 2022-06-29 11:37:01Z

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, 2022 at 11:37

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mark Over a year ago

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.

Frank Heikens Over a year ago

@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.

mark Over a year ago

what do you mean by content of the table?

Frank Heikens Over a year ago

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%.

Collectives™ on Stack Overflow

Include parameter in function to UNION multiple PostgreSQL tables

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related