INSERT/UPDATE/DELETE Views

I'm relatively new to PostgreSQL. I was wondering why were
INSERT/UPDATE/DELETE view diabled in PostgreSQL?

They are still available but you must write your own
triggers/rules/functions for them.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

On Tue, May 20, 2003 at 01:01:01PM +0300, Kaarel wrote:

Hi

I'm relatively new to PostgreSQL. I was wondering why were
INSERT/UPDATE/DELETE view diabled in PostgreSQL? Other database systems
have these features so why shouldn't PostgreSQL. I did some searching
and found that prior version 7.1 or 7.0 these features were in fact
enabled. I couldn't however find a satisfying reason for the removal of
these features from PostqreSQL mailing list archives.

You can define rules to make insert, update and delete work on views. I'm
surprised it was ever enabled by default since in the general case it is
impossible to workout what the rules should be.

Consider the view:

select * from a, b where a.id = b.id;

If you inserted into that, what should happen? So Postgresql leaves to option
upto the admin.

--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Tuesday 20 May 2003 12:01, Kaarel wrote:

Hi

I'm relatively new to PostgreSQL. I was wondering why were
INSERT/UPDATE/DELETE view diabled in PostgreSQL? Other database systems
have these features so why shouldn't PostgreSQL. I did some searching
and found that prior version 7.1 or 7.0 these features were in fact
enabled. I couldn't however find a satisfying reason for the removal of
these features from PostqreSQL mailing list archives.

Postgres had, and still have update, delete and insert rules, which can be
used to "simulate" updatable views. Read documentation ! You can start with
"CREATE RULE" sql command. It is too much to explain it in one mail.

Martijn van Oosterhout <kleptog@svana.org> writes:

On Tue, May 20, 2003 at 01:01:01PM +0300, Kaarel wrote:

I'm relatively new to PostgreSQL. I was wondering why were
INSERT/UPDATE/DELETE view diabled in PostgreSQL?

You can define rules to make insert, update and delete work on views. I'm
surprised it was ever enabled by default since in the general case it is
impossible to workout what the rules should be.

It never was; Kaarel misunderstands the history.

A view used to be an actual table that happened to have an ON SELECT DO
INSTEAD rule attached to it. That rule did not prevent you from doing
an INSERT ... but the inserted row went into the physical table. Where
you could not see it, because the ON SELECT rule would redirect any
attempt to select it. Likewise you could update and delete such rows,
but you were flying blind all the time.

This was obviously useless, so we got rid of the physical table
associated with views.

What should happen, but no one has got round to yet, is to automatically
generate appropriate ON INSERT, UPDATE, DELETE rules for cases where the
view structure is simple enough that we can work out exactly what those
rules ought to be. I believe that such cases correspond pretty closely
to the rules laid down in the SQL spec for whether a view is updatable.

regards, tom lane

Darko Prenosil wrote:

On Tuesday 20 May 2003 12:01, Kaarel wrote:

Hi

I'm relatively new to PostgreSQL. I was wondering why were
INSERT/UPDATE/DELETE view diabled in PostgreSQL? Other database systems
have these features so why shouldn't PostgreSQL. I did some searching
and found that prior version 7.1 or 7.0 these features were in fact
enabled. I couldn't however find a satisfying reason for the removal of
these features from PostqreSQL mailing list archives.

Postgres had, and still have update, delete and insert rules, which can be
used to "simulate" updatable views. Read documentation ! You can start with
"CREATE RULE" sql command. It is too much to explain it in one mail.

That "documentation" would be

http://www.postgresql.org/docs/view.php?version=7.3&amp;idoc=0&amp;file=rules.html

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Tom Lane wrote:

Martijn van Oosterhout <kleptog@svana.org> writes:

On Tue, May 20, 2003 at 01:01:01PM +0300, Kaarel wrote:

I'm relatively new to PostgreSQL. I was wondering why were
INSERT/UPDATE/DELETE view diabled in PostgreSQL?

You can define rules to make insert, update and delete work on views. I'm
surprised it was ever enabled by default since in the general case it is
impossible to workout what the rules should be.

It never was; Kaarel misunderstands the history.

A view used to be an actual table that happened to have an ON SELECT DO
INSTEAD rule attached to it. That rule did not prevent you from doing
an INSERT ... but the inserted row went into the physical table. Where
you could not see it, because the ON SELECT rule would redirect any
attempt to select it. Likewise you could update and delete such rows,
but you were flying blind all the time.

This is not entirely accurate ... you forgot that the RIR rules applied
did prevent UPDATE and DELETE to actually find back the rows :-)

This was obviously useless, so we got rid of the physical table
associated with views.

What should happen, but no one has got round to yet, is to automatically
generate appropriate ON INSERT, UPDATE, DELETE rules for cases where the
view structure is simple enough that we can work out exactly what those
rules ought to be. I believe that such cases correspond pretty closely
to the rules laid down in the SQL spec for whether a view is updatable.

Which was what - one table select of attributes only, no computation, no
qualification, no aggregates, no groups, no leather, no chrome? Maybe
some day I get bored enough to do that, but it doesn't look like that's
gonna happen within the next two or three years.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #