Обсуждение: Strict Set Returning Functions
STRICT functions return NULL if any of their inputs are NULL according to the manual, so that they need not be executed at all. Unless it is a Set Returning Function, in which case a NULL input is not reduced nor does it to appear to be handled as a special case in the executor function scan code. So a function that is both STRICT and SET RETURNING will return rows. Presumably this is just a case of missing documentation? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Simon Riggs <simon@2ndQuadrant.com> writes: > STRICT functions return NULL if any of their inputs are NULL according > to the manual, so that they need not be executed at all. > Unless it is a Set Returning Function, in which case a NULL input is > not reduced nor does it to appear to be handled as a special case in > the executor function scan code. > So a function that is both STRICT and SET RETURNING will return rows. Really? The case behaves as expected for me. regards, tom lane
On Wed, Jun 15, 2011 at 8:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Simon Riggs <simon@2ndQuadrant.com> writes: >> STRICT functions return NULL if any of their inputs are NULL according >> to the manual, so that they need not be executed at all. > >> Unless it is a Set Returning Function, in which case a NULL input is >> not reduced nor does it to appear to be handled as a special case in >> the executor function scan code. > >> So a function that is both STRICT and SET RETURNING will return rows. > > Really? The case behaves as expected for me. Seems that's the wrong question. Let me return to why I raised this: Why does evaluate_function() specifically avoid returning NULL for a set returning function? It could easily do the NULL test first, so it was applied to all function types. That seems strange. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Simon Riggs <simon@2ndQuadrant.com> writes: > On Wed, Jun 15, 2011 at 8:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Simon Riggs <simon@2ndQuadrant.com> writes: >>> So a function that is both STRICT and SET RETURNING will return rows. >> Really? The case behaves as expected for me. > Seems that's the wrong question. Let me return to why I raised this: > Why does evaluate_function() specifically avoid returning NULL for a > set returning function? Because replacing the SRF call with a constant NULL would produce the wrong result, ie, a single row containing NULL, not zero rows. regards, tom lane
On Thu, Jun 16, 2011 at 12:24 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Simon Riggs <simon@2ndQuadrant.com> writes: >> On Wed, Jun 15, 2011 at 8:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> Simon Riggs <simon@2ndQuadrant.com> writes: >>>> So a function that is both STRICT and SET RETURNING will return rows. > >>> Really? The case behaves as expected for me. > >> Seems that's the wrong question. Let me return to why I raised this: > >> Why does evaluate_function() specifically avoid returning NULL for a >> set returning function? > > Because replacing the SRF call with a constant NULL would produce the > wrong result, ie, a single row containing NULL, not zero rows. OK, thanks. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services