Richard Huxton <dev@archonet.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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
