Thread: BUG #5078: returns setof functions fails after table structure altered

The following bug has been logged online:

Bug reference:      5078
Logged by:          Sergey Manakov
Email address:      vialorn@gmail.com
PostgreSQL version: 8.4.1
Operating system:   Fedora Core 11
Description:        returns setof functions fails after table structure
altered
Details:

Hi! I'am from Russia and write English ugly, but i'am must to try...

Just repeat steps below:

CREATE TABLE biz_uks
(
  id serial NOT NULL,
  rid timestamp(0) without time zone NOT NULL DEFAULT
('now'::text)::timestamp without time zone,
  title character varying NOT NULL,
  otitles character varying,
  adrfact character varying NOT NULL,
  station character varying,
  region integer,
  zipcode character varying,
  phone character varying NOT NULL,
  fax character varying,
  url character varying,
  mail character varying,
  techabout text,
  remark character varying,
  cnlastdate date,
  CONSTRAINT pk_biz_uks PRIMARY KEY (id),
  CONSTRAINT uq_biz_uks UNIQUE (title)
);
COMMENT ON TABLE biz_uks IS 'Справочник управляющих
компаний';

alter table biz_uks
    drop column remark
;

drop function if exists set__biz_uks();
create or replace function set__biz_uks()
returns biz_uks
security definer volatile language plpgsql as $$
declare
    res    biz_uks;
begin
    return res;
end;
$$ ;
select * from set__biz_uks();

drop function if exists set__biz_uks();
create or replace function set__biz_uks()
returns setof biz_uks
security definer volatile language plpgsql as $$
declare
    res    biz_uks;
begin
    return query
    select res.*;
end;
$$ ;
select * from set__biz_uks();

Last command will raise exception!

"Sergey Manakov" <vialorn@gmail.com> writes:
> alter table biz_uks
>     drop column remark
> ;

> create or replace function set__biz_uks()
> returns setof biz_uks
> security definer volatile language plpgsql as $$
> declare
>     res    biz_uks;
> begin
>     return query
>     select res.*;
> end;
> $$ ;

Yeah, plpgsql has never been tremendously good with rowtypes containing
dropped columns.  There is a fix in CVS HEAD that deals with this case
http://archives.postgresql.org/pgsql-committers/2009-08/msg00068.php
but it was deemed too invasive to risk back-patching.

            regards, tom lane