Set Returning Functions and joins

[David Greco <David_Greco@xxxxxxxxxxxxxxx>]

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)