need help on writing an aggregate function

On Tue, Nov 16, 2004 at 12:41:45 -0700,
subhash@nmsu.edu wrote:

hi,
I was writing my own data type, and, I have to write aggregare function like
min() and max() for my datatype. I googled for the user defined aggregate
functions, but I could not find any examples for the sfunc and ffunc.
Can any of you provide me the source/structure in C or SQL for these two
functions of min or max or avg etc? for a complex type as given in the examples.
Thanks,
subhash.

Here is an example I did to do concatenation:

drop view people_with_email;
drop table people2email;
drop table email;
drop table people;
drop aggregate concatenate(text);
drop function join_with_comma(text,text);

create function join_with_comma(text,text)
returns text
immutable strict language 'sql'
as 'select $1||'', ''||$2'
;

create aggregate concatenate (
sfunc = join_with_comma,
basetype = text,
stype = text
);

create table email (
email_id integer primary key,
email_address text not null unique
);

copy email from stdin with delimiter '|';
1|scott@scottg.tv
2|fred.flintstone@blah.com
3|barney@hodown.com
4|barney.rubble@hey.org
\.

create table people (
person_id integer primary key,
first_name text not null,
last_name text not null
);

copy people from stdin with delimiter '|';
1|Scott|Goodwin
2|Fred|Flintstone
3|Barney|Rubble
\.

create table people2email (
person_id integer references people (person_id),
email_id integer references email (email_id)
);

copy people2email from stdin with delimiter '|';
1|1
2|2
3|3
3|4
\.

create view people_with_email as
select
a.first_name,
a.last_name,
c.email_address
from
people a,
(select r.person_id, concatenate(b.email_address) as email_address
from people2email r, email b
where r.email_id = b.email_id
group by r.person_id) as c
where a.person_id = c.person_id
;

On Tue, Nov 16, 2004 at 03:21:44PM -0600, Bruno Wolff III wrote:

On Tue, Nov 16, 2004 at 12:41:45 -0700,
subhash@nmsu.edu wrote:

hi,
I was writing my own data type, and, I have to write aggregare
function like min() and max() for my datatype. I googled for the
user defined aggregate functions, but I could not find any
examples for the sfunc and ffunc. Can any of you provide me the
source/structure in C or SQL for these two functions of min or max
or avg etc? for a complex type as given in the examples. Thanks,
subhash.

Here is an example I did to do concatenation:

There's an easier way with the tables below and without a new
aggregate. :)

drop view people_with_email;
drop table people2email;
drop table email;
drop table people;

create table email (
email_id integer primary key,
email_address text not null unique
);

copy email from stdin with delimiter '|';
1|scott@scottg.tv
2|fred.flintstone@blah.com
3|barney@hodown.com
4|barney.rubble@hey.org
\.

create table people (
person_id integer primary key,
first_name text not null,
last_name text not null
);

copy people from stdin with delimiter '|';
1|Scott|Goodwin
2|Fred|Flintstone
3|Barney|Rubble
\.

create table people2email (
person_id integer references people (person_id),
email_id integer references email (email_id)
);

copy people2email from stdin with delimiter '|';
1|1
2|2
3|3
3|4
\.

CREATE VIEW people_with_email AS
SELECT
a.first_name,
a.last_name,
array_to_string(
ARRAY(
SELECT b.email_address
FROM
email b
JOIN
people2email r
ON (
r.email_id = b.email_id
AND
r.person_id = a.person_id
)
),
', '
) AS "email(s)"
FROM
people a;

--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!