Sample Header Ad - 728x90

Postgresql return results of dynamic SQL

2 votes
1 answer
3533 views
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$;
Asked by Jacob (35 rep)
Nov 5, 2019, 03:18 AM
Last activity: Jun 6, 2025, 06:04 PM