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?
[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