| 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: | Whole Thread | Raw Message | 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? |