[SQL] Passing array to PL/SQL and looping

[Peter Atkins]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]