Обсуждение: how to generate a list of distinct scalar values from a column which type is array
how to generate a list of distinct scalar values from a column which type is array
От
"Sergio Andreozzi"
Дата:
Dear all,
given a column which type is for instance varchar(20)[], is it possible via SQL to generate the list of distinct scalar values?
e.g.:
col1
row 1: (aaa, bb, c)
row 2: (dddd, eeee)
row 3: (aaa, eeee)
the query should return:
aaa
bb
c
dddd
eeee
if not, I guess the approach is to use stored procedure. Any code snippet/suggestion?
Thanks, Sergio
given a column which type is for instance varchar(20)[], is it possible via SQL to generate the list of distinct scalar values?
e.g.:
col1
row 1: (aaa, bb, c)
row 2: (dddd, eeee)
row 3: (aaa, eeee)
the query should return:
aaa
bb
c
dddd
eeee
if not, I guess the approach is to use stored procedure. Any code snippet/suggestion?
Thanks, Sergio
Re: how to generate a list of distinct scalar values from a column which type is array
От
David Fetter
Дата:
On Thu, Feb 22, 2007 at 12:38:50PM +0100, Sergio Andreozzi wrote: > Dear all, > > given a column which type is for instance varchar(20)[], This is almost never a good design. If you must have an interface like that, make it VIEW over an aggregate, which you can make writeable. > is it possible via SQL to generate the list of distinct scalar > values? Yes, but you should fix your design :) CREATE TABLE foo (ft TEXT[]); COPY foo(ft) FROM stdin; {aaa,bb,c} {dddd,eeee} {aaa,eeee} \. SELECT DISTINCT ft[i] FROM ( SELECT ft, generate_series( array_lower(ft,1), array_upper(ft,1) ) AS i FROM foo ) AS bar; So, yes, you can do it, and no, you shouldn't. Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!