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