Re: Strange RETURN NEXT behaviour in Postgres 8.0

Richard Huxton <dev(at)archonet(dot)com> writes:
> I seem to remember some subtle problems with dropped columns and plpgsql
> functions - could be one of those still left.

For instance:

regression=# create table usno (ra real, dec real, bmag real, rmag real,ipix int8);
CREATE TABLE
regression=# [ create Sergey's functions ]
regression=# insert into usno values(1,2,3,4);
INSERT 1132435 1
regression=# select * from yyy();
ra | dec | bmag | rmag | ipix
----+-----+------+------+------
1 | 2 | 3 | 4 |
(1 row)

regression=# alter table usno add column errbox box;
ALTER TABLE
regression=# select * from yyy();
ra | dec | bmag | rmag | ipix | errbox
----+-----+------+------+------+--------
1 | 2 | 3 | 4 | |
(1 row)

regression=# alter table usno drop column errbox;
ALTER TABLE
regression=# select * from yyy();
ERROR: wrong record type supplied in RETURN NEXT
CONTEXT: PL/pgSQL function "yyy" line 8 at return next
regression=#

It looks like the code that handles returning a RECORD variable doesn't
cope with dropped columns in the function result rowtype.

(If you instead declare rec as usno%rowtype, you get a different set
of misbehaviors after adding/dropping columns, so that code path isn't
perfect either :-()

regards, tom lane