I am running Postgres 9.1 and have a master table (returns.msf_mse
) with 30 empty columns and 30 additional temporary tables (temp23_01
- temp23_30
). I want to populate each of the empty columns in the master table with the data from a particular column from each temporary table. The relevant column in each temporary table is always cumret_past
.
I have tried to create a stored procedure/function to achieve this and have been getting nowhere. I have also tried to loop over the update statement but can not get it to work. My base query is:
update returns.msf_mse as a
set cumret_past_23_01 = b.cumret_past
from temp23_01 as b
where a.date = b.date
and a.permno = b.permno;
I simply want to run this statement 30 times, incrementing cumret_past_23_i
and temp23_i
each time.
You could use a DO
statement (pg 9.0+) with EXECUTE
to loop through all tables:
DO
$$
BEGIN
FOR i IN 1..30 LOOP
EXECUTE '
UPDATE returns.msf_mse a
SET cumret_past_23_' || to_char(i, 'FM00') || ' = b.cumret_past
FROM temp23_' || to_char(i, 'FM00') || ' b
WHERE a.date = b.date
AND a.permno = b.permno';
END LOOP;
END
$$
Pay attention to the leading space when formatting the number with to_char()
. I use the FM
modifier to get rid of it.
Aside: I wouldn't use date
as column name, it easily conflicts with the base type or the function of the same name.