How to formulate an array literal of a composite type containing arrays?

Question

I have a composite type like

CREATE TYPE example AS (id integer, some_stuff integer[]);

Thought I can use an array of this type as an argument of a function. The only problem is I couldn't find a way to build an array literal for that... If I try obtain it from PostgreSQL:

WITH elements AS (
    SELECT (12, '{1,2}')::example AS e UNION 
    SELECT (3, '{3,1}')::example 
)
SELECT array_agg(e) FROM elements;

I get the following:

{"(3,\"{3,1}\")","(12,\"{1,2}\")"}

But look:

SELECT E'{"(3,\"{3,1}\")","(12,\"{1,2}\")"}'::example[];

ERROR:  malformed array literal: "{"(3,"{3,1}")","(12,"{1,2}")"}"
LINE 1: select E'{"(3,\"{3,1}\")","(12,\"{1,2}\")"}'::example[]

Is there a way to do this?

Grzegorz Szpetkowski · Accepted Answer · 2011-09-09 22:14:38Z

12

Try using ARRAY and ROW constructors:

Select array[row(3, array[3,1]), row(12, array[1,2])]::example[];
               array
------------------------------------
 {"(3,\"{3,1}\")","(12,\"{1,2}\")"}
(1 row)

If you want solution without using constructors, then use following example:

Select E'{"(3,\\"{3,1}\\")","(12,\\"{1,2}\\")"}'::example[];
              example
------------------------------------
 {"(3,\"{3,1}\")","(12,\"{1,2}\")"}
(1 row)

As you see main issue here is that you need to write \\", because this effectively means \" (using "escape" string syntax) that you saw as output of your first select.

edited Sep 9, 2011 at 22:14

answered Sep 9, 2011 at 15:38

Grzegorz Szpetkowski

38.1k6 gold badges94 silver badges140 bronze badges

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

1 Comment

András Váczi Over a year ago

Thanks! The only problem is that I must use literals here, the array constructor is not an option. Fortunately this is a rare case :)

Collectives™ on Stack Overflow

How to formulate an array literal of a composite type containing arrays?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related