Passing array to PL/SQL and looping
| От | Peter Atkins |
|---|---|
| Тема | Passing array to PL/SQL and looping |
| Дата | |
| Msg-id | 1CAD483B723BD611B0C10090274FF068554860@NXCDMAIL обсуждение исходный текст |
| Ответы |
Re: Passing array to PL/SQL and looping
|
| Список | 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_countBINARY_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';
В списке pgsql-sql по дате отправления: