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)
