Thread: returning SETOF RECORD
populate_record_worker in jsonfuncs.c says this:
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("function returning record called in context "
"that cannot accept type record"), errhint("Try calling the function in the FROM
clause" "using a column definition list.")));
dblink.c has a similar incantation.
Is there any reasonable alternative? That is, if you have a function
returning SETOF record, and the details of the record type aren't
specified, is there anything you can do other than error out like
this?
Thanks,
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 07/14/2014 03:44 PM, Robert Haas wrote:
> populate_record_worker in jsonfuncs.c says this:
>
> if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
> ereport(ERROR,
> (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> errmsg("function returning record called in context "
> "that cannot accept type record"),
> errhint("Try calling the function in the FROM clause "
> "using a column definition list.")));
>
> dblink.c has a similar incantation.
>
> Is there any reasonable alternative? That is, if you have a function
> returning SETOF record, and the details of the record type aren't
> specified, is there anything you can do other than error out like
> this?
>
Not that I can see. What would you suggest?
cheers
andrew
On Mon, Jul 14, 2014 at 4:39 PM, Andrew Dunstan <andrew@dunslane.net> wrote: >> Is there any reasonable alternative? That is, if you have a function >> returning SETOF record, and the details of the record type aren't >> specified, is there anything you can do other than error out like >> this? > > Not that I can see. What would you suggest? Dunno. Was hoping someone else had an idea. It'd certainly be nice to have some way of calling functions like this without specifying the shape of the return value, but I doubt there's a way to make that work without a lot of new infrastructure. For example, if a function could be called at the point where we need to know the record shape with a special flag that says "just tell me what kind of record you're going to return" and then called again at execution time to actually produce the results, that would be nifty. But mostly, I think it's slightly odd that the function gets called at all if nothing useful can be done. Why not just error out in the caller? So that made me wonder if maybe there is a way to do something useful, and I'm just not seeing it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 07/14/2014 04:46 PM, Robert Haas wrote:
> On Mon, Jul 14, 2014 at 4:39 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>>> Is there any reasonable alternative? That is, if you have a function
>>> returning SETOF record, and the details of the record type aren't
>>> specified, is there anything you can do other than error out like
>>> this?
>> Not that I can see. What would you suggest?
> Dunno. Was hoping someone else had an idea. It'd certainly be nice
> to have some way of calling functions like this without specifying the
> shape of the return value, but I doubt there's a way to make that work
> without a lot of new infrastructure. For example, if a function could
> be called at the point where we need to know the record shape with a
> special flag that says "just tell me what kind of record you're going
> to return" and then called again at execution time to actually produce
> the results, that would be nifty.
>
> But mostly, I think it's slightly odd that the function gets called at
> all if nothing useful can be done. Why not just error out in the
> caller? So that made me wonder if maybe there is a way to do
> something useful, and I'm just not seeing it.
>
For json{b}, this only happens if you call json{b}_to_record{set}.
json{b}_populate_record{set} will always have the required info. The
downside of these is that you have to supply a value of a named type
rather than an anonymous type expression.
cheers
andrew
Robert Haas <robertmhaas@gmail.com> writes:
> Dunno. Was hoping someone else had an idea. It'd certainly be nice
> to have some way of calling functions like this without specifying the
> shape of the return value, but I doubt there's a way to make that work
> without a lot of new infrastructure. For example, if a function could
> be called at the point where we need to know the record shape with a
> special flag that says "just tell me what kind of record you're going
> to return" and then called again at execution time to actually produce
> the results, that would be nifty.
I think you're confusing these functions with the kind that specify
their own output rowtype --- which we *can* handle, via a list of OUT
parameters. In these cases, the entire point is that the user has to
specify what SQL rowtype he wants out of the conversion.
regards, tom lane
On Tue, Jul 15, 2014 at 10:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> Dunno. Was hoping someone else had an idea. It'd certainly be nice
>> to have some way of calling functions like this without specifying the
>> shape of the return value, but I doubt there's a way to make that work
>> without a lot of new infrastructure. For example, if a function could
>> be called at the point where we need to know the record shape with a
>> special flag that says "just tell me what kind of record you're going
>> to return" and then called again at execution time to actually produce
>> the results, that would be nifty.
>
> I think you're confusing these functions with the kind that specify
> their own output rowtype --- which we *can* handle, via a list of OUT
> parameters. In these cases, the entire point is that the user has to
> specify what SQL rowtype he wants out of the conversion.
It did take me a bit of time to understand that, but it's not exactly
what I think is odd about this. What I think is strange is that the
function gets called in situations where it can't do anything useful -
it MUST throw an error.
Actually, on further study, I found that isn't quite true. dblink()'s
materializeResult() calls CreateTemplateTupleDesc() if the query
returns PGRES_COMMAND_OK and get_call_result_type() only if it returns
PGRES_TUPLES_OK. That leads to the following odd behavior:
rhaas=# select dblink('', 'vacuum'); dblink
----------(VACUUM)
(1 row)
rhaas=# select dblink('', 'select 1');
ERROR: function returning record called in context that cannot accept
type record
So in theory it seems to be possible to return a value even if no
column definition list is specified. But most further things you
might then want to do with it don't work:
rhaas=# create table f as select dblink('', 'vacuum');
ERROR: column "dblink" has pseudo-type record
rhaas=# select (x.f).* from (select dblink('', 'vacuum') f) x;
ERROR: record type has not been registered
There are a few options, though:
rhaas=# do $$ declare r record; begin r := dblink('', 'vacuum'); raise
notice 'status = %', r.status; end $$;
NOTICE: status = VACUUM
DO
rhaas=# select row_to_json(dblink('', 'vacuum')); row_to_json
---------------------{"status":"VACUUM"}
(1 row)
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Jul 15, 2014 at 10:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I think you're confusing these functions with the kind that specify
>> their own output rowtype --- which we *can* handle, via a list of OUT
>> parameters. In these cases, the entire point is that the user has to
>> specify what SQL rowtype he wants out of the conversion.
> Actually, on further study, I found that isn't quite true. dblink()'s
> materializeResult() calls CreateTemplateTupleDesc() if the query
> returns PGRES_COMMAND_OK and get_call_result_type() only if it returns
> PGRES_TUPLES_OK.
Right --- in the command case, dblink acts like a function that does know
its output rowtype. None too consistent.
We could imagine allowing dblink to default to an output rowtype of
"(text,text,...)" if it can't get anything from its call environment.
I'm not sure if that would be an improvement or not.
regards, tom lane
On Tue, Jul 15, 2014 at 5:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Tue, Jul 15, 2014 at 10:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> I think you're confusing these functions with the kind that specify >>> their own output rowtype --- which we *can* handle, via a list of OUT >>> parameters. In these cases, the entire point is that the user has to >>> specify what SQL rowtype he wants out of the conversion. > >> Actually, on further study, I found that isn't quite true. dblink()'s >> materializeResult() calls CreateTemplateTupleDesc() if the query >> returns PGRES_COMMAND_OK and get_call_result_type() only if it returns >> PGRES_TUPLES_OK. > > Right --- in the command case, dblink acts like a function that does know > its output rowtype. None too consistent. > > We could imagine allowing dblink to default to an output rowtype of > "(text,text,...)" if it can't get anything from its call environment. > I'm not sure if that would be an improvement or not. Well, right now, it doesn't seem like it would buy much. If some of the cases I showed failing in the previous email could be made to actually do something useful, then it'd be more worthwhile. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company