Hello,
  When I change view and change number of column PostgreSQL return error :
'cannot change number of column in view'
Is it too hard set this command
if view exits drop view
and then change view

It is like with return type in function

Now 'or replace' don't help too much

regards

Thu, 14 Nov 2002 13:41:18 +0000
snpe <snpe@snpe.co.yu>
create or replace view
On Thu, Nov 14, 2002 at 13:41:18 +0000,
  snpe <snpe@snpe.co.yu> wrote:
> Hello,
>   When I change view and change number of column PostgreSQL return error :
> 'cannot change number of column in view'
> Is it too hard set this command
> if view exits drop view
> and then change view
> 
> It is like with return type in function
> 
> Now 'or replace' don't help too much

The create or replace command exists so that you can modify a view in a
way that allows other objects that refer to it to keep working (without
having to recreate those objects). However if you can the number of
columns (and probably any of their types), then these other objects
or going to need to know that things have changed so that you can't
just replace the view.

Thu, 14 Nov 2002 08:41:41 -0600
Bruno Wolff III <bruno@wolff.to>
Re: create or replace view
On Thursday 14 November 2002 02:41 pm, Bruno Wolff III wrote:
> On Thu, Nov 14, 2002 at 13:41:18 +0000,
>
>   snpe <snpe@snpe.co.yu> wrote:
> > Hello,
> >   When I change view and change number of column PostgreSQL return error
> > : 'cannot change number of column in view'
> > Is it too hard set this command
> > if view exits drop view
> > and then change view
> >
> > It is like with return type in function
> >
> > Now 'or replace' don't help too much
>
> The create or replace command exists so that you can modify a view in a
> way that allows other objects that refer to it to keep working (without
> having to recreate those objects). However if you can the number of
> columns (and probably any of their types), then these other objects
> or going to need to know that things have changed so that you can't
> just replace the view.

I undestand that, but if I change number of column I want that
'create or replace view' do 'drop view ..; create view ..;'
Why not ?

regards
Haris Peco

Thu, 14 Nov 2002 16:17:15 +0000
snpe <snpe@snpe.co.yu>
Re: create or replace view
On Thu, 2002-11-14 at 11:17, snpe wrote:
> On Thursday 14 November 2002 02:41 pm, Bruno Wolff III wrote:
> > On Thu, Nov 14, 2002 at 13:41:18 +0000,
> >
> >   snpe <snpe@snpe.co.yu> wrote:
> > > Hello,
> > >   When I change view and change number of column PostgreSQL return error
> > > : 'cannot change number of column in view'
> > > Is it too hard set this command
> > > if view exits drop view
> > > and then change view
> > >
> > > It is like with return type in function
> > >
> > > Now 'or replace' don't help too much
> >
> > The create or replace command exists so that you can modify a view in a
> > way that allows other objects that refer to it to keep working (without
> > having to recreate those objects). However if you can the number of
> > columns (and probably any of their types), then these other objects
> > or going to need to know that things have changed so that you can't
> > just replace the view.
> 
> I undestand that, but if I change number of column I want that
> 'create or replace view' do 'drop view ..; create view ..;'
> Why not ?

Now you've just broken all functions, views, rules, and triggers that
depend on that view to function.

-- 
  Rod Taylor


14 Nov 2002 11:38:59 -0500
Rod Taylor <rbt@rbt.ca>
Re: create or replace view
On Thursday 14 November 2002 04:38 pm, Rod Taylor wrote:
> On Thu, 2002-11-14 at 11:17, snpe wrote:
> > On Thursday 14 November 2002 02:41 pm, Bruno Wolff III wrote:
> > > On Thu, Nov 14, 2002 at 13:41:18 +0000,
> > >
> > >   snpe <snpe@snpe.co.yu> wrote:
> > > > Hello,
> > > >   When I change view and change number of column PostgreSQL return
> > > > error
> > > >
> > > > : 'cannot change number of column in view'
> > > >
> > > > Is it too hard set this command
> > > > if view exits drop view
> > > > and then change view
> > > >
> > > > It is like with return type in function
> > > >
> > > > Now 'or replace' don't help too much
> > >
> > > The create or replace command exists so that you can modify a view in a
> > > way that allows other objects that refer to it to keep working (without
> > > having to recreate those objects). However if you can the number of
> > > columns (and probably any of their types), then these other objects
> > > or going to need to know that things have changed so that you can't
> > > just replace the view.
> >
> > I undestand that, but if I change number of column I want that
> > 'create or replace view' do 'drop view ..; create view ..;'
> > Why not ?
>
> Now you've just broken all functions, views, rules, and triggers that
> depend on that view to function.
But I can simple:
drop view view_name;
create view view_name ...;

