Обсуждение: need help on writing an aggregate function
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.
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
;
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!