function json_populate_record(..., text) does not exist

Question

I went by the example from the docu which went fine:

select * from json_populate_record(null::x, '{"a":1,"b":2}')

But my self-constructed JSON simplified similar to this did not work :-(

-- p_some_num of type int
select * from json_populate_record( null:my_record_type, '{"a":'||p_some_num||'',"b":2}' )

resulting in:

ERROR: function json_populate_record(my_record_type, text) does not exist

Answer 1

I should have read more carefully and was not aware of the implicit text to json conversion in the example. After some fiddling around with what was wrong, of course the following works:

select * from json_populate_record( 
  null:my_record_type, ('{"a":'||p_some_num||'',"b":2}')::json )

Answer 2

Andreas Dietrich answered your question, I'll be a little more specific with the example. You can find the answer over here.

According to the documentation, the base or record type is referred from either a Type or a Table. Using which it can determine the column mapping.

Type is a user defined data type. You can find about a Type construct here.

tldr;

for that to work, you have create a table like this.

CREATE TABLE x(a int,b int);

or a type like this.

CREATE TYPE x(a int,b int);

Then run the query.

select * from json_populate_record(null::x, '{"a":1,"b":2}')