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!

score 1 · Accepted Answer · edited May 23 '17 at 10:32

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

edited May 23 '17 at 10:32

Community

answered Dec 09 '13 at 18:32

Your solution is so elegant! But after that I need to iterate every element from each array to pass that number to another procedure like in the first array I will call my proc 9 times: my_proc(1); my_proc(2); my_proc(3); and keep going... – ricardocaldeira Dec 09 '13 at 18:45
@ricardocaldeira: You can still do that after converting your data. I suppose a nested for loop. I added a link for looping arrays in modern and ancient Postgres .. – Erwin Brandstetter Dec 09 '13 at 18:49
Man I was trying but I don't know how to get this... If you can help me please responde the other question http://stackoverflow.com/questions/20480035/iterating-on-each-element-from-an-array-of-arrays-on-postgresql – ricardocaldeira Dec 09 '13 at 20:27

1 Answers1