Re: polymorphic table functions light - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: polymorphic table functions light
Date
Msg-id CAFj8pRC+hNzpH+0bPRCnqNncUCGjEvpwX+0nbhb1F7gwjYZZNg@mail.gmail.com
Whole thread Raw
In response to polymorphic table functions light  (Peter Eisentraut <peter.eisentraut@2ndquadrant.com>)
List pgsql-hackers
Hi

po 16. 12. 2019 v 19:53 odesílatel Peter Eisentraut <peter.eisentraut@2ndquadrant.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@mail.gmail.com

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

pgsql-hackers by date:

Previous
From: Juan José Santamaría Flecha
Date:
Subject: Re: Windows port minor fixes
Next
From: Robert Haas
Date:
Subject: Re: non-exclusive backup cleanup is mildly broken