Re: 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
notnull
 
);

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
referencesemail (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
wherer.email_id = b.email_id          group by r.person_id) as c   where a.person_id = c.person_id
 
;