Postgres convert string 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 value through a user-defined function. In the above type both id and data will have comma-separated values. The length of the array can vary between 1 and 100. Below is a sample text value I get as function input parameter which if converted to array should have length 2.

txt text := '("id1,id2,id3", "dat1,data2,dat3"), ("id4,id5,id6", "dat4,dat5,dat6")';

In txt, 'id1,'id2,id3' is the id of type t_attr[1] and so on.

I tried as below to convert it to array, but I'm always getting either 'malformed record literal' and 'Too many columns'.

   arr_attr := array[txt]::t_attr[];

arr_attr should look like this after loading with sample data:

arr_attr[1] should look like ("id1,id2,id3", "data1,data2,data3")
arr_attr[2] will be ("id4,id5,id6","data4,data5,data6")

Hmm. There are unmatched quotes in your input data. What programming language is that? — Laurenz Albe
– Laurenz Albe, Commented Nov 9, 2020 at 11:59
@LaurenzAlbe, can you please help on how the quotes should look like for it to work fine? Its being sent from Java code and quotes can be manipulated as required. — Mano
– Mano, Commented Nov 9, 2020 at 12:03
No, I mean the code like in your question that starts with txt text := contains 9 single quotes, so I don't know what exactly you mean with that line. I don't know any programming language where such a line would be valid. — Laurenz Albe
– Laurenz Albe, Commented Nov 9, 2020 at 12:06
single quotes in single quotes won’t work magically without any escaping. — Cyrille Pontvieux
– Cyrille Pontvieux, Commented Nov 9, 2020 at 12:12
@LaurenzAlbe, edited the txt part. Replaced single-quotes with double-quotes for each array element. — Mano
– Mano, Commented Nov 9, 2020 at 13:05

Laurenz Albe · Accepted Answer · 2020-11-09 20:49:35Z

2

Not pretty, but this should work:

WITH x(e) AS (
   SELECT regexp_split_to_array(
             trim(q.q, '"'),
             '", "'
          )
   FROM regexp_split_to_table(
           trim(
              '("id1,id2,id3", "dat1,data2,dat3"), ("id4,id5,id6", "dat4,dat5,dat6")',
              '()'
           ),
           '\), \('
        ) AS q
)
SELECT ROW(e[1], e[2])::t_attr FROM x;

                row                
-----------------------------------
 ("id1,id2,id3","dat1,data2,dat3")
 ("id4,id5,id6","dat4,dat5,dat6")
(2 rows)

answered Nov 9, 2020 at 20:49

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Postgres convert string 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