Обсуждение: 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