Re: postgres functions

On Thursday 12 Sep 2002 9:50 pm, Douglas Blood wrote:
> I have a field in my database that is comma deliminated values. I know that
> I can count up how many values are there using java but i was wondering if
> there was a way using postgres functions or just standard sql.

Use one of PG's procedural languages. This should be fairly straightforward
using plpgsql. The functions you'll want are strpos() and substr()

strpos('1,2,3,4',',') will return 2
substr('1,2,3,4',2+1) will return '2,3,4'

You can simply loop through these until strpos() returns 0 - no need for
recursion. The only problem will be if there are quoted text items containing
commas in your CSV string.

See the manual for discussion of procedural languages, also check
techdocs.postgresql.org for the plpgsql cookbook - someone might have already
solved this for you.

If you prefer perl/tcl, either of these are good candidates for this sort of
problem too.

- Richard Huxton