Обсуждение: BUG #4907: stored procedures and changed tables
The following bug has been logged online: Bug reference: 4907 Logged by: Michael Tenenbaum Email address: michael@strategic-techs.com PostgreSQL version: 8.2.11 Operating system: Gentoo Description: stored procedures and changed tables Details: 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. Any chance a future release can check for stored procedures that return a set of records of a changed table? Thanks.
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? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes: > 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. The= 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? 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 qu= ery 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 =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 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 qu= ery 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 --=20 Sergey Burladyan
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
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 >