Обсуждение: 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

Поиск
Список
Период
Сортировка

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

От
Henry Drexler
Дата:
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



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

От
Thomas Kellerer
Дата:
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;

Re: 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

От
Henry Drexler
Дата:


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. 

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

От
David Johnston
Дата:
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

Re: 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

От
"David Johnston"
Дата:

 

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.