I want that 'create or replace view' work drop-create if view exists else only 
create

regards
Haris Peco


Thu, 14 Nov 2002 16:49:42 +0000
snpe <snpe@snpe.co.yu>
Re: create or replace view
On Thursday 14 November 2002 05:01 pm, Bruno Wolff III wrote:
> On Thu, Nov 14, 2002 at 16:49:42 +0000,
>
>   snpe <snpe@snpe.co.yu> wrote:
> > I want that 'create or replace view' work drop-create if view exists else
> > only create
>
> Why do you want this?
>

Why 'create or replace' ?


Thu, 14 Nov 2002 17:00:30 +0000
snpe <snpe@snpe.co.yu>
Re: create or replace view
On Thu, Nov 14, 2002 at 16:49:42 +0000,
  snpe <snpe@snpe.co.yu> wrote:
> 
> I want that 'create or replace view' work drop-create if view exists else only 
> create

Why do you want this?

Thu, 14 Nov 2002 11:01:36 -0600
Bruno Wolff III <bruno@wolff.to>
Re: create or replace view
On Thursday 14 November 2002 05:22 pm, Bruno Wolff III wrote:
> On Thu, Nov 14, 2002 at 17:00:30 +0000,
>
>   snpe <snpe@snpe.co.yu> wrote:
> > On Thursday 14 November 2002 05:01 pm, Bruno Wolff III wrote:
> > > On Thu, Nov 14, 2002 at 16:49:42 +0000,
> > >
> > >   snpe <snpe@snpe.co.yu> wrote:
> > > > I want that 'create or replace view' work drop-create if view exists
> > > > else only create
> > >
> > > Why do you want this?
> >
> > Why 'create or replace' ?
>
> Why do you want create or replace to do a drop, then a create if the view
> exists but it is being changed in a way that will break any objects that
> refer to the old view?
>
> Are you trying to save typing a few characters or what?
Yes, it is 'create or replace view', not ?


Thu, 14 Nov 2002 17:20:18 +0000
snpe <snpe@snpe.co.yu>
Re: create or replace view
On Thu, Nov 14, 2002 at 17:00:30 +0000,
  snpe <snpe@snpe.co.yu> wrote:
> On Thursday 14 November 2002 05:01 pm, Bruno Wolff III wrote:
> > On Thu, Nov 14, 2002 at 16:49:42 +0000,
> >
> >   snpe <snpe@snpe.co.yu> wrote:
> > > I want that 'create or replace view' work drop-create if view exists else
> > > only create
> >
> > Why do you want this?
> >
> 
> Why 'create or replace' ?

Why do you want create or replace to do a drop, then a create if the view
exists but it is being changed in a way that will break any objects that
refer to the old view?

Are you trying to save typing a few characters or what?

Thu, 14 Nov 2002 11:22:59 -0600
Bruno Wolff III <bruno@wolff.to>
Re: create or replace view
snpe <snpe@snpe.co.yu> writes:
> On Thursday 14 November 2002 05:22 pm, Bruno Wolff III wrote:
>> Are you trying to save typing a few characters or what?

> Yes, it is 'create or replace view', not ?

The statement was not invented to save a few characters of typing.
It was invented to allow people to make internal changes to view
definitions without breaking other objects that refer to the view.

If we made it automatically drop and recreate the view then we'd
be defeating the purpose.

			regards, tom lane

