Sergey Burladyan <eshkin...@gmail.com> writes: > Alvaro Herrera <alvhe...@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. 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:
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 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 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 query select * from t; end$$; select * from foo(); ERROR: 42804: structure of query does not match function result type КОНТЕКСТ: PL/pgSQL function "foo" line 1 at RETURN QUERY РАСПОЛОЖЕНИЕ: exec_stmt_return_query, pl_exec.c:2173 -- 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
