Re: insert in an array of composite type

On Sat, Apr 25, 2020 at 2:45 PM Maxime FRYSOU <maxprocess(at)gmail(dot)com> wrote:

> Code speaks louder than words, so ...in order to abstract most of the
> complexity, and to focus on the syntax, the products table is obviously not
> representative of the real one. My goal here is to make a "simple" insert.
>
> CREATE TYPE RGB AS (R VARCHAR(5), G VARCHAR(5), B VARCHAR(5));
> CREATE TYPE color AS (rgb RGB, label VARCHAR(50));
> CREATE TABLE products (index SERIAL PRIMARY KEY, colors color []);
>
> And this is where it's not working ...
> INSERT INTO products (colors)
> VALUES
> (
> '{ (("18", "15", "55"), "BLACK" )',
> '("137", "231", "129"), "GREEN" )}' :: color []
> )
>
>
If you are going to do this in pure SQL (i.e., INSERT/VALUES) going from a
string to the color array is the tedious way to do it. Just do it
directly, for example:

--create type comp_in as (a text, b text);
--create type comp_out as (ci comp_in, lbl text);

select array[
( ('a', 'b')::comp_in,
'lbl'
)::comp_out
]::comp_out[]

Regardless the text output is basically what you want if you do find the
need to represent the value as a single string.

select '{"(\"(a,b)\",lbl)"}'::comp_out[]

David J.