From: | Peter Atkins <peter(dot)atkins(at)NXCD(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Passing array to PL/SQL and looping |
Date: | 2002-09-26 22:01:12 |
Message-ID: | 1CAD483B723BD611B0C10090274FF068554860@NXCDMAIL |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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';
From | Date | Subject | |
---|---|---|---|
Next Message | jonesbl | 2002-09-26 22:47:44 | FW: query problem "server sent binary data ... without prior row description ..." |
Previous Message | Dan Langille | 2002-09-26 21:08:18 | Re: Case Sensitive "WHERE" Clauses? |