Thu, 14 Nov 2002 12:45:51 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: create or replace view 
Tom Lane wrote:
> snpe <snpe@snpe.co.yu> writes:
> 
>>On Thursday 14 November 2002 05:22 pm, Bruno Wolff III wrote:
>>
>>>Are you trying to save typing a few characters or what?
>>
> 
>>Yes, it is 'create or replace view', not ?
> 
> 
> The statement was not invented to save a few characters of typing.
> It was invented to allow people to make internal changes to view
> definitions without breaking other objects that refer to the view.
> 
> If we made it automatically drop and recreate the view then we'd
> be defeating the purpose.

It might just be me but it seems that this discussion is missing the 
point if we believe this request is about saving some characters. I 
don't think it is. I think it's about being able to write simple SQL 
scripts that don't produce errors when you use the syntax below in an 
adminstration or development script and the object doesn't exist:

	drop...
	create...


The accepted syntax in both PG and others for trying to avoiding this 
issue is:

	create or replace....

Using this syntax the database script will run without errors, quietly 
adjusting the object definition as required. Perfect. That's what we want.

Now I'm only interpreting here and haven't run into this problem myself 
in PG but it appears from some of the early posts on this subject that 
PG isn't consistent in whether it will allow the change to occur, at 
least with respect to views. Instead, PG apparently tries to "help" by 
not updating the view if the views' result schema would be different, 
hence the request (perhaps misguided by trying to specify "how" instead 
of "what") to drop/create.


Assuming that's a correct assessment and summary of the problem then 
reviewing the following use cases seems in order:


1. The view doesn't exist.

Action: create the new view


2. The view exists and the change can be determined to be benign, 
presumably because the virtual table schema of the view retains the same 
column specifications (names and types match original specification).

Action: replace the view "in situ" so that dependencies are ok


3. The view exists but the change isn't benign and it's clear that other 
objects referencing the view are going to have issues since column 
names, types, number, etc. are being changed.

Action 3: drop/create the view. Optionally we might consider doing a 
NOTIFY "dependent object references" which might also work nicely in 
other areas such as trigger functions etc.


Why drop/create? (or appropriate similar internal operation). A lot of 
reasons actually.

First, this use case, by definition, says the new view's going to break 
other objects -- and that this will be true regardless of whether I use 
create-replace or drop/create. So not allowing create-replace to operate 
as sugar changes nothing in terms of the resulting schema issues upon 
statement completion. It has a big impact on my SQL though, since 
drop/create may throw errors that create-replace won't. So we haven't 
solved a problem by ignoring case #3. Instead we've continued to require 
developers to use a syntax guaranteed to throw errors. Cool.

Second, if there are other objects depending on the view to look a 
certain way, and I'm knowingly changing the view what can you infer? One 
might choose to infer "The programmer's an idiot for wanting to break 
his schema like this." I see far too much code written from this 
attitude...it's what I hate about most M$ code. I prefer to infer that 
"The programmer's a human being who might just be 10x smarter than 
me...maybe I should let him do his job as he sees fit."

As an aside, this is the UNIX philosophy. Not only do we not try to 
protect you from yourself by taking away all the guns (no command prompt 
etc), we give you a fully loaded semi-automatic weapon (C, shell, etc) 
with the safety off (root) and say "Be careful".

<soapbox>

So, instead of assuming that we know more about what's right than the 
programmer, perhaps we should try assuming that the programmer's next 
SQL script lines will adapt to the new view definition and make the 
appropriate changes -- perhaps via a series of more create or replace 
statements ;). A reasonable developer/DBA should know they're changing 
the view in a way that isn't compatible with previously defined 
dependents, just as they should realize dependencies may exist when they 
alter schema in general. If not, then hey we told you to "Be careful".

The "create or replace" syntax, in my mind anyway, wasn't designed to 
say "If you can create, do so. If you can replace, do so. If you have to 
drop, tell the programmer to bite you" as implied by many of the posts 
on this thread. It has a different goal, one of making the developer or 
DBA's life easier (which occasionally means saving characters BTW. I 
mean, if people weren't concerned about that how can you explain Unix or 
Perl? ;) ).

If we're concerned with this change from a consistency perspective, look 
at triggers. The programmer drops a function and the triggers relying on 
that function go to hell. Sure, and if we said "you can't drop the 
function because triggers might break" then it'd parallel what we're 
saying here -- in effect "we know better than you do what you want". Or 
to use M$ terminology "we know where you want to go today" ;).

