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(at)users(dot)sourceforge(dot)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