Create stored procedure with dynamic number of columns + column names

Question

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

Answer 1

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:

• How to create a table dynamically based on json data?
• Table name as a PostgreSQL function parameter

Answer 2

When passing a table name as a parameter, you can create a clone of this table in a plpgsql function or procedure with simple code like this:

CREATE FUNCTION example(tblname text) RETURNS void AS
$$
 begin
   execute format('CREATE TABLE %I (LIKE %I)', tblname || '_adj', tblname);
 end
$$ LANGUAGE plpgsql;

See Executing Dynamic Commands in plpgsql documentation for more.

To adjust the values in columns like GDP or Dividends, you may automate this with a dynamic UPDATE similarly to the CREATE TABLE, if the columns that need the updates exist under identical names in all these tables (but that seems unlikely except if the schema was designed up-front with this constraint in mind).

Answer 3

The answer below works, but is kind of ugly. Note that it does a slightly different cpi adjustment than the one in the original question. Would be happy to see any improvements or comments, but thanks all for your pointers!

CREATE OR REPLACE FUNCTION cpi_end(tablename TEXT, cpiyear int)  RETURNS VOID

AS $$

DECLARE

-- for the loop
temprow record;

-- for the loop, the name of the column
colname TEXT;

-- the new table name
tablename_cpi TEXT;


BEGIN

-- define the new table name with _cpi
tablename_cpi := tablename || '_cpi' ;

-- Drop the new table if it exists
execute 'DROP TABLE IF EXISTS ' || tablename_cpi  ;

-- create the new table with the same form as the old table
execute format('CREATE TABLE %I AS SELECT * FROM %I', tablename_cpi, tablename);

-- Loop through the column names
FOR temprow IN

        -- Actually get the column names
        EXECUTE 'SELECT column_name 
        FROM information_schema.columns 
        WHERE table_name =' || '''gdp_test_cpi''' 

    LOOP

        -- this removes the parentheses from the column names
        colname := temprow.column_name;

        -- we don't want to update the years : ) 
        IF colname !='year' THEN

            -- First, we divided by cpi index at end of the year
            execute format('

            UPDATE %I SET %I = %I / subq.newval

            FROM 

            (SELECT 

            cpi.year AS newyear, cpi.cpi_end AS newval

            FROM 

            cpi

            ) AS subq

            WHERE %I.year = subq.newyear '

            ,tablename_cpi, colname, colname, tablename_cpi);


            --- Now, we multiply by the CPI for the particular year

            execute format('

            UPDATE %I SET %I = %I * subq.newval

            FROM 

            (SELECT 

            cpi.cpi_end AS newval

            FROM 

            cpi

            WHERE cpi.year = ',tablename_cpi, colname, colname) || cpiyear || ' ) AS subq '


            ;


          END IF;

    END LOOP;

END
$$ LANGUAGE plpgsql;


SELECT cpi_end('gdp_test',2005);




``````