CODEWITHSUNDEEP
postgresqlplpgsqlunnest
user1107753
user1107753

Reputation: 1646

plpgsql cursor on unnest function

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;

Upvotes: 0

Views: 1433

Answers (2)

Pavel Stehule
Pavel Stehule

Reputation: 45930

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.

Upvotes: 1

Abelisto
Abelisto

Reputation: 15624

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; $$;

Upvotes: 1

Related Questions