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
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
Henry Drexler, 10.11.2011 14:22:
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
CREATE TABLE clothes
(
type character varying,
color character varying
)
SELECT type,
string_agg(color, ',') as organized_by_type
FROM clothes
GROUP BY type;
On Thu, Nov 10, 2011 at 8:34 AM, Thomas Kellerer <spam_eater@gmx.net> wrote:
SELECT type,
string_agg(color, ',') as organized_by_type
FROM clothes
GROUP BY type;
wow, yes that is cleaner.
Thank you for taking the time - obviously I need to read through the string
functions again.
On Nov 10, 2011, at 8:22, Henry Drexler <alonup8tb@gmail.com> wrote:
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
Use the STRING_AGG aggregate function instead of writing your own.
David J
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Henry Drexler
Sent: Thursday, November 10, 2011 8:42 AM
To: Thomas Kellerer
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Re: 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
On Thu, Nov 10, 2011 at 8:34 AM, Thomas Kellerer <spam_eater@gmx.net> wrote:
SELECT type,
string_agg(color, ',') as organized_by_type
FROM clothes
GROUP BY type;
wow, yes that is cleaner.
Thank you for taking the time - obviously I need to read through the string
functions again.
It isn't a "String Function" but an "Aggregate Function"; classification
can be a pain sometimes - especially when more than one category could
readily apply.
David J.