Set Returning Functions and joins

From: David Greco <David_Greco(at)harte-hanks(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Set Returning Functions and joins
Date: 2012-08-15 19:55:20
Message-ID: 187F6C10D2931A4386EE8E58E13857F609EFCA5E@BY2PRD0811MB415.namprd08.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Johnston 2012-08-15 20:15:41 Re: Set Returning Functions and joins
Previous Message Carl von Clausewitz 2012-08-15 19:19:04 Re: corrupted statistics file "pg_stat_tmp/pgstat.stat"