Thread: postgres functions

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. I was
thinking a recursive function with substrings and finding the next location
of the comma but I don't know if it will work or if it does the effect on
the database. Any help would be great.

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

The easiest way that I can think of is using plpython (although for those so
inclined, I imagine plperl might do the job about as easily):

create function nelem(text) returns int as '
return len(args[0].split('',''))
' language 'plpython';

That should work very quickly compared with any loop or recursion. The first
call might take a moment to load the python interpreter, but after that if
should be fine.

Regards,
    Jeff

On Thursday 12 September 2002 01: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. I was
> thinking a recursive function with substrings and finding the next location
> of the comma but I don't know if it will work or if it does the effect on
> the database. Any help would be great.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org