Re: Set Returning Functions and joins

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)