> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Tille, Andreas
> Sent: Thursday, May 23, 2002 5:24 AM
> To: rmello@fslc.usu.edu
> Cc: PostgreSQL SQL
> Subject: Re: [SQL] Turning column into row
>
>
> On Wed, 22 May 2002, Joel Burton wrote:
>
> >
> http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?re
> cipe_id=13
> > 9 will do as a LIST() replacement.
> While beeing a great hint it has a syntactical mistake.
>
> The correct syntax would be:
>
> -- creat function to comma-ify a list
>
> create function comma_aggregate(text,text) returns text as '
> begin
> if (length($1) > 0 ) then
> return $1 || '', '' || $2; /* note the '' here !! */
> else
> return $2;
> end if;
> end;
> ' language 'plpgsql';
Yep. When you get the function back from \df+, it drops the doubling of
single-quotes.
> A further question: Is there any possibility to add a further flag in the
> way:
>
> select fname, lname, comma(hobbies,SPORT_FLAG) from people
> join hobbies on (personid) group by personid, fname, lname, SPORT_FLAG;
>
> So that we get only those hobbies listed which have SPORT_FLAG = 1
> or something else?
Not like that--aggregate functions can only take one argument. You could,
however, do something like:
SELECT fname, lname, comma ( SPORT_FLAG::char || hobby ) ..
and have the function examine the first character. If 0, don't add the thing
at all; if 1, drop the 1 and add as usual. I think that's way ugly and
hackish, but it would work.
A more SQL-ish way would be something like:
SELECT fname, lname, comma(hobby) FROM people JOIN hobbies USING (personid) WHERE
sport_flag=1GROUPBY personid, fname, lname;
But that would drop everyone that has hobbies but none that are sport_flag
hobbies.
If you wanted the people with hobbies but without sport_flag hobbies, you
could UNION them in at the end.
Alternatively, you could write the whole thing differently:
SELECT fname, lname, ( SELECT comma(hobby) FROM hobbies WHERE
sport_flag=1 AND h.personid=p1.personid ) AS hobbies FROM people AS p0
This would show all people, regardless of whether they had any hobbies or
not.
In recent versions (7.2+, IIRC), this would probably be a good deal faster.
To see people w/o sports hobbies, you could change the join to an outer
join, and kick out people with count(hobbies)=0.
SELECT fname, lname, comma(hobby) FROM people as P0, JOIN ( SELECT * FROM hobbies WHERE
sport_flag=1) AS h0 USING (personid)GROUP BY personid, fname, lname;
HTH.
Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant