Re: Bug in RETURN QUERY

"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