"Guy Rouillier" <guyr@xxxxxxxxxxx> writes: > We appear to have two issues here: > (1) What is the meaning of "replace" as in replace view? > (2) What are the semantics for multiple set operations? > My original issue deals with (1). I'm unfortunately not well versed in > the SQL spec, but from a layman's point of view, if I'm replacing a view > definition, I don't expect that to be rejected because it is > incompatible with the original view definition. Well, we don't have the ability to cope with redefining a view in a way that changes its result column list --- we don't know how to propagate that into dependent views in any clean way. (Now, ALTER TABLE deals with this by allowing the change only when there are no dependent views; maybe it'd be sensible to do the same for REPLACE VIEW.) The immediate issue here is that the redefinition shouldn't be seen as changing the result column type. The underlying cause of that hasn't got anything to do with views per se, it has to do with the way that we impute a result datatype to a UNION structure. > My new definition may > use entirely different set of tables than the original view. "create or > replace view" should accomplish the same thing as "drop view; create > view". In the cases I'm concerned about, DROP VIEW would fail unless you used CASCADE, and the subsequent CREATE VIEW would not magically bring back the dependent objects the CASCADE had killed. The entire *point* of REPLACE VIEW is that it does not have the side effect of destroying dependent objects; and therefore it has to act in a way that ensures the dependent objects still make sense. So, for the moment anyway, that means disallowing changes in the output column names and datatypes. >> Consider for example >> >> SELECT float8col UNION ALL (SELECT numericcol UNION SELECT >> numericcol) > This seems to be addressing point (2). In this particular case, I would > expect the query engine to follow the submitter's explicit directions, > since he or she provided an explicit grouping. If no parentheses are > included, then the optimizer or query rewriter is free to do as it > wishes. I don't think we get to do it that way. The way the SQL spec is written, there is not a difference between A UNION B UNION C and (A UNION B) UNION C. The result is *always* built up from pairwise UNIONs, and the semantic definition of UNION talks about combining only two tables at a time. This consideration doesn't stop us from fixing your problem (since in the case you care about, all the datatypes are the same anyway) ... but it puts rather a large crimp in Woehling's problem, which I was hoping to fix at the same time. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
