PostgreSQL Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesPostgreSQL Help

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!
  #1  
Old April 8th, 2008, 02:17 PM
TravisO TravisO is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Apr 2008
Posts: 1 TravisO User rank is Just a Lowly Private (1 - 20 Reputation Level) 
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';

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesPostgreSQL Help > How to foreach an array in a function


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway