Thread: What is the point of create or replace view command
What is the point of the create or replace view command if you can't change the column and data types ? Chris -- Chris Velevitch Manager - Sydney Flash Platform Developers Group www.flashdev.org.au
Chris Velevitch wrote: > What is the point of the create or replace view command if you can't > change the column and data types ? You could have a calculated column and change the calcuation. You could change the sort order, selection, or grouping clauses. Just to name two.
"Chris Velevitch" <chris.velevitch@gmail.com> writes:
> What is the point of the create or replace view command if you can't
> change the column and data types ?
You might as well ask what's the point of CREATE OR REPLACE FUNCTION,
when it doesn't let you change the function's parameter list or result
type. The analogy to result type is pretty exact, in fact.
regards, tom lane
On 6/5/06, Berend Tober wrote: > Chris Velevitch wrote: > > What is the point of the create or replace view command if you can't > > change the column and data types ? > > You could have a calculated column and change the calcuation. > > You could change the sort order, selection, or grouping clauses. That's what I'm trying to do but the command won't let me. So what's the point of having such a if it won't replace the previous definition. I ended up having to drop and the create it again. I'm trying to understand the reasoning behind the limit in functionality of the command. With my current understanding, it's very useful and should be dropped from postgres. Chris -- Chris Velevitch Manager - Sydney Flash Platform Developers Group www.flashdev.org.au
On 6/5/06, Tom Lane wrote: > "Chris Velevitch" <chris.velevitch@gmail.com> writes: > > What is the point of the create or replace view command if you can't > > change the column and data types ? > > You might as well ask what's the point of CREATE OR REPLACE FUNCTION, > when it doesn't let you change the function's parameter list or result > type. The analogy to result type is pretty exact, in fact. Yes, please, what is point of CREATE OR REPLACE FUNCTION, when it doesn't let you change the function's parameter list or result type? Chris -- Chris Velevitch Manager - Sydney Flash Platform Developers Group www.flashdev.org.au
Chris Velevitch wrote: > On 6/5/06, Tom Lane wrote: > >> "Chris Velevitch" <chris.velevitch@gmail.com> writes: >> > What is the point of the create or replace view command if you can't >> > change the column and data types ? >> >> You might as well ask what's the point of CREATE OR REPLACE FUNCTION, >> when it doesn't let you change the function's parameter list or result >> type. The analogy to result type is pretty exact, in fact. > > > Yes, please, what is point of CREATE OR REPLACE FUNCTION, when it > doesn't let you change the function's parameter list or result type? > > The way that user-created functions work allows one to have the same function name with different numbers & types of parameters. Thus, you must specify the param list when replacing. Now, having default values for params might be nice. Is that possible? brian
Chris Velevitch wrote: > Yes, please, what is point of CREATE OR REPLACE FUNCTION, when it > doesn't let you change the function's parameter list or result type? Being able to change the _logic_ of a function is certainly useful. Well, I find it so, at least - YMMV. Tim -- ----------------------------------------------- Tim Allen tim@proximity.com.au Proximity Pty Ltd http://www.proximity.com.au/
On 6/5/06, brian ally wrote:
> Chris Velevitch wrote:
> > Yes, please, what is point of CREATE OR REPLACE FUNCTION, when it
> > doesn't let you change the function's parameter list or result type?
>
> The way that user-created functions work allows one to have the same
> function name with different numbers & types of parameters. Thus, you
> must specify the param list when replacing.
Ok, so postgres allows function overloading. That makes that if you
need to change the parameter list or result type, so you need to drop
the function to change teh parameter list or result type.
But what about my original question?
"What is the point of the create or replace view command if you
can't change the
column and data types?"
Chris
--
Chris Velevitch
Manager - Sydney Flash Platform Developers Group
www.flashdev.org.au
Chris Velevitch wrote: > But what about my original question? > > "What is the point of the create or replace view command if you > can't change the > column and data types?" -- create table t1 create table t1(f int); -- create view v1 based on table t1 create view v1 as select * from t1; -- create view v2 based on view v1 create view v2 as select * from v1; -- attempt to drop and recreate view v1 with a WHERE clause drop view v1; -- attempt to create or replace view v1 with a WHERE clause create or replace view v1 as select * from t1 where 1 = 1; -- attempt to create or replace view v1 with a different WHERE clause create or replace view v1 as select * from t1 where 1 = 2; HTH, Joe
On 6/5/06, Joe Conway wrote: > Chris Velevitch wrote: > > But what about my original question? > > > > "What is the point of the create or replace view command if you > > can't change the > > column and data types?" > > -- create table t1 > create table t1(f int); > -- create view v1 based on table t1 > create view v1 as select * from t1; > -- create view v2 based on view v1 > create view v2 as select * from v1; > -- attempt to drop and recreate view v1 with a WHERE clause > drop view v1; > -- attempt to create or replace view v1 with a WHERE clause > create or replace view v1 as select * from t1 where 1 = 1; > -- attempt to create or replace view v1 with a different WHERE clause > create or replace view v1 as select * from t1 where 1 = 2; Sorry, I don't understand what you are trying to say as these examples don't change the number of columns, type of column or column names. So according to the description of the command, these will work. Chris -- Chris Velevitch Manager - Sydney Flash Platform Developers Group www.flashdev.org.au
Chris Velevitch wrote: > On 6/5/06, Berend Tober wrote: > >> Chris Velevitch wrote: >> > What is the point of the create or replace view command if you can't >> > change the column and data types ? >> >> You could have a calculated column and change the calcuation. >> >> You could change the sort order, selection, or grouping clauses. > > > That's what I'm trying to do but the command won't let me. So what's > the point of having such a if it won't replace the previous > definition. I ended up having to drop and the create it again. Chaning the calculation doesn't necessarily mean changing the result data type -- what I was referring to was changing the calculation embedded in the view column definition, like, for example, if you missed a minus sign in an arithmetic formula, or wanted to concatinate middle name along with first and last names in a string column. If you drop the view, then you also lose any rules and defaults defined for the view, too, and so would have to recreate all that extra stuff. REPLACEing a view doesn't interfere with these other definitions but allows you to change the details of the column definitions. > I'm > trying to understand the reasoning behind the limit in functionality > of the command. With my current understanding, it's very useful and > should be dropped from postgres. I think we would have to make a more detailed assessment of your current understanding before finalizing that course of action. :) -- BMT
# chris.velevitch@gmail.com / 2006-06-05 16:19:19 +1000:
> On 6/5/06, Joe Conway wrote:
> >Chris Velevitch wrote:
> >> But what about my original question?
> >>
> >> "What is the point of the create or replace view command if you
> >> can't change the
> >> column and data types?"
> >
> >-- create table t1
> >create table t1(f int);
> >-- create view v1 based on table t1
> >create view v1 as select * from t1;
> >-- create view v2 based on view v1
> >create view v2 as select * from v1;
> >-- attempt to drop and recreate view v1 with a WHERE clause
> >drop view v1;
> >-- attempt to create or replace view v1 with a WHERE clause
> >create or replace view v1 as select * from t1 where 1 = 1;
> >-- attempt to create or replace view v1 with a different WHERE clause
> >create or replace view v1 as select * from t1 where 1 = 2;
>
> Sorry, I don't understand what you are trying to say as these examples
> don't change the number of columns, type of column or column names. So
> according to the description of the command, these will work.
The point of the create or replace view command is that you may
need to change other attributes of the view (see Joe's examples),
and then the [OR REPLACE] clause is useful.
Now, I don't disagree that being able to CREATE OR REPLACE even
when the "interface" changes wouldn't be useful. You're welcome
to provide a patch that does this at least when there are no objects
that depend on the view in question.
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991