From: | "Tim Dunnington" <timbert(at)timshouse(dot)com> |
---|---|
To: | <pgsql-bugs(at)postgresql(dot)org> |
Subject: | plpgsql function error after alter table add |
Date: | 2000-12-05 16:31:51 |
Message-ID: | 200012051631.LAA27948@jenkins.timshouse.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
NOTE: Please retract similar, earlier entry (hit send by mistake :-)
Synopsis:
If you create a function with a table as the argument, and later alter that
table and add a new column, the function fails saying "incorrect number of
attributes for table _tablename_" Dropping and readding the function does
not fix this problem.
Error:
ERROR: query didn't return correct # of attributes for $1
Reproduce:
Of course you'll need to add plpgsql as a valid language on your database in
order for this to work:
CREATE TABLE car_make(
make text primary key);
CREATE TABLE car(
vin text primary key,
make text references car_make(make));
INSERT INTO car_make VALUES('Toyota');
INSERT INTO car VALUES('mytoyota','Toyota');
CREATE FUNCTION carname(car) RETURNS text AS '
declare
tmp alias for $1;
begin
return tmp.vin || ''_'' || tmp.make;
end;
' LANGUAGE 'plpgsql';
SELECT carname(car) FROM car;
ALTER TABLE car ADD purchase_date timestamp;
SELECT carname(car) FROM car;
DROP FUNCTION carname(car);
CREATE FUNCTION carname(car) RETURNS text AS '
declare
tmp alias for $1;
begin
return tmp.vin || ''_'' || tmp.make;
end;
' LANGUAGE 'plpgsql';
SELECT carname(car) FROM car;
/* above causes error */
Results:
CREATE FUNCTION carname(car) RETURNS text AS '
junk'# declare
junk'# tmp alias for $1;
junk'# begin
junk'# return tmp.vin || ''_'' || tmp.make;
junk'# end;
junk'# ' LANGUAGE 'plpgsql';
end;
' LANGUAGE 'plpgsql';
CREATE
junk=# SELECT carname(car) FROM car;
carname
-----------------
mytoyota_Toyota
(1 row)
junk=# ALTER TABLE car ADD purchase_date timestamp;
SELECT carname(car) FROM car;
ALTER
junk=# SELECT carname(car) FROM car;
carname
-----------------
mytoyota_Toyota
(1 row)
junk-# DROP FUNCTION carname(car);
DROP
junk=# CREATE FUNCTION carname(car) RETURNS text AS '
junk'# declare
junk'# tmp alias for $1;
junk'# begin
junk'# return tmp.vin || ''_'' || tmp.make;
junk'# end;
junk'# ' LANGUAGE 'plpgsql';
CREATE
junk=# SELECT carname(car) FROM car;
ERROR: query didn't return correct # of attributes for $1
junk=# /* above causes error */
--
Tim Dunnington
Sr. Integration Engineer
Healthcare.com
From | Date | Subject | |
---|---|---|---|
Next Message | pgsql-bugs | 2000-12-05 16:59:42 | createlang requires entering password 4 times |
Previous Message | Thomas Lockhart | 2000-12-05 15:47:14 | Re: Aggregate Function (AVG) not calculated correctly |