Disallow LATERAL references to the target table of an UPDATE/DELETE.

On second thought, commit 0c051c90082da0b7e5bcaf9aabcbd4f361137cdc was
over-hasty: rather than allowing this case, we ought to reject it for now.
That leaves the field clear for a future feature that allows the target
table to be re-specified in the FROM (or USING) clause, which will enable
left-joining the target table to something else.  We can then also allow
LATERAL references to such an explicitly re-specified target table.
But allowing them right now will create ambiguities or worse for such a
feature, and it isn't something we documented 9.3 as supporting.

While at it, add a convenience subroutine to avoid having several copies
of the ereport for disalllowed-LATERAL-reference cases.

2 files changed, 25 insertions, 36 deletions

diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 9adfac5ef67..74bc8ead26c 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4105,17 +4105,7 @@ LINE 1: select 1 from tenk1 a, lateral (select max(a.unique1) from i...
                                                ^
 -- check behavior of LATERAL in UPDATE/DELETE
 create temp table xx1 as select f1 as x1, -f1 as x2 from int4_tbl;
-select * from xx1;
-     x1      |     x2      
--------------+-------------
-           0 |           0
-      123456 |     -123456
-     -123456 |      123456
-  2147483647 | -2147483647
- -2147483647 |  2147483647
-(5 rows)
-
--- error, can't do this without LATERAL:
+-- error, can't do this:
 update xx1 set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
 ERROR:  column "x1" does not exist
 LINE 1: ... set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
@@ -4126,28 +4116,28 @@ ERROR:  invalid reference to FROM-clause entry for table "xx1"
 LINE 1: ...t x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss...
                                                              ^
 HINT:  There is an entry for table "xx1", but it cannot be referenced from this part of the query.
--- OK:
+-- can't do it even with LATERAL:
 update xx1 set x2 = f1 from lateral (select * from int4_tbl where f1 = x1) ss;
-select * from xx1;
-     x1      |     x2      
--------------+-------------
-           0 |           0
-      123456 |      123456
-     -123456 |     -123456
-  2147483647 |  2147483647
- -2147483647 | -2147483647
-(5 rows)
-
--- error:
+ERROR:  invalid reference to FROM-clause entry for table "xx1"
+LINE 1: ...= f1 from lateral (select * from int4_tbl where f1 = x1) ss;
+                                                                ^
+HINT:  There is an entry for table "xx1", but it cannot be referenced from this part of the query.
+-- we might in future allow something like this, but for now it's an error:
+update xx1 set x2 = f1 from xx1, lateral (select * from int4_tbl where f1 = x1) ss;
+ERROR:  table name "xx1" specified more than once
+-- also errors:
 delete from xx1 using (select * from int4_tbl where f1 = x1) ss;
 ERROR:  column "x1" does not exist
 LINE 1: ...te from xx1 using (select * from int4_tbl where f1 = x1) ss;
                                                                 ^
 HINT:  There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
--- OK:
+delete from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss;
+ERROR:  invalid reference to FROM-clause entry for table "xx1"
+LINE 1: ...from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss...
+                                                             ^
+HINT:  There is an entry for table "xx1", but it cannot be referenced from this part of the query.
 delete from xx1 using lateral (select * from int4_tbl where f1 = x1) ss;
-select * from xx1;
- x1 | x2 
-----+----
-(0 rows)
-
+ERROR:  invalid reference to FROM-clause entry for table "xx1"
+LINE 1: ...xx1 using lateral (select * from int4_tbl where f1 = x1) ss;
+                                                                ^
+HINT:  There is an entry for table "xx1", but it cannot be referenced from this part of the query.
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index e2bf915be06..409e0b13d62 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1151,17 +1151,16 @@ select 1 from tenk1 a, lateral (select max(a.unique1) from int4_tbl b) ss;
 -- check behavior of LATERAL in UPDATE/DELETE
 
 create temp table xx1 as select f1 as x1, -f1 as x2 from int4_tbl;
-select * from xx1;
 
--- error, can't do this without LATERAL:
+-- error, can't do this:
 update xx1 set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
 update xx1 set x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss;
--- OK:
+-- can't do it even with LATERAL:
 update xx1 set x2 = f1 from lateral (select * from int4_tbl where f1 = x1) ss;
-select * from xx1;
+-- we might in future allow something like this, but for now it's an error:
+update xx1 set x2 = f1 from xx1, lateral (select * from int4_tbl where f1 = x1) ss;
 
--- error:
+-- also errors:
 delete from xx1 using (select * from int4_tbl where f1 = x1) ss;
--- OK:
+delete from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss;
 delete from xx1 using lateral (select * from int4_tbl where f1 = x1) ss;
-select * from xx1;