plpgsql, I have a solution, want to see if there is a cleaner/better one. Taking vertical list and comma separating it onto a row

From: Henry Drexler <alonup8tb(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: plpgsql, I have a solution, want to see if there is a cleaner/better one. Taking vertical list and comma separating it onto a row
Date: 2011-11-10 13:22:17
Message-ID: CAAtgU9SqhUJfkk2B4Ov5t_pieDML-7f5UpShysLA5dnkusxUHg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am thinking there is a better/simpler way, though this is what I have
working:

(postgres 9.1)

I would like to have the list of colors for each type of clothing to be
comma seperated in the end result.

like this:

type organized_by_type
pants red, blue, orange
shirt black, gray

though with my current solution it looks like this:

type organized_by_type
pants , red, , blue, ,orange,
shirt , black, ,gray,

I know I can add more logic in to get rid of the leading and ending commas,
etc, but it seem like there would be a cleaner more elegant solution.

table
-----------------

-- Table: clothes

-- DROP TABLE clothes;

CREATE TABLE clothes
(
type character varying,
color character varying
)
WITH (
OIDS=FALSE
);
ALTER TABLE clothes
OWNER TO postgres;

insert into clothes values('shirt','red');
insert into clothes values('shirt','blue');
insert into clothes values('shirt','orange');
insert into clothes values('pants','black');
insert into clothes values('pants','gray');

create or replace function organized_by_type(input text) returns text
language plpgsql as $$
DECLARE
item alias for $1;
t text;
groups text;
r integer;
BEGIN
groups = '';
select into r count(color) from clothes where type = item;

for i in 1..r loop
select into t
color
from
clothes
where
type = item
limit 1 offset i-1;

groups = groups || ', ' || t || ', ';
RAISE NOTICE 'value groups: % value t: %',groups,t;
end loop;
return groups;
END
$$

Query with result
------------------------------

select
type,
organized_by_type(type)
from
clothes
group by type

type organized_by_type
pants red, blue, orange
shirt black, gray

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2011-11-10 13:22:47 Re: How to list installed pl languages
Previous Message Jens Wilke 2011-11-10 13:18:14 Re: How to list installed pl languages