Postgres split and convert string value to composite array

Question

I have a Type with below structure.

create type t_attr as (id character varying(50), data character varying(100));

I get text/varchar value through a user-defined function. The value looks like below.

txt := 'id1#data1' , 'id2#data2';

(In the above example, there are 2 values separated by comma, but the count will vary).

I'm trying to store each set into the t_attr array using the below code. Since each set will contain a # as separator I use it to split the id and data values. For testing purpose I have used only one set below.

DO
$$
DECLARE
  attr_array t_attr[];
  txt text := 'id1#data1';
BEGIN
  attr_array[1] := regexp_split_to_array(txt, '#');
END;
$$
LANGUAGE plpgsql;

But the above code throws error saying 'malformed record literal' and 'missing left paranthesis'. Can someone please help on how to store this data into array? Thanks.

Abelisto · Accepted Answer · 2020-11-10 20:17:53Z

1

It is because you are trying to assign an array value to the record. Try:

do $$
declare
    attr_array t_attr[];
    txt text := 'id1#data1';
begin
    attr_array[1] := (split_part(txt, '#', 1), split_part(txt, '#', 2));
    raise info '%', attr_array;
end $$;

Output:

INFO:  {"(id1,data1)"}
DO

However if you really need to split values using arrays:

do $$
declare
    a text[];
begin
    ....
    a := regexp_split_to_array(txt, '#');
    attr_array[1] := (a[1], a[2]);
end $$;

edited Nov 10, 2020 at 20:17

answered Nov 6, 2020 at 0:43

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres split and convert string value to composite array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related