Re: BUG #4585: out parameter name cuases disruption in custom aggregate?

On Tuesday 16 December 2008 15:40:17 robert treat wrote:
> The following bug has been logged online:
>
> Bug reference:      4585
> Logged by:          robert treat
> Email address:      xzilla@users.sourceforge.net
> PostgreSQL version: 8.3.x
> Operating system:   solaris/linux
> Description:        out parameter name cuases disruption in custom
> aggregate?
> Details:
>
> using pagila schema{1}, and array_accum from docs{2}, I uncovered the
> following odd behavior.
>
> pagila=# create or replace function wtf(out actorid int) returns setof int
> as $$ declare v_wtf int[]; begin v_wtf := array_accum(actor_id) from actor
> limit 3; raise notice 'wtf --> %',v_wtf; return; end $$ language plpgsql;
> CREATE FUNCTION
> pagila=# select * from wtf();
> NOTICE:  wtf --> {1,2,3,4,5,6,7,8,9,10}
>  actorid
> ---------
> (0 rows)
>
> pagila=# create or replace function wtf(out actor_id int) returns setof int
> as $$ declare v_wtf int[]; begin v_wtf := array_accum(actor_id) from actor
> limit 3; raise notice 'wtf --> %',v_wtf; return; end $$ language plpgsql;
> CREATE FUNCTION
> pagila=# select * from wtf();
> NOTICE:  wtf --> {NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}
>  actor_id
> ----------
> (0 rows)
>
> Apparently the assignment of the integer value is somehow dependent on the
> name of the out parameter; when it matches the column name, you get nulls
> back.  Is this a bug?
>
> {1} http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/dbsamples/pagila/
> {2} http://www.postgresql.org/docs/current/interactive/xaggr.html

I meant to add that in my real code I worked around this by aliasing the
column name, so that it was clear to postgres what was supposed to be accum'd
(otherwise I think it treats your column name as actually a reference to $1,
which is the out param and not set).  Still seems like it could be a bug, but
there is a least a work around.

--
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com