Postgresql return results of dynamic SQL

dynamic-sqlpostgresql

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$;

Best Answer

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.

Related Question