FAQ
Hello

there is fix for bug Re: [BUGS] BUG #4907: stored procedures and changed tables

regards
Pavel Stehule


2009/7/10 Sergey Burladyan <eshkinkot@gmail.com>:
Sergey Burladyan <eshkinkot@gmail.com> writes:
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.  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

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJul 12, '09 at 8:25p
activeJul 12, '09 at 8:25p
posts1
users1
websitepostgresql.org...
irc#postgresql

1 user in discussion

Pavel Stehule: 1 post

People

Translate

site design / logo © 2021 Grokbase