plpgsql cursor on unnest function

Question

I have a plpgsql function like:

DO
$$
DECLARE
  c_id c.id%TYPE;   
  j_text c.j_options%TYPE;
  j_option varchar;
  c1 c%ROWTYPE;

begin
  CREATE TYPE my_row_type AS (c2 TEXT);
  for 
    --c1 in select c.j_options, c.id from c c
    c1 in select * from c
    loop
      c_id = c1.id; 
        for
         c2 in select * from  unnest(string_to_array(c1.j_options,', '))
         loop
        raise notice 'Value: %, %', c_id, c2.j_options;
         end loop;   

    end loop;
END
$$ language plpgsql;

My issue is this line:

c2 in select * from  unnest(string_to_array(c1.j_options,', '))

The sample query I run for eg:

select unnest(string_to_array('1.0, 1.2',', '));

returns 2 rows:

1. 1.0
2. 1.2

I need to loop over these two rows but am not sure what the type of the return of this unnest statement should be or how it should be declared in the declare section.

The error I get when running the script:

ERROR:  loop variable of loop over rows must be a record or row variable or 

list of scalar variables
LINE 18:          c2 in select * from  unnest(string_to_array(c1.j_...

From the answer below I get the following error

ERROR:  function string_to_array(bytea, unknown) does not exist
LINE 1: select from  unnest(string_to_array(c1.j_options,', '))
                            ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  select from  unnest(string_to_array(c1.j_options,', '))

I don't understand why this would not work in the script. It recognizes that c1.j_options is bytea.

My amended script bit was:

for c2 in
   select from unnest(string_to_array(c1.j_options,', '))
loop
   raise notice '%', c2;
end loop;

Try `string_to_array(convert_from(c1.j_options, 'utf8'), ', ')` — Abelisto, Feb 24 '16 at 12:28
Honestly it is subject for yet another question and investigation. Create it and also provide DDL for `c` table. — Abelisto, Feb 24 '16 at 15:11

score 1 · Answer 1 · answered Feb 24 '16 at 10:47

According to string_to_array function signature it returns array of text (text[]) so c2 should to be text or varchar:

do language plpgsql $$
declare
  c varchar;
begin
  for c in select unnest(string_to_array('1.0, 1.2', ', ')) loop
    raise notice '%', c;
  end loop;
end; $$;

There is also special loop kind for iterating trough the array elements directly:

do language plpgsql $$
declare
  c varchar;
begin
  foreach c in array string_to_array('1.0, 1.2', ', ') loop
    raise notice '%', c;
  end loop;
end; $$;

tried the above but got an error that it doesnt recognise the function. Please see my update above — user1107753, Feb 24 '16 at 12:10

score 1 · Answer 2 · answered Feb 24 '16 at 20:01

The error message is "ERROR: function string_to_array(bytea, unknown) does not exist". The bytea type is not text, it is binary value, and for this type the function string_to_array is not defined. There are not any default cast from bytea to text. Hard to say what is solution, because bytea can holds anything. If there are some text, then the type "text" should be used, not bytea.

plpgsql cursor on unnest function

2 Answers2