Create stored procedure with dynamic number of columns + column names 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
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).