Re: polymorphic table functions light

Hi

po 16. 12. 2019 v 19:53 odesílatel Peter Eisentraut <
peter(dot)eisentraut(at)2ndquadrant(dot)com> napsal:

> I want to address the issue that calling a record-returning function
> always requires specifying a result column list, even though there are
> cases where the function could be self-aware enough to know the result
> column list of a particular call. For example, most of the functions in
> contrib/tablefunc are like that.
>
> SQL:2016 has a feature called polymorphic table functions (PTF) that
> addresses this. The full PTF feature is much larger, so I just carved
> out this particular piece of functionality. Here is a link to some
> related information:
> https://modern-sql.com/blog/2018-11/whats-new-in-oracle-database-18c#ptf
>
> The idea is that you attach a helper function to the main function. The
> helper function is called at parse time with the constant arguments of
> the main function call and can compute a result row description (a
> TupleDesc in our case).
>
> Example from the patch:
>
> CREATE FUNCTION connectby_describe(internal)
> RETURNS internal
> AS 'MODULE_PATHNAME', 'connectby_describe'
> LANGUAGE C;
>
> CREATE FUNCTION connectby(text,text,text,text,int,text)
> RETURNS setof record
> DESCRIBE WITH connectby_describe
> AS 'MODULE_PATHNAME','connectby_text'
> LANGUAGE C STABLE STRICT;
>
> (The general idea is very similar to Pavel's patch "parse time support
> function"[0] but addressing a disjoint problem.)
>
> The original SQL:2016 syntax is a bit different: There, you'd first
> create two separate functions: a "describe" and a "fulfill" and then
> create the callable PTF referencing those two (similar to how an
> aggregate is composed of several component functions). I think
> deviating from this makes some sense because we can then more easily
> "upgrade" existing record-returning functions with this functionality.
>
> Another difference is that AFAICT, the standard specifies that if the
> describe function cannot resolve the call, the call fails. Again, in
> order to be able to upgrade existing functions (instead of having to
> create a second set of functions with a different name), I have made it
> so that you can still specify an explicit column list if the describe
> function does not succeed.
>
> In this prototype patch, I have written the C interface and several
> examples using existing functions in the source tree. Eventually, I'd
> like to also add PL-level support for this.
>
> Thoughts so far?
>

What I read about it - it can be very interesting feature. It add lot of
dynamic to top queries - it can be used very easy for cross tables on
server side.

Sure - it can be used very badly - but it is nothing new for stored
procedures.

Personally I like this feature. The difference from standard syntax
probably is not problem a) there are little bit syntax already, b) I cannot
to imagine wide using of this feature. But it can be interesting for
extensions.

Better to use some special pseudotype for describe function instead
"internal" - later it can interesting for PL support

Regards

Pavel

>
>
> [0]:
>
> https://www.postgresql.org/message-id/flat/CAFj8pRARh+r4=HNwQ+hws-D6msus01Dw_6zjNYur6tPk1+W0rA(at)mail(dot)gmail(dot)com
>
> --
> Peter Eisentraut http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>