CODEWITHSUNDEEP
postgresql
MonkeyWithDarts
MonkeyWithDarts

Reputation: 775

Basic json_to_recordset Query Getting 'set-valued function called in context that cannot accept a set'

I am doing a basic json_to_recordset query that, as far as I can tell, follows the pattern in the docs. But I am getting an error that I don't understand. (I'll summarize my research after the code sample.)

select my_schema.my_func(2, 2366800)
=>    my_func
      json
      ----------------------------------------------------
      [{"col_a":"...", "col_b":"...", "col_c":"..."}, ...]

select *
from json_to_recordset(my_schema.my_func(2, 2366800))
as results (col_a         TEXT,
            col_b         TEXT,
            col_c         NUMERIC(10,2))
=> ERROR:  set-valued function called in context that cannot accept a set

In addition to the docs, I have patterned my query on other examples of json_to_recordset. (1, 2, 3) As far as I can tell, my query should work.

I have looked into the set-valued function error. (1, 2, 3; even 4, which says, "When a function returns SETOF something, you need to treat it as if it were a table.") Unfortunately, I have found no explanation of why this error is occurring this particular query.

Upvotes: 1

Views: 701

Answers (2)

MonkeyWithDarts
MonkeyWithDarts

Reputation: 775

My other answer is the immediate answer to my question, but I want to list the solution separately because the solution is rather specific to this situation, while the answer is general.

The simplest solution would be to modify my_func to return json instead of setof json but this function is used elsewhere, so I wanted to handle the type mismatch without modifying my_func.

As noted here, "When a function returns SETOF something, you need to treat it as if it were a table." So the solution involves treating the output of my_func as a table (of a single value), and passing that one row to the json_to_recordset function. Postgres's lateral join did the trick:

select results.*
from my_spec.my_func(2, 2366800) json,
     lateral json_to_recordset(json)
          as results (col_a         TEXT,
                      col_b         TEXT,
                      col_c         NUMERIC(10,2));

Upvotes: 1

MonkeyWithDarts
MonkeyWithDarts

Reputation: 775

As identified in the comment by @klin, the problem was that my_func was returning setof json and json_to_recordset takes json. That type mismatch is what caused the error.

Upvotes: 0

Related Questions