Inline comments: On Aug 15, 2012, at 16:24, David Greco <david_gr...@harte-hanks.com> wrote:
> Not sure how to write the CTE form of this. This query gives an error that > dave is missing a from clause entry. > WITH o as ( > SELECT getRecord(1, dave.field1) > ) > SELECT > id, o.* > FROM > dave > ; > > With o (id, result) as (select id, getRecord(...) from dave) Select id, (o.result).* From dave Left? Join o Using (id) > > Regarding the INNER JOIN, actually it appears the opposite is true: > SELECT > id, generate_series(1, dave.field1) > FROM > Dave > Works great if generate_series returns rows, but does not return any rows if > generate_series does not return rows (i.e. if dave has a row with > field1=null). So in fact, I need to figure out how to rewrite a LEFT JOIN > version of this query Agreed. See above example that can handle both. > > > From: David Johnston [mailto:pol...@yahoo.com] > Sent: Wednesday, August 15, 2012 4:16 PM > To: David Greco > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Set Returning Functions and joins > > On Aug 15, 2012, at 15:55, David Greco <david_gr...@harte-hanks.com> wrote: > > 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) > > > I suggest putting your function calls within a CTE (common table expression: > SQL command WITH) structure. > > I do not get why you think you need an "EXISTS" in the INNER JOIN situation. > At worse you should make it a sub-query and add a IS NOT NULL condition on > one of the function result columns. There is no way to avoid evaluating once > per record in dave but you should never have to evaluate more frequently than > that. CTE and sub-selects are your friends. > > The original form is currently being developed but will not be available > until at least 9.3 > > David J. > > > > > >
