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

• Luuk: The part 'dynamic number of columns' is not possible because when creating a stored procedure, or a function, the number of parameters (and definition) needs to be known.
• McNets: You can get an answer from this similar question. You should deal with INFORMATION_SCHEMA views.
• Erwin Brandstetter: 1. Shouldn't it be 120 where you say 80? 2. Always disclose the targeted version of Postgres. 3. When you say "stored procedure", do you actually mean "function"? dba.stackexchange.com/a/194811/3684
• Jacob: My mistake, yes it should say 120. And I have added my version, which is 12. Note that I now have an answer below as well, and thank you.

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

• Jacob: Awesome code -- exactly what I'm looking for. One follow up question --- if I wanted the function to return the result in a SELECT statement, rather than create a new table, would this be possible?
• Erwin Brandstetter: @Jacob: Possible, yes. Even simple. But not trivial. I half suspected that's what you really want. I suggest you ask a new question. Comments are not the place.

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);
``````