Hi,
I found "Do not include the table's name in the specification of a target column
— for example, UPDATE tab SET tab.col = 1 is invalid." in the documentation.
Some people usually like to update table by alias. They want to add this feature.
So I get the source in the gram.y, and I found that they just take the alias as the name of column.
So I think we could analyze whether the "indirection" is alias or other.
The attachment is my patch to support - update table by alias:
CREATE TABLE update_test1 (
a INT, b INT, c TEXT
);
INSERT INTO update_test1 VALUES (1, 10, 'aa'),(2, 11, 'bb'),(3, 12, 'cc'),(4, 13, 'dd');
SELECT * FROM update_test1;
a | b | c
---+----+----
1 | 10 | aa
2 | 11 | bb
3 | 12 | cc
4 | 13 | dd
(4 rows)
UPDATE update_test1 SET tb1.a = 4 WHERE a = 1;
ERROR: column "a" of relation "tb1" does not exist
LINE 1: UPDATE update_test1 SET tb1.a = 4 WHERE a = 1;
^
UPDATE update_test1 tb1 SET tb1.f = 4 WHERE tb1.a = 1;
ERROR: column "f" of relation "tb1" does not exist
LINE 1: UPDATE update_test1 tb1 SET tb1.f = 4 WHERE tb1.a = 1;
^
UPDATE update_test1 tb SET tb1.a = 4 WHERE tb.a = 1;
ERROR: Perhaps you meant to reference the table alias "tb".
UPDATE update_test1 tb SET tb1.f = 4 WHERE tb.a = 1;
ERROR: Perhaps you meant to reference the table alias "tb".
UPDATE update_test1 tb1 SET tb1.a = 5 WHERE tb1.a = 1;
UPDATE update_test1 tb1 SET update_test1.a = 6 WHERE tb1.a = 2;
UPDATE update_test1 SET update_test1.a = 7 WHERE a = 3;
UPDATE update_test1 tb1 SET a = 8 WHERE a = 4;
SELECT * FROM update_test1;
a | b | c
---+----+----
5 | 10 | aa
6 | 11 | bb
7 | 12 | cc
8 | 13 | dd
(4 rows)
2016-04-07
WANGSHUO
A PGer