Re: UNNEST with multiple args, and TABLE with multiple funcs

From: Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
To: Boszormenyi Zoltan <zb(at)cybertec(dot)at>, PG Hackers <pgsql-hackers(at)postgresql(dot)org>
Cc: Greg Stark <stark(at)mit(dot)edu>, David Fetter <david(at)fetter(dot)org>, Josh Berkus <josh(at)agliodbs(dot)com>
Subject: Re: UNNEST with multiple args, and TABLE with multiple funcs
Date: 2013-08-26 23:24:58
Message-ID: 57fdbff8ae01c9784d9c095d10008f7d@news-out.riddles.org.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Latest version of patch. This should be it as far as code goes; there
may be some more regression test work, and a doc patch will be
forthcoming.

This version supports, in addition to the previous stuff:

SELECT ... FROM TABLE(func() AS (colname coltype, ...));

i.e. the column definition list required for functions that return
arbitrary RECORD results can go inside the TABLE() construct. This
allows more than one such function in a call:

SELECT ... FROM TABLE(func1() AS (a integer), func2() AS (b text));

or mixing RECORD functions with ORDINALITY:

SELECT ... FROM TABLE(func1() AS (c text)) WITH ORDINALITY;

The existing FROM func() AS f(c text) is still supported of course,
and the variation FROM TABLE(func()) AS f(c text) is also supported
but only when there's exactly one function and no ORDINALITY.

Other changes:

- function dependence on executor parameters is now tracked
per-function, so that on rescan, only affected functions are
re-executed, and others are simply rescanned from the existing
tuplestore

- some cases where deparse or other code broke because an element
of funcexprs was not actually a FuncExpr have been fixed

- fixed the pg_stat_statements issue

A change I _didn't_ include, but did test, was adding mark/restore to
FunctionScan to allow mergejoins on ordinality columns to work without
needing extra nodes (which I did to do some performance tests referred
to in a previous message). I took this code back out because it didn't
seem to make much difference: the planner often (not always) adds the
Materialize node even when it's not needed, in the belief that it is
faster; the overhead of the extra node doesn't seem serious; and the
case is of limited applicability (only useful when joining against
something other than a function using the ordinal column alone).

--
Andrew (irc:RhodiumToad)

Attachment Content-Type Size
table-functions-2.patch text/plain 164.9 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2013-08-27 00:26:29 Re: pg_restore multiple --function options
Previous Message Josh Berkus 2013-08-26 21:25:49 Re: pg_system_identifier()