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 null
 lname    | text    | not null
 username | 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:doe
 john:doe
 willem:doe
 rick: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


25 Jul 2002 20:17:56 +0200
Hannu Krosing <hannu@tm.ee>
creating aggregates that work on composite types (whole tuples)
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

Tue, 30 Jul 2002 10:49:54 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: creating aggregates that work on composite types (whole tuples) 
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


30 Jul 2002 18:06:57 +0200
Hannu Krosing <hannu@tm.ee>
Re: creating aggregates that work on composite types