Using a type in place of a column definition list?

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?

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


StackExchange Database Administrators Q#186033, answer score: 10