On Tue, Nov 16, 2004 at 03:21:44PM -0600, Bruno Wolff III wrote: > On Tue, Nov 16, 2004 at 12:41:45 -0700, > [EMAIL PROTECTED] 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|[EMAIL PROTECTED] > 2|[EMAIL PROTECTED] > 3|[EMAIL PROTECTED] > 4|[EMAIL PROTECTED] > \. > > 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 [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org