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

Hi,

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(at)gmail(dot)com <javascript:;>> 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(at)postgresql(dot)org<javascript:;>
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>