Thread: creating aggregates that work on composite types (whole tuples)

creating aggregates that work on composite types (whole tuples)

From

Hannu Krosing

Date:

25 July 2002, 16:16:17

I am trying to create an aggregate function that works on whole tuples,
but the system does not find them once defined ;(

hannu=# \d users                              Table "users" Column  |  Type   |                     
Modifiers                       
----------+---------+------------------------------------------------------fname    | text    | not nulllname    | text
  | not nullusername | text    | userid   | integer | not null 
 

hannu=# create or replace function add_table_row(text,users) returns
text as
hannu-# 'state = args[0]
hannu'# user = args[1]["fname"] + ":" + args[1]["lname"]
hannu'# if state:
hannu'#     return state + "\\n" + user 
hannu'# else:
hannu'#     return user
hannu'# '
hannu-# LANGUAGE 'plpython';
CREATE
hannu=# select add_table_row('',users) from users;add_table_row 
---------------jane:doejohn:doewillem:doerick:smith
(4 rows)
hannu=# create aggregate tabulate (
hannu(#     basetype = users,
hannu(#     sfunc = add_table_row,
hannu(#     stype = text,
hannu(#     initcond = ''
hannu(# );
CREATE
hannu=# select tabulate(users) from users;
ERROR:  No such attribute or function 'tabulate'



What am I doing wrong ?

--------------
Hannu

Re: creating aggregates that work on composite types (whole tuples)

From

Tom Lane

Date:

30 July 2002, 13:49:51

Hannu Krosing <hannu@tm.ee> writes:
> I am trying to create an aggregate function that works on whole tuples,
> but the system does not find them once defined ;(
> hannu=# select tabulate(users) from users;
> ERROR:  No such attribute or function 'tabulate'

This seems to work in CVS tip.  I think you're stuck in older releases
though.  The syntax "foo(tablename)" is understood to mean "either a
column selection or a function call" ... but aggregates were quite
distinct from plain functions up until about a month ago, and they
weren't considered as an option at that spot in the code.
        regards, tom lane

Re: creating aggregates that work on composite types

From

Hannu Krosing

Date:

30 July 2002, 14:05:28

On Tue, 2002-07-30 at 16:49, Tom Lane wrote:
> Hannu Krosing <hannu@tm.ee> writes:
> > I am trying to create an aggregate function that works on whole tuples,
> > but the system does not find them once defined ;(
> > hannu=# select tabulate(users) from users;
> > ERROR:  No such attribute or function 'tabulate'
> 
> This seems to work in CVS tip.

That's great news.

What I really would want is to be able to register and call the same
function for "any" input, like count(*) is currently, only with the
exception that the rows are actually passed to it.

I think that could be made possible sometime in the future with either
registering for 'any' and anonymous types created on-the-fly or some
sort of tuple "supertype" that any type of row could be cast into,
either implicitly or explicitly so that I could register ggregate
tabulate(tupletype)

I would not mind having to do tabulate(tupletype(users)) but it would be
nice if it were done automatically.

> I think you're stuck in older releases
> though.  The syntax "foo(tablename)" is understood to mean "either a
> column selection or a function call" ... but aggregates were quite
> distinct from plain functions up until about a month ago, and they
> weren't considered as an option at that spot in the code.

Thanks, I'll check it on CVS tip.

---------------
Hannu