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
