How To Union A List Of Tables Retrieved From Another Table With A Single Query?

I have a table with a list of tables in PostgreSQL: |id|table | |--|------| |1 |table1| |2 |table2| |3 |table3| I want to select from a union of all these tables like (pseudo-code

Solution 1:

To automate this, you need dynamic SQL

CREATEOR REPLACE FUNCTION f_multi_select()
  RETURNS SETOF table1
  LANGUAGE plpgsql AS
$func$
BEGINRETURN QUERY EXECUTE
   (
   SELECT string_agg(format('SELECT * FROM %I', tbl), ' UNION ALL ')
   FROM   (SELECT tbl FROM tablenames ORDERBY id) sub
   );
END
$func$;

Call:

SELECT * FROM f_multi_select();

Assuming that all tables share the same row type - so we can pick any to define the return type of the function.

I threw in a subquery with ORDER BY - in case the order of tables is meaningful.

Related:

• Return SETOF rows from PostgreSQL function
• Table name as a PostgreSQL function parameter

Solution 2:

Here is one way you can do this without using dynamic SQL. Let's say that you only had 10 possible tables in your schema. Then, you could write the following query:

select*from table1 where'table1'in (select "table" from tablenames) unionallselect*from table2 where'table2'in (select "table" from tablenames) unionallselect*from table3 where'table3'in (select "table" from tablenames) unionall
...
select*from table10 where'table10'in (select "table" from tablenames);

The drawback of this approach is that it requires hard coding a query for each possible table.

I also assume here that select * makes sense, because each of the ten tables would have the same number and types of columns.