Select substring from a varchar and convert to Integer array

Question

I have a VARCHAR of numbers inside my stored procedure, these numbers are organized as arrays, I will show an example below:

{1,2,3,4,5,6,7,8,9},{1,2,3,4,5},{1,2,3},{9} -- This is a STRING

I want to do a FOR loop to select every time a substring from this set between {} and convert this to an array of integers.

So at first time inside my loop I will have:

{1,2,3,4,5,6,7,8,9}

So I will use array_to_string to convert this to an integer[]

At second time I will have:

{1,2,3,4,5}

and keep going using array_to_string

Any tips? Careful, because unfortunately I'm using PostgreSQL 8.3!

Answer 1

You could do it in a single statement:

SELECT string_to_array(unnest(string_to_array(
          trim('{1,2,3,4,5,6,7,8,9},{1,2,3,4,5},{1,2,3},{9}', '{}')
          , '},{')), ',')::int[]

.. in Postgres 8.4 or later. 8.3 has reached EOL. Urgently consider an upgrade.

However, there is regexp_split_to_table() in 8.3 already:

SELECT string_to_array(regexp_split_to_table(
          trim('{1,2,3,4,5,6,7,8,9},{1,2,3,4,5},{1,2,3},{9}', '{}')
          , '},{'), ',')::int[]

-> SQLfiddle demo for Postgres 8.3.

For looping the array, consider this related answer:
Postgres - array for loop