4

Running the below, I get "a column definition list is required for functions returning "record"".

SELECT *
FROM json_to_record('{"a":1,"b":2,"c":3,"d":4}');
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM json_to_record('{"a":1,"b":2,"c":3,"d":4}');

That's fine. I know what it wants.

SELECT *
FROM json_to_record('{"a":1,"b":2,"c":3,"d":4}')
  AS (a int, b int, c int, d int);

Also, In PostgreSQL, all tables already have a type created by the same name.

CREATE TABLE foo(a,b,c,d)
  AS VALUES
    (1,2,3,4);

That creates an internal type foo linked to the newly created table foo. I can easily create a similar type, bar explicitly though.

CREATE TYPE bar AS (a int, b int, c int, d int);

It would be great to be able to cast the record returned by json_to_record() to bar.

SELECT *
FROM json_to_record('{"a":1,"b":2,"c":3,"d":4}')
  AS foo; -- bar? anything?

Is there anyway to satisfy a column definition list with a type?

7

Use json_populate_record:

SELECT *
FROM json_populate_record(null::foo, '{"a":1,"b":2,"c":3,"d":4}')

The column matching is done by name, non-existing columns are silently ignored:

create type other_foo as (a int, b int, x int, y int):
SELECT *
FROM json_populate_record(null::other_foo, '{"a":1,"b":2,"c":3,"d":4}');

returns:

a | b
--+--
1 | 2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.