Now, if I've misunderstood the problem here I just spent a lot of time 
on a non-issue and wasted a lot of time, for which I apologize. But I 
think the overall philosophy is reusable in any event. I bring it up 
here because I've gotten a distinct sense of disrepect in some of the 
replies on this thread and it disturbs me. If we have any goals for the 
Postgres community they should include:

A. We want the programmer/DBA to have an easier time getting their job 
done and anything we do to that end that is compatible with existing and 
emerging standards is "a good thing". If PG is easier to use it'll get 
used more.

B. We want to treat people who are interested in PostgreSQL with respect 
at all times, keeping in mind that we communicate with them not only 
through this forum, but through the code we write for them.


As a personal note, any time I see a response to my posts consisting of 
  "Why would you want to do that?" I automatically assume the author 
simply left off the implied suffix of "you idiot". It's not a question 
that I feel treats me with respect. I'm sure I'm not alone.

</soapbox>


ss



Scott Shattuck
Technical Pursuit Inc.



Thu, 14 Nov 2002 12:26:53 -0700
Scott Shattuck <ss@technicalpursuit.com>
Re: create or replace view
On Thu, 14 Nov 2002, Scott Shattuck wrote:

> It might just be me but it seems that this discussion is missing the
> point if we believe this request is about saving some characters. I
> don't think it is. I think it's about being able to write simple SQL
> scripts that don't produce errors when you use the syntax below in an
> adminstration or development script and the object doesn't exist:

I think there are two groups of people who have different ideas of what
this functionality is supposed to do. From my understanding of the
discussions on create or replace function, the point really was to do an
in place modification to not need to drop and recreate dependent objects.
Note that afaik you also can't change the return type of a function in a
create or replace if it already exists with a different return type.

The other usage is useful, but I don't think it was the intended way to be
used. I use it that way too, but if I get an error on a create or replace
I do the more involved version (dump dependents if necessary, drop
cascade, create, edit dump, restore).

> If we're concerned with this change from a consistency perspective, look
> at triggers. The programmer drops a function and the triggers relying on
> that function go to hell. Sure, and if we said "you can't drop the
> function because triggers might break" then it'd parallel what we're
> saying here -- in effect "we know better than you do what you want". Or
> to use M$ terminology "we know where you want to go today" ;).

In fact, afaict 7.3 does exactly this unless you use drop cascade.
I don't think that the past way was particularly easier, with needing to
dump/restore dependent objects in order to make them work again. I think
of it like constraints, as much as you can you enforce the constraint.
It's possible that the next statement will make the sequence
work for the constraint, but you don't wait to find out.

> B. We want to treat people who are interested in PostgreSQL with respect
> at all times, keeping in mind that we communicate with them not only
> through this forum, but through the code we write for them.

This is always true. Even if we forget sometimes. :)



Thu, 14 Nov 2002 12:01:49 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: create or replace view
On Thursday 14 November 2002 05:45 pm, Tom Lane wrote:
> snpe <snpe@snpe.co.yu> writes:
> > On Thursday 14 November 2002 05:22 pm, Bruno Wolff III wrote:
> >> Are you trying to save typing a few characters or what?
> >
> > Yes, it is 'create or replace view', not ?
>
> The statement was not invented to save a few characters of typing.
> It was invented to allow people to make internal changes to view
> definitions without breaking other objects that refer to the view.
>
> If we made it automatically drop and recreate the view then we'd
> be defeating the purpose.
>
Does it mean that if I will change any object (view or function) I must
drop all dependent objects ?
example :
  I want change (number of columns) view viewa
If viewb depend of viewa, I must drop and create viewa and viewb ?

Does it possible that viewb stay temporary (or always) invalid ?
 recreate viewa will make viewb valid or pgsql return error for viewb ?

regards
Haris Peco

