
April 8th, 2008, 02:17 PM
|
|
Registered User
|
|
Join Date: Apr 2008
Posts: 1
Time spent in forums: 10 m 29 sec
Reputation Power: 0
|
|
|
How to foreach an array in a function
I have a task I need to repeat on 7 tables, so instead of having the same group of SQL 7 times but with different table names, I'm refactoring this to hardcode an array with my 7 tablenames and want to loop my query. After reading the pgsql manual I don't see how to impliment a FOR on an array. What's the proper syntax to do the following? And can I even do dynamic table naming this way?
CREATE OR REPLACE FUNCTION menu_copy (from_hall integer, to_hall integer)
RETURNS boolean AS
$BODY$
DECLARE
from_hall ALIAS FOR $1;
to_hall ALIAS FOR $2;
declare tables varchar[] := ('table1','table2','table3');
BEGIN
FOR thiscolumn IN columns
CREATE TEMP TABLE thiscolumn || '_tmp' AS SELECT * FROM thiscolumn WHERE AND hall=from_hall AND status=1;
UPDATE thiscolumn || '_tmp' SET hall=to_hall;
INSERT INTO thiscolumn SELECT * FROM thiscolumn || '_tmp';
DROP TABLE thiscolumn || _tmp;
END LOOP;
END;
$$ language 'plpgsql';
|