Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Bruce Momjian wrote:
> >> So the problem is that our DELETE ... USING does not allow ANSI join
> >> syntax? Can that be added?
>
> > Not sure about that. USING is already an extension to the standard, so
> > if we extend it a bit more, it can't be a problem, can it?
>
> I don't see any very good way to extend the USING syntax to allow the
> target table to be outer-joined to something else. Some other systems
> allow it by letting you re-specify the target in the other clause,
> equivalently to
>
> DELETE FROM target t USING t LEFT JOIN other_table ot ON ...
>
> but we have always considered that the target is *not* to be identified
> with any member of the FROM/USING clause, so it would be a serious
> compatibility break to change that now.
Let's look at this a little closer. We can use an alias in the DELETE
FROM clause:
test=> DELETE FROM test t;test=> DELETE FROM test t USING test;
What we cannot currently do is reference test twice:
test=> DELETE FROM test USING test;ERROR: table name "test" specified more than oncetest=> DELETE FROM test t USING
testt;ERROR: table name "t" specified more than once
As far as I understand it, allowing ANSI joins in USING would simple
mean removing that error message and linking the two table aliases.
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +