Fill in a table name using a variable from another query

Question

I have a general query I need to run for many tables

SELECT sum(honoraria_amount)
FROM table_1

The table IDs I have a list, from another query. For example, I want to run this for: table_1, table_39, table_42, etc.

Since I have a query that returns the IDs:

table_id
----
1
39
42

Is there anyway I can feed these query results, into the sum query, like some form of replacing with a variable or array to iterate through?

If its possible I'd like to replace x below with the IDs and maybe loop through all the queries to print out all the sums:

SELECT sum(honoraria_amount)
FROM table_x

• McNets: stackoverflow.com/questions/12777231/…

Answer 1

Normally I'd advise against dynamic SQL as it can't be cached by the engine, but I don't see any other alternative that doesn't add a hurdle every time you wish to add a new one of these table_x's.

With that in mind, the following function will help you out:

create or replace function sum_table(
 tid integer
) returns numeric as $$
declare
 _out numeric;
begin
 execute 'select sum(honoraria_amount) from table_' || tid::text || ';' into _out;
 return _out;
end;
$$ language plpgsql;

And then to use it in a query:

with
 __tables as(
 select unnest(array[1, 39, 42]) as table_id
 )
select
 table_id,
 sum_table(table_id)
from
 __tables

Answer 2

Would it be acceptable for you to build a view with UNION ALL over all the tables and then make your select on this view? Like

CREATE VIEW union_table (id, name)
 AS ( SELECT honoraria_amount,... FROM foreign_table_1 UNION ALL 
 SELECT honoraria_amount, ... FROM foreign_table_2 
 ) ;