Thread: pl/pgsql uniq varchar[] sort?
Hello list. I'm trying to get a unique, sorted varchar array in pl/pgsql. Essentially a "group by" and "order by"'d varchar[].
Anyone got any ideas or point me in the right direction? Thanks.
New Yahoo! Messenger with Voice. Call regular phones from your PC and save big.
On 4/3/06, Matthew Peter <survivedsushi@yahoo.com> wrote: > > Hello list. I'm trying to get a unique, sorted varchar array in pl/pgsql. > Essentially a "group by" and "order by"'d varchar[]. > > Anyone got any ideas or point me in the right direction? Thanks. If your data is not an array type coming off the table but you want it to end up that way, check out array_accum at http://www.postgresql.org/docs/8.1/static/xaggr.html. All you have to do is order the data going into the aggregate: select array_accum(d) from ( select d from t order by... ) if your data is starting off as an array type, you have a few options. you might get the most milage out of a pl/perl procedure to sort the type. If the arrays are small and you absolutely had to do it in plpgsql you could copy the values into a temp table, sort it via query, and resinsert into an array using the above technique. merlin
I'll help clarify exactly what I am trying to accomplish.
What I'm trying to do is create a function that accepts a list then sorts and groups the values (like in sql)... I will have an unique list I can convert it to an array later or leave it a list
I'd like to keep it simple and manipulate the argument like...
select $1 as list
group by list
order by list asc;
Which doesn't work, but that is the functionality I need if possible.
New Yahoo! Messenger with Voice. Call regular phones from your PC for low, low rates.
What I'm trying to do is create a function that accepts a list then sorts and groups the values (like in sql)... I will have an unique list I can convert it to an array later or leave it a list
I'd like to keep it simple and manipulate the argument like...
select $1 as list
group by list
order by list asc;
Which doesn't work, but that is the functionality I need if possible.
New Yahoo! Messenger with Voice. Call regular phones from your PC for low, low rates.
Matthew Peter <survivedsushi@yahoo.com> writes: > What I'm trying to do is create a function that accepts a list then sorts and groups the values (like in sql)... I willhave an unique list I can convert it to an array later or leave it a list There is no "list" data structure in SQL. There are tables, and there are arrays, but it's not especially easy to pass an arbitrary table value to a function. So you almost certainly need to define your problem as "create a function that accepts an array then ...". regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote:
Matthew Peter writes:Originally I wanted to pass in text or varchar array, group it, sort it then do as I willed with it, but I couldn't figure out how. I saw some functions in the contrib for doing these operations, yet only with integer arrays.
> What I'm trying to do is create a function that accepts a list then sorts and groups the values (like in sql)... I will have an unique list I can convert it to an array later or leave it a list
There is no "list" data structure in SQL. There are tables, and there
are arrays, but it's not especially easy to pass an arbitrary table
value to a function. So you almost certainly need to define your
problem as "create a function that accepts an array then ...".
regards, tom lane
I could always try converting the array_sort, array_uniq int[] C function to accept text[], but I decided to ask and see if there was a simplier/standard way first since I don't know C all that well.
Thanks
Blab-away for as little as 1¢/min. Make PC-to-Phone Calls using Yahoo! Messenger with Voice.
On Apr 4, 2006, at 3:22 PM, Matthew Peter wrote: > Tom Lane <tgl@sss.pgh.pa.us> wrote: Matthew Peter writes: > > What I'm trying to do is create a function that accepts a list > then sorts and groups the values (like in sql)... I will have an > unique list I can convert it to an array later or leave it a list > > There is no "list" data structure in SQL. There are tables, and there > are arrays, but it's not especially easy to pass an arbitrary table > value to a function. So you almost certainly need to define your > problem as "create a function that accepts an array then ...". > > regards, tom lane > > Originally I wanted to pass in text or varchar array, group it, > sort it then do as I willed with it, but I couldn't figure out how. > I saw some functions in the contrib for doing these operations, yet > only with integer arrays. > > I could always try converting the array_sort, array_uniq int[] C > function to accept text[], but I decided to ask and see if there > was a simplier/standard way first since I don't know C all that well. Sadly there's very little in the way of array operators in PostgreSQL, so your best bet would be to modify what's in contrib, preferably allowing it to work with any data type. I'm guessing that the int array stuff is in contrib and not in the backend because we're waiting for someone to make it work for all types... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461