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

Re: Bug in RETURN QUERY

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
Cc: "Oleg Serov" <serovov(at)gmail(dot)com>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: Bug in RETURN QUERY
Date: 2008-09-01 16:22:50
Message-ID: 21818.1220286170@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
> 2008/9/1 Oleg Serov <serovov(at)gmail(dot)com>:
>> ERROR:  structure of query does not match function result type
>> CONTEXT:  PL/pgSQL function "buggy_procedure" line 3 at RETURN QUERY*/

> this isn't bug, it's feature.

No, it's a bug, and it's not related to plan caching at all --- even if
you start a fresh session the error persists.  The problem is that
plpgsql isn't very good at dealing with rowtypes that contain dropped
columns.  Unfortunately Oleg shouldn't hold his breath waiting for a
fix, because it's not trivial.  In this example, the function would need
to return a three-column tuple (id, dropped-column, buggy_enum_field)
but the SELECT is only giving it two columns.  There isn't anything in
plpgsql that has the ability to convert a tuple to add dropped columns
in the right places.  I think we'd consider adding such functionality
as a new feature not a back-patchable bug fix.

The best near-term workaround would be to handle changes like this by
means of ALTER COLUMN TYPE rather than dropping and re-adding columns.

			regards, tom lane

In response to

Responses

pgsql-bugs by date

Next:From: Zdenek KotalaDate: 2008-09-01 16:56:22
Subject: Re: BUG #4389: FATAL: could not reattach to shared memory (key=1804, addr=018E0000): 487
Previous:From: Tom LaneDate: 2008-09-01 16:05:06
Subject: Re: Bug with FOR ... LOOP and composite types

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