plpgsql function error after alter table add

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