Re: Bug in RETURN QUERY

"Pavel Stehule" <pavel.stehule@gmail.com> writes:
> 2008/9/1 Oleg Serov <serovov@gmail.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