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