Обсуждение: BUG #4585: out parameter name cuases disruption in custom aggregate?

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

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

"robert treat" <xzilla@users.sourceforge.net> writes:
> 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?

No, it's expected behavior.  You're "accumulating" n copies of the
current value of the parameter variable, which is null because you've
not yet assigned anything to it.

The real issue here is that an unqualified name is matched against
plpgsql variables before considering names coming from the SQL query.
See previous pghackers discussions about changing that, and the massive
breakage of user code that would doubtless ensue :-(

            regards, tom lane