Robins Tharakan <robins.tharakan@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
