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

Reply via email to