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
No one has answered this question yet
Check out similar questions: