Skip site navigation (1) Skip section navigation (2)

fix: plpgsql: return query and dropped columns problem

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Sergey Burladyan <eshkinkot(at)gmail(dot)com>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Michael Tenenbaum <michael(at)strategic-techs(dot)com>, pgsql-bugs(at)postgresql(dot)org
Subject: fix: plpgsql: return query and dropped columns problem
Date: 2009-07-12 15:28:58
Message-ID: 162867790907120828wa84ac80p8da8fa8d8bef2376@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
Hello

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

regards
Pavel Stehule


2009/7/10 Sergey Burladyan <eshkinkot(at)gmail(dot)com>:
> Sergey Burladyan <eshkinkot(at)gmail(dot)com> writes:
>
>> Alvaro Herrera <alvherre(at)commandprompt(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

Attachment: return_query_fix.diff
Description: text/x-patch (6.5 KB)

Responses

pgsql-hackers by date

Next:From: Greg StarkDate: 2009-07-12 15:29:41
Subject: Re: concurrent index builds unneeded lock?
Previous:From: Tom LaneDate: 2009-07-12 15:17:04
Subject: Re: concurrent index builds unneeded lock?

pgsql-bugs by date

Next:From: David FetterDate: 2009-07-12 15:53:50
Subject: Re: [BUGS] BUG #4916: wish: more statistical functions (median,percentiles etc)
Previous:From: Joe ConwayDate: 2009-07-12 09:51:39
Subject: Re: BUG #4916: wish: more statistical functions (median, percentiles etc)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group