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