PostgreSQL – Create Stored Procedure with Dynamic Number of Columns and Column Names

dynamic-sqlpostgresqlstored-procedures

I am relatively new to postgresql (I have version 12), and am having some trouble creating my first stored procedure. The real trouble is that I need it to be dynamic in some way, and thus I am having trouble finding previous threads that have asked this question.

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.

So I want to create a stored procedure that takes as an input a table (or table name), and inside this function, I want to create a new table with the same exact names and number of columns as the original table, but with all of the values adjusted for inflation as above.

I can select all of the columns names I need to create the new table from the command

SELECT column_name 
FROM information_schema.columns 
WHERE table_name = 'nominal_dollars' 
ORDER BY ordinal_position;

I can then loop through these column names using the following loop

   FOR temprow IN
        SELECT column_name FROM information_schema.columns WHERE table_name = 'test' ORDER BY ordinal_position;
    LOOP
        execute 'UPDATE test SET temprow = temprow / 2'
    END LOOP;

But I'm having trouble putting it together in a stored procedure that will actually work.

I have the following snippet of code which should work for MySQL but does not work for Postgres. Any advice on how to get it to work? 

CREATE PROCEDURE [dbo].[Results_Inflation_Adjusted]
@TableName    VARCHAR(50)
AS
BEGIN
DECLARE @sql VARCHAR(5000);

SET @sql = 'SELECT ';

SELECT @sql +=  CASE ColName WHEN 'ID' THEN  @TableName + '.' + ColName WHEN 'Year' THEN  @TableName + '.' + ColName  ELSE @TableName + '.' + ColName + ' * cpi_adjustment' END + ' As ' + ColName + ','

FROM
(SELECT COLUMN_NAME AS Colname
    FROM INFORMATION_SCHEMA. COLUMNS
    WHERE TABLE_NAME = @TableName
    --ORDER BY ORDINAL_POSITION
) temp

SET @sql = LEFT(@sql,DATALENGTH(@sql)-1)  --remove last comma
SET  @sql = @sql + ' From ' + @TableName + ' inner join CPI on ' + @TableName + '.year = ' + ' CPI.year' ;
EXECUTE(@sql)
END

Best Answer

Can be done with a single dynamic CREATE TABLE AS statement:

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

db<>fiddle here

This should be massively faster than what you had in mind.

Assuming you always use the public schema. Else you need to do more.

Be aware of SQL injection and properly quote table and column names!

Related:

Related Question