Skip site navigation (1) Skip section navigation (2)

Re: Procedure failing after upgrade

From: "Jie Liang" <jie(at)stbernard(dot)com>
To: "patkins" <patkins(at)killinglyschools(dot)org>,<pgsql-sql(at)postgresql(dot)org>
Subject: Re: Procedure failing after upgrade
Date: 2004-05-05 17:20:47
Message-ID: E7E213858379814A9AE48CA6754F5ECB1E1F8E@mail01.stbernard.com (view raw or flat)
Thread:
Lists: pgsql-sql
Are you using 7.4.x now?
If so, I think the problem is condition:
        WHILE id_array[count_it] LOOP
Change it to:
        WHILE id_array[count_it] NOTNULL LOOP

Jie Liang

-----Original Message-----
From: patkins [mailto:patkins(at)killinglyschools(dot)org] 
Sent: Tuesday, May 04, 2004 6:32 AM
To: pgsql-sql(at)postgresql(dot)org
Subject: [SQL] Procedure failing after upgrade


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





---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

pgsql-sql by date

Next:From: Rod TaylorDate: 2004-05-05 17:49:57
Subject: Re: Procedure failing after upgrade
Previous:From: Theodore PetroskyDate: 2004-05-05 15:10:03
Subject: not really SQL but I need info on BLOBs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group