Обсуждение: Adding support for a fully qualified column-name in UPDATE ... SET
The accepted syntax for UPDATE ... SET does not currently permit the column name to be qualified by schema.table or table or correlation-name, as is permitted in other systems. This is apparently due to the syntax that PostgreSQL accepts for composite columns, which would create an ambiguity in the grammar if both SET t.c [ opt_indirection ] = value, or SET c.f [ opt_indirection ] = value, were both allowed. As a result, databases migrated from several other commercial database servers to PostgreSQL must be "cleaned up" to reconcile these differences. This can be time consuming and unnecessary. This can be disambiguated during semantic analysis in all but the most contrived cases. The current behavior has been documented as follows: column_name The name of a column in the table named by table_name. The column name can be qualified with a subfield name or array subscript, if needed. Do not include the table's name in the specification of a target column — for example, UPDATE table_name SET table_name.col = 1 is invalid. If the community is willing to extend this behavior to support optional schema-name . table-name, table-name, or correlation-name, we can discuss solutions in this thread. thank you, /Jim ----- Jim Finnerty, AWS, Amazon Aurora PostgreSQL -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
Jim Finnerty <jfinnert@amazon.com> writes: > The accepted syntax for UPDATE ... SET does not currently permit the column > name to be qualified by schema.table or table or correlation-name, as is > permitted in other systems. This is apparently due to the syntax that > PostgreSQL accepts for composite columns, which would create an ambiguity in > the grammar if both SET t.c [ opt_indirection ] = value, or SET c.f [ > opt_indirection ] = value, were both allowed. > As a result, databases migrated from several other commercial database > servers to PostgreSQL must be "cleaned up" to reconcile these differences. > This can be time consuming and unnecessary. > This can be disambiguated during semantic analysis in all but the most > contrived cases. I don't think it'd really be a good idea to allow "SET x.y = ..." to mean two (or more?) completely different things depending on context. That's just a recipe for shooting yourself in the foot. Your claim that ambiguity would arise only in contrived cases seems way over-optimistic. The case for doing something would be stronger if the SQL spec allowed qualified column names here. But AFAICS it does not, for pretty much the same reason we don't: it thinks "x.y" is an assignment to subcolumn y of composite column x --- or at least I think that's what the impenetrable verbiage around "mutated set clause" means. regards, tom lane
Considering first just the addition of the table-name or correlation-name, without a schema-name qualifier: UPDATE table-name [ AS correlation-name ] SET [ { table-name | correlation-name } '.' ] column-name opt_indirection '=' value-expression ';' we take the first sql92identifier after the SET as the presumed column-name, and we create a list for the indirection elements, if any. So for the ambiguous case (t.c versus c.f), the first token must match the table-name (or correlation-name, if supplied), and the second token must be the name of a column of table t (i.e. that t.t exists), and also t.c must have a composite type. If all of these are true, we can parse this as c.f, just as we do now, so existing PostgreSQL applications would have the same semantics. if an Oracle application has an UPDATE statement written as: myTable.myTable = scalarValue when myTable is a column of myTable, and myTable.myTable has a composite type, but scalarValue is not coercible into the full composite value of that type, then you'd get an error. So, don't do that, because we will interpret the meaning of this corner case like PostgreSQL has always interpreted it. Adding the optional schema-name doesn't expand the scope of that corner case very much, although there are the usual issues with search_path. ----- Jim Finnerty, AWS, Amazon Aurora PostgreSQL -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html