Re: Dynamic/polymorphic record/composite return types for C user-defined-functions

Stephen Scheck <singularsyntax(at)gmail(dot)com> writes:
> I'm trying to write some user-defined functions in C which operate on a
> large object (so their common first argument will be an OID referencing an
> object in the pg_largeobject catalog table created with lo_create()) and
> return either a single row or a set depending on the function. Depending on
> the contents of the BLOB, some of the functions have a need to return
> polymorphic number column(s) as part of their result row (i.e. it could be
> an integer, real or double depending on the input BLOB).

> I've tried various approaches for this but none of them quite work the way
> I need and I'm wondering if I'm missing a fundamental bit of understanding
> of Postgres' type system or it simply doesn't support what I want to do.

It doesn't. Type analysis happens at parse time, not at run time, so
you cannot expect a query variable's data type to be determined by the
contents of some data value not seen until runtime.

The only way I can see to get this to work is a hack similar to common
usage of dblink: you declare the function as returning RECORD or SETOF
RECORD, and then the calling query has to specify an AS clause that
shows what column type(s) it's expecting to get back on this particular
call. That works, sorta, for dblink usages where you're writing
SELECT ... FROM dblink('some particular SQL command') AS ...
and so you know what you're expecting to get from the remote SQL
command. But it's certainly ugly, and you haven't said enough about
your use-case to tell if this is workable for you or not.

If you're only worried about numbers, is it really so critical to
preserve the datatype? You could coerce 'em all to numeric to dodge the
problem, albeit at some loss of efficiency.

Another thought here is that if you don't try to expand a record value,
the need for the parser to know its column types goes away; that is,
if you just write

SELECT function_returning_record(...) FROM ...

and not

SELECT (function_returning_record(...)).* FROM ...

I think that the run-time-blessed-record-type hack will work okay.
Of course that greatly limits what you can do with the result in SQL,
but if you just need to ship it to a client it might be all right.

regards, tom lane