Re: returning SETOF RECORD

On Tue, Jul 15, 2014 at 10:20 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)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