list manipulation at column level

Matthew Peter wrote:

Is it possible to append and delete (unknown location)
items in a list stored in a column? For instance,

a column with 'some,values,in,a,list,12,34';

Could I [ap|pre]pend and or delete items in this list
through pgsql?

prepend:
'value' || ',' || column
append
column || ',' ||'value'

Delete would require some regular expressions-- check the manual on these.

Or you could write a Perl function.

However, this strikes me as quite denormalized....

Best Wishes,
Chris Travers
Metatron Technology Consulting

On Sun, Sep 11, 2005 at 04:02:24PM -0700, Chris Travers wrote:

Matthew Peter wrote:

Is it possible to append and delete (unknown location)
items in a list stored in a column? For instance,

a column with 'some,values,in,a,list,12,34';

Could I [ap|pre]pend and or delete items in this list
through pgsql?

prepend:
'value' || ',' || column
append
column || ',' ||'value'

Or use an array type and perform array operations.

http://www.postgresql.org/docs/8.0/interactive/arrays.html
http://www.postgresql.org/docs/8.0/interactive/functions-array.html

CREATE TABLE foo (a text[]);
INSERT INTO foo VALUES ('{some,values,in,a,list,12,34}');

SELECT array_prepend('foo', a) FROM foo;
array_prepend
-----------------------------------------
[0:7]={foo,some,values,in,a,list,12,34}
(1 row)

SELECT array_append(a, 'foo') FROM foo;
array_append
-----------------------------------
{some,values,in,a,list,12,34,foo}
(1 row)

SELECT array_cat(a[1:2], a[6:7]) FROM foo;
array_cat
---------------------
{some,values,12,34}
(1 row)

--
Michael Fuhr

I was reading the concatenation operator earlier.
Postgresql is very neat.

What about deleting an item in the array without
knowing it's position? For example, deleting the item
"12" in the array? Is that possible? Like using

UPDATE array_delete_at(array_find(a,'12'));

where "a" is the column and "12" is the value to find
or would a select be in order to find the position?

from the docs
-----
SELECT * FROM sal_emp WHERE 10000 = ALL
(pay_by_quarter);

Tip: Arrays are not sets; searching for specific
array elements may be a sign of database misdesign.
Consider using a separate table with a row for each
item that would be an array element. This will be
easier to search, and is likely to scale up better to
large numbers of elements.
-----

Lastly, what's considered a large number of elements?

--- Michael Fuhr <mike@fuhr.org> wrote:

On Sun, Sep 11, 2005 at 04:02:24PM -0700, Chris
Travers wrote:

Matthew Peter wrote:

Is it possible to append and delete (unknown

location)

items in a list stored in a column? For instance,

a column with 'some,values,in,a,list,12,34';

Could I [ap|pre]pend and or delete items in this

list

through pgsql?

prepend:
'value' || ',' || column
append
column || ',' ||'value'

Or use an array type and perform array operations.

http://www.postgresql.org/docs/8.0/interactive/arrays.html

http://www.postgresql.org/docs/8.0/interactive/functions-array.html

CREATE TABLE foo (a text[]);
INSERT INTO foo VALUES
('{some,values,in,a,list,12,34}');

SELECT array_prepend('foo', a) FROM foo;
array_prepend
-----------------------------------------
[0:7]={foo,some,values,in,a,list,12,34}
(1 row)

SELECT array_append(a, 'foo') FROM foo;
array_append
-----------------------------------
{some,values,in,a,list,12,34,foo}
(1 row)

SELECT array_cat(a[1:2], a[6:7]) FROM foo;
array_cat
---------------------
{some,values,12,34}
(1 row)

--
Michael Fuhr

______________________________________________________
Yahoo! for Good
Watch the Hurricane Katrina Shelter From The Storm concert
http://advision.webevents.yahoo.com/shelter