Re: BUG #4907: stored procedures and changed tables

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:

> Michael Tenenbaum wrote:
>
> > If I have a stored procedure that returns a set of records of a table, I get
> > an error message that the procedure's record is the wrong type after I
> > change some columns in the table.
> >
> > Deleting the procedure then rewriting the procedure does not help. The only
> > thing that works is deleting both the stored procedure and the table and
> > starting over again.
>
> Does it work if you disconnect and connect again?

No, example:

PostgreSQL 8.4.0 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.3-13) 4.3.3, 32-bit

create table t (i int);
create function foo() returns setof t language plpgsql as $$begin return query select * from t; end$$;
select foo();
alter table t add v text; alter table t drop i;
select foo();
ERROR: 42804: structure of query does not match function result type
ПОДРОБНО: Number of returned columns (1) does not match expected column count (2).
КОНТЕКСТ: PL/pgSQL function "foo" line 1 at RETURN QUERY
РАСПОЛОЖЕНИЕ: validate_tupdesc_compat, pl_exec.c:5143
drop function foo();
\c
psql (8.4.0)
You are now connected to database "seb".
create function foo() returns setof t language plpgsql as $$begin return query select * from t; end$$;
select foo();
ERROR: 42804: structure of query does not match function result type
ПОДРОБНО: Number of returned columns (1) does not match expected column count (2).
КОНТЕКСТ: PL/pgSQL function "foo" line 1 at RETURN QUERY
РАСПОЛОЖЕНИЕ: validate_tupdesc_compat, pl_exec.c:5143

--
Sergey Burladyan