creating aggregates that work on composite types (whole tuples)
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
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
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