Robins Tharakan <robins.tharakan@gmail.com> writes:
> This is a case where I changed the name of a field in a table that a VIEW
> referred to, but the VIEW definition still points to the old name of the
> field. The surprise is that the VIEW still works (with live data).
Specifically, you mean that you had a column referenced by a USING
clause, and then you renamed it, right? We've had discussions about
that in the past, and concluded that the SQL spec is just fundamentally
broken here. If you rename one of the input columns, there is no way
to represent a view that (used to) use USING without changing the view's
behavior -- in particular, the set of columns exposed by a join with
USING is different from the set of columns exposed without that, so
simply replacing the USING with an ON clause wouldn't get the job done.
So our view-dumping code just doesn't bother to try. You'll get
something that still says USING, but of course this won't work when the
view definition is dumped and reloaded. This is not the fault of the
view: if you'd not used a view but just issued the equivalent join
directly as a SQL query, the rename would still have broken your
application.
Our internal representation doesn't depend on the name-matching aspect
of USING, so the view continues to work as before, so long as you don't
dump and reload. But it looks wrong if you dump the definition as SQL.
That's basically because SQL lacks a way to represent the situation.
The best idea I've heard for fixing it is to invent a non-standard
syntax that could represent a USING clause matching two dissimilarly
named columns, say USING (foo = bar AS baz), and then use that syntax
when dumping a view if the column names don't match. Nobody's worked
out the idea in full detail, though, let alone implemented it; it's not
really clear it's worth the trouble.
regards, tom lane
