Re: BUG #4907: stored procedures and changed tables

Hello

this is known bug - you have to drop modified table and create it
again. I believe so this should be fixed early - in this summer.

regards
Pavel Stehule

2009/7/10 Sergey Burladyan <eshkinkot@gmail.com>:
> Alvaro Herrera <alvherre@commandprompt.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. =C2=
=A0The only
>> > thing that works is deleting both the stored procedure and the table a=
nd
>> > 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 (Debi=
an 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: =C2=A042804: 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: =C2=A0Number of returne=
d columns (1) does not match expected column count (2).
> =D0=9A=D0=9E=D0=9D=D0=A2=D0=95=D0=9A=D0=A1=D0=A2: =C2=A0PL/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:=
 =C2=A0validate_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: =C2=A042804: 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: =C2=A0Number of returne=
d columns (1) does not match expected column count (2).
> =D0=9A=D0=9E=D0=9D=D0=A2=D0=95=D0=9A=D0=A1=D0=A2: =C2=A0PL/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:=
 =C2=A0validate_tupdesc_compat, pl_exec.c:5143
>
> --
> Sergey Burladyan
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>