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
Message-ID: <1CAD483B723BD611B0C10090274FF068554860@NXCDMAIL> (raw)
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';
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: peter.atkins@NXCD.com
Subject: Re: Passing array to PL/SQL and looping
In-Reply-To: <1CAD483B723BD611B0C10090274FF068554860@NXCDMAIL>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox