5

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
Andreas Covidiot
  • 290
  • 1
  • 3
  • 10

2 Answers2

3

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 )
Andreas Covidiot
  • 290
  • 1
  • 3
  • 10
  • 2
    what are you using for my_record_type in this example? – ryantuck Jun 19 '17 at 15:40
  • @Ryan Tuck: something like CREATE TYPE x(a int,b int) (as Pavan stated) – Andreas Covidiot Mar 04 '19 at 11:13
  • why can't postgres derive the types by itself? Like pandas does when reading csv files – Sergey Zaitsev Jun 03 '21 at 10:56
  • @SergeyZaitsev it maybe could, but its open source and this is maybe not a widely used functionality and thus priority. pg is way behind oracle or m$ in some areas since decades. but it is robust and feature-rich enough to be a fitting competitor in many use cases. – Andreas Covidiot Jun 04 '21 at 16:22
  • @SergeyZaitsev you can use tables, views or custom types as valid types. Unfortunately unable to make completely dynamic tables from JSONB though. – rup Dec 08 '21 at 09:31
3

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}')
Pavan
  • 31
  • 1