PostgreSQL json to composite type conversion

I am experimenting with the new json features in POstgreSQL 9.4 and find most of them very useful. For example, converting Composite to json to help back-end processes make it easier to display function results.

but what I cannot get is another way, turning the json string into a Composite type object.

note that the function to_json (...) is used here to simulate a json string that has the exact structure of a composite type

DROP TYPE IF EXISTS myType_1;
DROP TYPE IF EXISTS myType_0;

CREATE TYPE myType_0 AS (
    id SMALLINT,
    name TEXT
);

CREATE TYPE myType_1 AS (
    since TIMESTAMP,
    objects myType_0[]
);


SELECT json_populate_record(NULL::myType_1, 
                            to_json( ROW( now(), 
                                          ARRAY[ROW(1,'name1')::myType_0, 
                                                ROW(2,'name2')::myType_0, 
                                                ROW(4,'name3')::myType_0])::myType_1))

      

code Returns

ERROR:  malformed array literal: "[{"id":1,"name":"name1"},{"id":2,"name":"name2"},{"id":4,"name":"name3"}]"
DETAIL:  "[" must introduce explicitly-specified array dimensions. 

      

+3


source to share





All Articles