Message-Id: <5.1.0.14.2.20040921091713.0517f218@postgresql3.ultimeth.com>
Date: Tue, 21 Sep 2004 09:19:36 -0700
To: pgsql-sql@postgresql.org
From: "Dean Gibson (DB Administrator)" <postgresql3@ultimeth.com>
Subject: Re: Different topic 
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed

Ok, this is the third time I've sent this eMail;  the other two times the 
mail was accepted (as shown by my postfix logs), but never appeared on the 
list.  So, this time I've changed the subject field (was JOIN performance):

Tom Lane wrote on 2004-09-20 22:19:
>In 7.3 only a view whose targetlist consists of simple column references 
>can be pulled up into the nullable side of an outer join.

OK, well you dragged me kicking and screaming into writing and 'end_date' 
function (IMMUTABLE but non-STRICT).  I then use that in the JOINed VIEW 
(and other strategic places) rather than in the subject of the LEFT JOIN, 
and that works with no performance problems.

Since we mentioned PostgreSQL versions, one of the things I'd like to see 
in v8.0 is support for "DELETE FROM ... AS ..." and "UPDATE ... AS 
...".  For example, I need to do things like:

DELETE FROM xxx AS x WHERE field > xxx.field;

In other words, a DELETE that has criteria similar to a self JOIN.

Right now, I do this via:

CREATE VIEW yyy AS SELECT * FROM xxx;

DELETE FROM xxx WHERE field > yyy.field;

And that works fine.

But it's probably too late for v8.0 ...

-- Dean