Postgresql return results of dynamic SQL

Question

I'm using Postgresql, version 12.

My goal is to create a function, or stored procedure, which generates a dynamic select which is returned by the function or stored procedure. The difficulty is that the form of the table which is returned is not known in advance.

This is a follow up question to a previous thread , in which I learned how to dynamically create a table within a function with a lot of help from the StackExchange community. But now I realize it would be much nicer, instead of creating a new table inside the function, to just return the result of the select.

The actual problem I need to solve is as follows (this is repeated from the previous thread):

Say I have a table with the following information:

Table nominal_dollars:

year GDP Dividends
2000 100 20
2001 110 30
2002 120 40

In the above table, the numbers are not adjusted for inflation. A common procedure I want to do is create a new table, in which the numbers are adjusted for inflation. This will mean I need to join the above table with a new table, the consumer price inflation (CPI), which has the following form

Table cpi:

year cpi_adjustment
2000 1 
2001 2 
2002 3 

This will allow the creation of a new table, in which all of the numbers are adjusted for inflation, i.e. the original numbers are multiplied by the cpi adjustment:

Table nominal_dollars_adj:

year GDP Dividends
-----------------------------------------
2000 100 20
2001 220 60
2002 360 120

Where GDP for the new table equals GDP from the original table * cpi_adjustment, and the same for dividends.

Now, I want to do this CPI adjustment procedure for many different tables --- which may have different numbers of columns.

The answer which was given by is Erwin is really nice (see below), and generates a new table dynamically. But my question remains --- instead of creating the new table, can I return the results in a select?

CREATE OR REPLACE FUNCTION f_results_inflation_adjusted(_tbl text)
 RETURNS void LANGUAGE plpgsql AS
$func$
BEGIN
 -- RAISE NOTICE '%', ( -- use instead of EXECUTE for debugging
 EXECUTE (
 SELECT format('DROP TABLE IF EXISTS public.%1$I;
 CREATE TABLE public.%1$I AS SELECT %3$s
 FROM public.%2$I t JOIN public.cpi c USING (year)'
 , _tbl || '_adj' -- %1
 , _tbl -- %2
 , string_agg( -- %3
 CASE a.attname
 WHEN 'id' THEN 't.id' -- case sensitive!
 WHEN 'year' THEN 't.year'
 ELSE format('t.%1$I * c.cpi_adjustment AS %1$I', a.attname)
 END, ', ' ORDER BY a.attnum
 )
 )
 FROM pg_catalog.pg_attribute a
 JOIN pg_catalog.pg_class c ON c.oid = a.attrelid
 WHERE c.relname = _tbl
 AND c.relnamespace = 'public'::regnamespace
 AND NOT a.attisdropped
 AND a.attnum > 0
 );
END
$func$;

• Colin 't Hart: I don’t understand why you can’t just create views instead.
• Jacob: Do you mean dynamically create a view in the procedure?
• Colin 't Hart: How you make the views is up to you -- but since you have the procedure, you can easily modify it to create them for you. If the views perform well enough, then you can leave them as views, or create materialized views if you need to. No need to reinvent the wheel and build code to refresh tables with data -- that's exactly what materialized views are.

Answer 1

I strongly recommend you use views for this.

create view <table>_adj
as
select id, year, <col> * cpi_adjustment
from <table>
join cpi using (year);

You can use whichever programming language you're familiar with to create the DDL statements required to create the views; a small tweak to your existing code and it can create the views for you.

With correct indexes, the performance of these views should be more than adequate.

If it's not, use materialized views instead: simply add the keyword materialized in the create statement, and then use refresh materialized view <name> whenever you want to refresh the data.

Because a materialized view is really just a table -- with Postgresql remembering the query used to populate it with data, Postgresql can do the refreshing for you without having to write as much code as you are trying to do. And because it's just a table, you can also add any specific indexes you like to individual materialized views as performance issues become apparent.