Re: BUG #4907: stored procedures and changed tables

Sergey Burladyan <eshkinkot@gmail.com> writes:

> Alvaro Herrera <alvherre@commandprompt.com> writes:
>=20
> > Michael Tenenbaum wrote:
> >=20
> > > 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.
> > >=20
> > > Deleting the procedure then rewriting the procedure does not help.  T=
he only
> > > thing that works is deleting both the stored procedure and the table =
and
> > > starting over again.
> >=20
> > Does it work if you disconnect and connect again?
>=20
> No, example:

More simple:

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);
 alter table t add v text; alter table t drop i;
 create function foo() returns setof t language plpgsql as $$begin return q=
uery select * from t; end$$;
 select foo();
ERROR:  42804: structure of query does not match function result type
=D0=9F=D0=9E=D0=94=D0=A0=D0=9E=D0=91=D0=9D=D0=9E:  Number of returned colum=
ns (1) does not match expected column count (2).
=D0=9A=D0=9E=D0=9D=D0=A2=D0=95=D0=9A=D0=A1=D0=A2:  PL/pgSQL function "foo" =
line 1 at RETURN QUERY
=D0=A0=D0=90=D0=A1=D0=9F=D0=9E=D0=9B=D0=9E=D0=96=D0=95=D0=9D=D0=98=D0=95:  =
validate_tupdesc_compat, pl_exec.c:5143

So, function with RETURNS SETOF tbl does not work if it created after ALTER=
 TABLE

8.3.7 too:

PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian=
 4.3.3-5) 4.3.3

 create table t (i int);
 alter table t add v text; alter table t drop i;
 create function foo() returns setof t language plpgsql as $$begin return q=
uery select * from t; end$$;
 select * from foo();
ERROR:  42804: structure of query does not match function result type
=D0=9A=D0=9E=D0=9D=D0=A2=D0=95=D0=9A=D0=A1=D0=A2:  PL/pgSQL function "foo" =
line 1 at RETURN QUERY
=D0=A0=D0=90=D0=A1=D0=9F=D0=9E=D0=9B=D0=9E=D0=96=D0=95=D0=9D=D0=98=D0=95:  =
exec_stmt_return_query, pl_exec.c:2173


--=20
Sergey Burladyan