Not sure how to write the CTE form of this. This query gives an error that dave
is missing a from clause entry.
WITH o as (
SELECT getRecord(1, dave.field1)
)
SELECT
id, o.*
FROM
dave
;
Regarding the INNER JOIN, actually it appears the opposite is true:
SELECT
id, generate_series(1, dave.field1)
FROM
Dave
Works great if generate_series returns rows, but does not return any rows if
generate_series does not return rows (i.e. if dave has a row with field1=null).
So in fact, I need to figure out how to rewrite a LEFT JOIN version of this
query.
From: David Johnston [mailto:[email protected]]
Sent: Wednesday, August 15, 2012 4:16 PM
To: David Greco
Cc: [email protected]
Subject: Re: [GENERAL] Set Returning Functions and joins
On Aug 15, 2012, at 15:55, David Greco
<[email protected]<mailto:[email protected]>> wrote:
I’m porting some code from an Oracle application and we have many uses of set
returning function. In particular, we are using them in joins of the form:
CREATE TABLE dave ( id integer, field1 integer );
INSERT INTO dave VALUES (1, 10);
SELECT
id, g.*
FROM
dave
INNER JOIN generate_series( 1, dave.field1 ) ON (1=1)
In reality, the examples are not trivial like this, and the set returning
function returns sets of records, not single values.
Now, in the case of a LEFT JOIN and a function returning a setoff a simple
value, I can rewrite it simply as:
SELECT
id, generate_series(1, dave.field1)
FROM
dave
In the case of a LEFT JOIN and a function returning a setoff a record, I can
rewrite it as:
SELECT
id, ( getRecord(1, dave.field1) ).*
FROM
dave
I then figured I can rewrite INNER JOINs as:
SELECT
id, ( getRecord(1, dave.field1) ).*
FROM
dave
WHERE
Exists ( SELECT 1 FROM getRecord(1, dave.field1) )
Though I suppose this is running getRecord once for every row in dave, then
another time for every row being returned.
Now in some non-trivial examples involving multiple joins on set returning
functions, this gets pretty complicated.
Is there any alternative? Or I can suggest that a query the original form
should be allowed?
SELECT
id, g.*
FROM
dave
INNER JOIN generate_series( 1, dave.field1 ) ON (1=1)
I suggest putting your function calls within a CTE (common table expression:
SQL command WITH) structure.
I do not get why you think you need an "EXISTS" in the INNER JOIN situation.
At worse you should make it a sub-query and add a IS NOT NULL condition on one
of the function result columns. There is no way to avoid evaluating once per
record in dave but you should never have to evaluate more frequently than that.
CTE and sub-selects are your friends.
The original form is currently being developed but will not be available until
at least 9.3
David J.
