Re: Dynamic/polymorphic record/composite return types for C user-defined-functions - Mailing list pgsql-general

We have solved that problem on the way to function always returns text, but text was actually formated json... We have used plv8 before 9.2 to actually execute dynamic SQL and return result...

However, I think some kind of dynamic record type would be very usefull... (Maybe just record but without need to say AS...)

Though have no idea how hard it would be to implement something like that... And how really it would be usefull widely...

We are avoid much as possible client code - and trying to make it tiny as possible....

All business logic is inside DB... In case we havent found json solution...

We would need to write "client code" related to business logic to actually be able to say, in this concrete case SELECT * FROM function(parameters) AS (expected result)

Or as Stephen described he would need to read BLOB outside DB and there apply the logic - instead of in DB...

Kind Regards,

Misa

On Tuesday, April 2, 2013, Tom Lane wrote:

Stephen Scheck <singularsyntax@gmail.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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general