Thread: foreach in sql functions

foreach in sql functions

From

"Zac Hillier"

Date:

02 October 2002, 21:16:07

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

Re: foreach in sql functions

From

"Oleg Lebedev"

Date:

02 October 2002, 21:23:30

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

Re: foreach in sql functions

From

Tom Lane

Date:

02 October 2002, 21:58:08

"Zac Hillier" <zac@affectors.net> writes:
> Is it possible to write sql functions with a foreach style loop?

SQL functions don't have any control structures.  Consider plpgsql,
pltcl, or plpython instead.

            regards, tom lane

Re: foreach in sql functions

From

"Zac Hillier"

Date:

03 October 2002, 07:02:18

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

Re: foreach in sql functions

From

"Zac Hillier"

Date:

03 October 2002, 07:24:35

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
>
>

Re: foreach in sql functions

From

"Patrick Fiche"

Date:

03 October 2002, 07:32:33

I'm not very experimented in Postgres but I think that you have to
double-quote your table and column names only if you double-quoted them
during creation time...
Otherwise, they should have been created without any capslock and are case
insensitive...

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:25 AM
To: Patrick FICHE; pgsql-general@postgresql.org
Subject: Re: [GENERAL] foreach in sql functions


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
>
>


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: foreach in sql functions

From

Richard Huxton

Date:

03 October 2002, 07:44:50

On Wednesday 02 Oct 2002 11:16 pm, Zac Hillier wrote:
>
> 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

Other people are suggesting plpgsql, but for this case you can use SQL:

INSERT INTO BskDtl (BskKey,PrdKey) SELECT $1,PrdKey FROM Prd WHERE Sts < 999;

I realise in a more general case you might want to manipulate data as you loop
though.

- Richard Huxton

Re: foreach in sql functions

From

Tino Wildenhain

Date:

03 October 2002, 15:53:11

Hi Zac,

even better:

INSERT INTO BskDtl (BskKey, PrdKey)
SELECT $1,PrdKey FROM Prd WHERE Sts < 999;


HTH
Tino

--On Mittwoch, 2. Oktober 2002 23:16 +0100 Zac Hillier <zac@affectors.net>
wrote:

> 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

Re: foreach in sql functions

From

Jan Wieck

Date:

03 October 2002, 17:58:01

Zac Hillier wrote:
>
> 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?

Easy ...

don't create your tables with double-quoted, mixed-case names, and you
can use them without double-quoting.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #