pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProcedure failing after upgrade
4+ messages / 4 participants
[nested] [flat]
* Procedure failing after upgrade
@ 2004-05-04 13:32 patkins <patkins@killinglyschools.org>
0 siblings, 1 reply; 4+ messages in thread
From: patkins @ 2004-05-04 13:32 UTC (permalink / raw)
To: pgsql-sql
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';
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Procedure failing after upgrade
@ 2004-05-05 17:20 Jie Liang <jie@stbernard.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jie Liang @ 2004-05-05 17:20 UTC (permalink / raw)
To: patkins <patkins@killinglyschools.org>; 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@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 '
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Procedure failing after upgrade
@ 2004-05-05 17:49 Rod Taylor <pg@rbt.ca>
parent: patkins <patkins@killinglyschools.org>
0 siblings, 1 reply; 4+ messages in thread
From: Rod Taylor @ 2004-05-05 17:49 UTC (permalink / raw)
To: patkins <patkins@killinglyschools.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Procedure failing after upgrade
@ 2004-05-05 19:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rod Taylor <pg@rbt.ca>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2004-05-05 19:46 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; +Cc: patkins <patkins@killinglyschools.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-05-05 19:46 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-04 13:32 Procedure failing after upgrade patkins <patkins@killinglyschools.org>
2004-05-05 17:49 ` Rod Taylor <pg@rbt.ca>
2004-05-05 19:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-05-05 17:20 Re: Procedure failing after upgrade Jie Liang <jie@stbernard.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox