Thread: 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 '
DECLAREid_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';

On Tue, 2004-05-04 at 09:32, patkins wrote:
> 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!

Please include the actual error message produced. That said, I'm getting
an interesting error. It appears as if the integer array type is being
confused for a boolean.
       WHILE id_array[count_it] LOOP

It is expecting WHILE <boolean> LOOP. Make the id_array[count_it]
expression into a boolean rather than an integer. Something like:
       WHILE id_array[count_it] IS NOT NULL LOOP

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@killinglyschools.org]
Sent: Tuesday, May 04, 2004 6:32 AM
To: pgsql-sql@postgresql.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 '
DECLAREid_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

Rod Taylor <pg@rbt.ca> writes:
> an interesting error. It appears as if the integer array type is being
> confused for a boolean.

Up till 7.4, plpgsql's control statements didn't actually check whether
the given expression returned boolean :-(.  They just assumed that the
resulting Datum should be interpreted as a bool.  7.4 will coerce to
bool or throw an error if it can't.
        regards, tom lane