Re: foreach in sql functions
От | Zac Hillier |
---|---|
Тема | Re: foreach in sql functions |
Дата | |
Msg-id | 00c801c26ab3$335e8d40$667ba8c0@ws обсуждение исходный текст |
Ответ на | Re: foreach in sql functions ("Oleg Lebedev" <oleg.lebedev@waterford.org>) |
Список | pgsql-general |
Hi, I have tried to run the plpgsql script supplied by Oleg but I run into an error that I don't understand. Can anyone shed some light on it for me - it reads NOTICE: Line 6 at open ERROR: Relation "prd" does not exist Thank you Zac ----- Original Message ----- From: "Oleg Lebedev" <oleg.lebedev@waterford.org> To: "Zac Hillier" <zac@affectors.net>; <pgsql-general@postgresql.org> Sent: Wednesday, October 02, 2002 11:24 PM Subject: RE: [GENERAL] foreach in sql functions *This message was transferred with a trial version of CommuniGate(tm) Pro* Try something along the following lines: DROP FUNCTION crt_bsk_dtl (INTEGER); CREATE FUNCTION crt_bsk_dtl (INTEGER) RETURNS INTEGER AS ' DECLARE curs CURSOR FOR SELECT PrdKey FROM Prd WHERE Sts < 999; DECLARE rec RECORD; BEGIN OPEN curs; LOOP -- fetch next record FETCH curs INTO rec; EXIT WHEN NOT FOUND; INSERT INTO BskDtl (BskKey, PrdKey) VALUES ($1, rec.PrdKey); END LOOP; CLOSE curs; RETURN 1; END; ' language 'plpgsql'; -----Original Message----- From: Zac Hillier [mailto:zac@affectors.net] Sent: Wednesday, October 02, 2002 4:16 PM To: pgsql-general@postgresql.org Subject: [GENERAL] foreach in sql functions Hi All, I have only recently started using pgsql and would like to try some internal functions as it appears this would be quicker than running similar functions from php. Is it possible to write sql functions with a foreach style loop? CREATE FUNCTION crt_bsk_dtl(integer) RETURNS integer AS ' SELECT PrdKey FROM Prd WHERE Sts < 999; foreach(Prd.PrdKey){ INSERT INTO BskDtl (BskKey, PrdKey) VALUES ($1, Prd.PrdKey); } SELECT 1; ' LANGUAGE SQL Any help or pointers very much appreciated. Thanks Zac ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
В списке pgsql-general по дате отправления: