Re: foreach in sql functions - Mailing list pgsql-general

Hmm

Would seem I'm a bit of a dummy...  I have to " " my table and column names,
and being new of course I didn't.

Is there a way to remove this requirement?

Thanks everyone for your help.

Zac


----- Original Message -----
From: "Patrick Fiche" <patrick.fiche@aqsacom.com>
To: "'Zac Hillier'" <zac@affectors.net>
Sent: Thursday, October 03, 2002 9:22 AM
Subject: RE: [GENERAL] foreach in sql functions


> *This message was transferred with a trial version of CommuniGate(tm) Pro*
> Does the Prd table really exist in your database :
> -- SELECT PrdKey FROM Prd WHERE Sts < 999
>
> Patrick
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Zac Hillier
> Sent: Thursday, October 03, 2002 10:02 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] foreach in sql functions
>
>
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>