Thu, 14 Nov 2002 21:32:49 +0000
snpe <snpe@snpe.co.yu>
Re: create or replace view
On Thursday 14 November 2002 08:01 pm, Stephan Szabo wrote:
> On Thu, 14 Nov 2002, Scott Shattuck wrote:
> > It might just be me but it seems that this discussion is missing the
> > point if we believe this request is about saving some characters. I
> > don't think it is. I think it's about being able to write simple SQL
> > scripts that don't produce errors when you use the syntax below in an
> > adminstration or development script and the object doesn't exist:
>
> I think there are two groups of people who have different ideas of what
> this functionality is supposed to do. From my understanding of the
> discussions on create or replace function, the point really was to do an
> in place modification to not need to drop and recreate dependent objects.
> Note that afaik you also can't change the return type of a function in a
> create or replace if it already exists with a different return type.
>
> The other usage is useful, but I don't think it was the intended way to be
> used. I use it that way too, but if I get an error on a create or replace
> I do the more involved version (dump dependents if necessary, drop
> cascade, create, edit dump, restore).
>
> > If we're concerned with this change from a consistency perspective, look
> > at triggers. The programmer drops a function and the triggers relying on
> > that function go to hell. Sure, and if we said "you can't drop the
> > function because triggers might break" then it'd parallel what we're
> > saying here -- in effect "we know better than you do what you want". Or
> > to use M$ terminology "we know where you want to go today" ;).
>
> In fact, afaict 7.3 does exactly this unless you use drop cascade.
> I don't think that the past way was particularly easier, with needing to
> dump/restore dependent objects in order to make them work again. I think
> of it like constraints, as much as you can you enforce the constraint.
> It's possible that the next statement will make the sequence
> work for the constraint, but you don't wait to find out.
>
> > B. We want to treat people who are interested in PostgreSQL with respect
> > at all times, keeping in mind that we communicate with them not only
> > through this forum, but through the code we write for them.
>
> This is always true. Even if we forget sometimes. :)
>
>
Problem is when I want change view (or functions) with a lot of dependecies
I must drop and recreate all dependent views (or functions) - I want add only
one column in view 
I don't know if solution hard for that.

regards
Haris Peco

Thu, 14 Nov 2002 21:45:54 +0000
snpe <snpe@snpe.co.yu>
Re: create or replace view

On Thu, 14 Nov 2002, snpe wrote:

> Problem is when I want change view (or functions) with a lot of dependecies
> I must drop and recreate all dependent views (or functions) - I want add only
> one column in view
> I don't know if solution hard for that.

Well, doing create or replace as a drop/create might very well do the same
thing, and even if it got the same oid, we'd have to be really sure that
nothing would misbehave upon receiving that extra column before allowing
it for purposes of avoiding recreation of dependencies.



Thu, 14 Nov 2002 14:36:34 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: create or replace view
> Problem is when I want change view (or functions) with a lot of
> dependecies
> I must drop and recreate all dependent views (or functions) - I
> want add only
> one column in view
> I don't know if solution hard for that.

This is definitely something that will cause some anguish in 7.3.  I think
7.4 will need the concept of an "invalid object" that can be resurrected...

Chris


Fri, 15 Nov 2002 09:45:26 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: create or replace view
On Thursday 14 November 2002 10:36 pm, Stephan Szabo wrote:
> On Thu, 14 Nov 2002, snpe wrote:
> > Problem is when I want change view (or functions) with a lot of
> > dependecies I must drop and recreate all dependent views (or functions) -
> > I want add only one column in view
> > I don't know if solution hard for that.
>
> Well, doing create or replace as a drop/create might very well do the same
> thing, and even if it got the same oid, we'd have to be really sure that
> nothing would misbehave upon receiving that extra column before allowing
> it for purposes of avoiding recreation of dependencies.
>
>
Can PostgreSQL recreate dependecies automaticly or say 'object is not valid'

regards
Haris Peco

Fri, 15 Nov 2002 02:09:13 +0000
snpe <snpe@snpe.co.yu>
Re: create or replace view
> > Well, doing create or replace as a drop/create might very well
> do the same
> > thing, and even if it got the same oid, we'd have to be really sure that
> > nothing would misbehave upon receiving that extra column before allowing
> > it for purposes of avoiding recreation of dependencies.
> >
> >
> Can PostgreSQL recreate dependecies automaticly or say 'object is
> not valid'

7.3 doesn't do 'object is not valid'

Chris


Fri, 15 Nov 2002 10:14:55 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: create or replace view