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)
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" |