Re: Bug in RETURN QUERY

Tom Lane wrote:

> "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

hi tom,

i've just come across this bug as well as soon as i dropped
some columns (demonstration code below for those anyone who
can't remember the bug).

i hope i misunderstood your suggested fix. i strongly
disagree that the fix is to make it possible for plpgsql to
add dropped columns to queries (either automatically or via
some explicit syntactic device). the dropped column was
dropped after all. it should stay dropped. i don't think
anyone would want to add dropped columns to any result sets.

it's the automatic rowtype of the table that is wrong,
because it has not been updated (and apparently needs to be)
to reflect the current state of the table.

i hope this does get fixed or dropping columns is a big mistake.
i guess i won't be doing that again :)

cheers,
raf

--- demonstrate: dropped column breaks rowtypes -------------------------
create table bug (a integer null, b integer null, c integer null);

insert into bug (a, b, c) values (1, 2, 3);

create or replace function bug1()
returns setof bug stable language plpgsql as
$$ begin return query select * from bug; end $$;

select * from bug1();
-- Works

alter table bug drop b;

-- Ending/restarting session here makes no difference
select * from bug1();
-- ERROR: structure of query does not match function result type

-- Recreating the function explicitly makes no difference
create or replace function bug1()
returns setof bug stable language plpgsql as
$$ begin return query select * from bug; end $$;

-- Ending/restarting session here makes no difference
select * from bug1();
-- ERROR: structure of query does not match function result type

drop table if exists bug cascade;