Tom and Stephan, thank you both for taking the time to reply. Further comments inline. Tom Lane wrote: > Stephan Szabo <sszabo@xxxxxxxxxxxxxxxxxxxxx> writes: >> On Fri, 11 Feb 2005, Guy Rouillier wrote: >>> Now I create a view like this: >>> create or replace view v1 as select * from t1; >>> >>> Next, I attempt to update this view like this: >>> create or replace view v1 as select * from t1 union select * from >>> t2; >>> >>> I receive: ERROR: cannot change data type of view column "serv_id" > >> I'm pretty sure PostgreSQL treats the type of serv_id in the new view >> as varchar with no limit rather than varchar(50). > > This is the same issue reported here: > http://archives.postgresql.org/pgsql-general/2004-12/msg00508.php > http://archives.postgresql.org/pgsql-hackers/2004-12/msg00408.php > and is pretty closely related to this: > http://archives.postgresql.org/pgsql-bugs/2005-02/msg00008.php 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. 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". > > I was looking at fixing it yesterday. The obvious path to a fix is > to do select_common_type across all the members of a set-operation > tree at once, rather than pairwise as we do it now. That bothers me > a bit because it could change the semantics. 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. -- Guy Rouillier ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
