Re: Pseudo modification of views and triggers (again)

> -----Original Message-----
> From: Jean-Michel POURE [mailto:jm(dot)poure(at)freesurf(dot)fr]
> Sent: 19 February 2002 19:53
> To: Dave Page
> Cc: pgadmin-hackers(at)postgresql(dot)org
> Subject: Pseudo modification of views and triggers (again)
>
>
> Dear Dave,
>
> 1) Views
> View pseudo-modification works fine. It was implemented in
> pgSchema->pgView->Let Defition property. This allows the
> modification of
> pgSchema->pgView->view
> definition only (not the name of the view).

The code looks great. I've made a couple of minor changes:

- In pgSchema, I've added code to commit the change to the RCS. This was
also missing from the Function modification code.

- In pgAdmin.frmView:Initialise I've moved the code that unlocks the hbx.
This is to be consistant with other similar functions.

- In pgAdmin.frmView:cmdOK_Click I've removed the code that saved the
comment before updating the definition. Just updating the comment first
should do fine.

> This is OK because view names should not be changed (views
> can be called
> inside functions or other views).

I've just found out that views can be renamed using ALTER TABLE <viewname>
RENAME... iirc, VIEWs are referred to by OID from PL/SQL functions & other
views, and by name from pl/pgsql functions.

Sequences can also be renamed in this way.

It might be an idea if you warned the user that some things will break when
the view is redefined in cmdOK_Click, and give them a chance to abort.

>
> The problem with pgSchema->pgView->Let Defition is that if a
> wrong definition
> is entered, an error is reported and the definition is saved
> in PostgreSQL.
> This ***might*** be a new feature of PostgreSQL 7.2. I don't
> remember 7.1
> accepted wrong definitions for views, but I may be wrong.
>
> We need to rollback views in such a case.
>
> Does this means implementing view modification at
> pgSchema->View level?

7.2:

helpdesk=# create view cow as select widget from notable;
ERROR: Relation "notable" does not exist
ERROR: Relation "notable" does not exist
helpdesk=# create view cow as select widget from pg_class;
ERROR: Attribute 'widget' not found
ERROR: Attribute 'widget' not found
helpdesk=# create view cow as selects * from pg_class;
ERROR: parser: parse error at or near "selects"
ERROR: parser: parse error at or near "selects"
helpdesk=#

I would suggest (in pgSchema) saving the definition in a local variable
before the update attempt, then if an error occurs, re-run the old SQL.

NOTE: I also noticed that you do not retrieve the new OID. This could be
done along with the definition.

> 2) Triggers
> It should be possible to change the name of a trigger as well as the
> underlying function, but not the trigger table itself.
> Therefore, it sounds
> like trigger pseudo modification should be implemented in
> pgSchema->Triggers.
> What is your opinion?

Triggers could be implemented exactly as Views. Each property will need to
be handled seperately, though in the future we might look into merging
multiple updates into one.

The Name property must be handled differently (as it would be for Views &
Sequences) in the Collection class. This has already been done for tables,
so there is some example code to steal.

Regards, Dave.