2

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):

select * from union(select table from tablenames)
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
dakay
  • 29
  • 5
  • 1
    You will probably need to write a stored procedure to do this. – Tim Biegeleisen Jul 01 '19 at 05:14
  • 1
    This sounds like a questionable table design to begin with. What is the underlying root problem you are trying to solve with that? [Partitioning](https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE) might be what you are looking for. –  Jul 01 '19 at 08:26

2 Answers2

3

To automate this, you need dynamic SQL

CREATE OR REPLACE FUNCTION f_multi_select()
  RETURNS SETOF table1
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE
   (
   SELECT string_agg(format('SELECT * FROM %I', tbl), ' UNION ALL ')
   FROM   (SELECT tbl FROM tablenames ORDER BY 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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • how would one add `tablenames` as a parameter to the function call. Say if there were different lists of tables to be called? – mark Jun 23 '22 at 09:02
0

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) union all
select * from table2 where 'table2' in (select "table" from tablenames) union all
select * from table3 where 'table3' in (select "table" from tablenames) union all
...
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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360