Re: foreach in sql functions

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