Date: Tue, 04 May 2004 09:32:11 -0400
From: "patkins" <patkins@killinglyschools.org>
Subject: Procedure failing after upgrade
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
Message-Id: <20040504133209.3EC83D1CAB0@svr1.postgresql.org>

All,

I just upgraded to the latest version from 7.2.x and now a procedure is failing.

Please tell me what I'm doing wrong!

Original Func:

SELECT generateinvoice('{123,124}');

CREATE FUNCTION "generateinvoice" (integer[]) RETURNS integer AS '

        DECLARE id_array ALIAS for $1;
        temppk INT4;
        count_it INT;

        BEGIN
        count_it := 1;

                SELECT INTO temppk nextval(''t_invoice_invoice_id_seq'');
                INSERT INTO t_invoice (invoice_id, created_date, invoice_status_id, modified_date) VALUES (temppk, CURRENT_TIMESTAMP(2), ''1'', CURRENT_TIMESTAMP(2));

        WHILE id_array[count_it] LOOP

                        UPDATE t_event SET invoice_id=temppk, event_status_id=''5'' WHERE event_id=id_array[count_it];

                count_it := count_it + 1;
        END LOOP;

        -- TEST COUNT RETURN (count_it - 1);
                RETURN temppk;
        END;' LANGUAGE 'plpgsql';


My Latest Attempt:

SELECT generateinvoice('{123,124}');

CREATE FUNCTION "generateinvoice" (anyarray) RETURNS integer AS '

	DECLARE
	id_array ALIAS for $1;
	temppk INT4;
	count_it INT;

	BEGIN
		count_it := 1;
		SELECT INTO temppk nextval(''t_invoice_invoice_id_seq'');
		INSERT INTO t_invoice (invoice_id, created_date, invoice_status_id, modified_date) VALUES (temppk, CURRENT_TIMESTAMP(2), ''1'', CURRENT_TIMESTAMP(2));

		WHILE id_array[count_it] LOOP                                                                                                                       
			UPDATE t_project SET invoice_id=temppk, project_status_id=''5'' WHERE project_id=id_array[count_it];
			count_it := count_it + 1;
		END LOOP;

		RETURN temppk;
	END;
' LANGUAGE 'plpgsql';