Procedure failing after upgrade

From: "patkins" <patkins(at)killinglyschools(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Subject: Procedure failing after upgrade
Date: 2004-05-04 13:32:11
Message-ID: 20040504133209.3EC83D1CAB0@svr1.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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';

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message ashok@kalculate.com 2004-05-04 13:35:31 typecasting numeric(18,4) to varchar/text
Previous Message scott.marlowe 2004-05-03 20:16:29 Re: start