Passing array to PL/SQL and looping

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';

Responses

Browse pgsql-sql by date

  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?