Hi po 16. 12. 2019 v 19:53 odesÃlatel Peter Eisentraut < peter.eisentr...@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+w...@mail.gmail.com > > -- > Peter Eisentraut http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
