Fill in a table name using a variable from another query 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
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