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:
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
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:
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