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

On Tue, Apr 2, 2013 at 12:55 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

There is a another way: the 'hstore populate_record hack'. Downside
(vs returning RECORD) is that you need a defined type -- a table or a
composite type. But it's terse and easy to abstract since you're not
providing the full column list.

http://www.postgresql.org/docs/devel/static/hstore.html#HSTORE-FUNC-TABLE

merlin