Обсуждение: 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