Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id D31FC4760BD
	for <pgsql-sql@postgresql.org>; Thu, 26 Sep 2002 17:50:08 -0400 (EDT)
Received: from nxcdmail.nxcd.com (nxcdmail.nxcd.com [65.213.43.194])
	by postgresql.org (Postfix) with ESMTP id 9A1D9477052
	for <pgsql-sql@postgresql.org>; Thu, 26 Sep 2002 17:50:04 -0400 (EDT)
Received: by NXCDMAIL with Internet Mail Service (5.5.2653.19)
	id <SM5CS29A>; Thu, 26 Sep 2002 15:01:13 -0700
Message-ID: <1CAD483B723BD611B0C10090274FF068554860@NXCDMAIL>
From: Peter Atkins <peter.atkins@NXCD.com>
To: pgsql-sql@postgresql.org
Subject: Passing array to PL/SQL and looping
Date: Thu, 26 Sep 2002 15:01:12 -0700
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2653.19)
Content-Type: text/plain;
	charset="iso-8859-1"
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200209/342
X-Sequence-Number: 9619

All,

I'm a newbie to PL/SQL and need help badly. I'm trying to pass my array of
id's to the function and then loop through until the array is empty. I know
there must be atleast five things I'm doing wrong. 

Please help!

Cheers,
-p


Call to Procedure and Array:

$myArray = array(15, 6, 23);
select generateInvoice($myArray);

Procedure:

CREATE FUNCTION generateInvoice (VARRAY) RETURNS int4 AS '

        DECLARE
                -- local variables
                temppk INT4;
                v_pids := $1;
                v_count BINARY_INTEGER := 1;
                id INT4;

        BEGIN
                SELECT INTO temppk nextval(''t_task_task_id_seq'');

                LOOP
                IF v_pids.EXISTS(v_count) THEN

                id := v_pids.NEXT(v_count);

                UPDATE t_project SET task_id=temppk WHERE project_id=id;

                v_count := v_count + 1;

                ELSE
                        EXIT;
                END IF;
                END LOOP;

                -- Everything has passed, return id as pk
                RETURN temppk;
        END;
' LANGUAGE 'plpgsql';