Thread: trouble building user defined agg function with plpython

I'm trying to test out a user defined aggregation function. The problem I'm
getting is that the state is never passed to the function after the first
call. I'm wondering if this is an issue with having my function defined as
a pypython function or something.

Each call I get an UnboundLocalError exception. I can try/catch it and set
a default value for the state, but then it's triggered on every invocation
during execution.

A small demo function:

CREATE TYPE group_data AS (
    id          integer,
    weight      decimal
);

CREATE OR REPLACE FUNCTION mysum(curr integer, vals group_data)
 RETURNS integer
 AS $$

    try:
        curr = curr + vals['weight']
    except UnboundLocalError:
        plpy.notice("UNBOUND")
        curr = 0

    return curr

 $$ LANGUAGE plpythonu;

CREATE AGGREGATE mysumagg (group_data)
(
    sfunc = mysum,
    stype = integer
);


I get the following when testing it:

-- SELECT
--        mysumagg(ROW(res.idx, 1)::group_data)
-- FROM (
--         SELECT r."D6" as idx
--         FROM t_fct_respondent r
--         LIMIT 2
--      ) AS res;

-- NOTICE:  UNBOUND
--          CONTEXT:  PL/Python function "mysum"
-- NOTICE:  UNBOUND
--          CONTEXT:  PL/Python function "mysum"
 mysumagg
----------
        0
(1 row)


Thanks for any pointers on what I'm missing from the way to handle the
agggregate definition.

--

Rick Harding
@mitechie
http://blog.mitechie.com
http://lococast.net

Rick Harding <rharding@mitechie.com> writes:
> CREATE OR REPLACE FUNCTION mysum(curr integer, vals group_data)
>  RETURNS integer
>  AS $$
>     try:
>         curr = curr + vals['weight']
>     except UnboundLocalError:
>         plpy.notice("UNBOUND")
>         curr = 0
>     return curr
>  $$ LANGUAGE plpythonu;

This function doesn't work when called manually; it's got nothing to do
with the aggregate context.  You should read the last half of this page:

http://www.postgresql.org/docs/9.0/static/plpython-funcs.html

            regards, tom lane

On Tue, 14 Jun 2011, Tom Lane wrote:

> Rick Harding <rharding@mitechie.com> writes:
> > CREATE OR REPLACE FUNCTION mysum(curr integer, vals group_data)
> >  RETURNS integer
> >  AS $$
> >     try:
> >         curr = curr + vals['weight']
> >     except UnboundLocalError:
> >         plpy.notice("UNBOUND")
> >         curr = 0
> >     return curr
> >  $$ LANGUAGE plpythonu;
>
> This function doesn't work when called manually; it's got nothing to do
> with the aggregate context.  You should read the last half of this page:
>
> http://www.postgresql.org/docs/9.0/static/plpython-funcs.html
>
>             regards, tom lane

Thanks so much, I had missed that part in the docs about reassigning the
function parameters. That helped point me in the right direction.

Rick