Обсуждение: INSERT/UPDATE/DELETE Views
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.
> 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/ > "the West won the world not by the superiority of its ideas or values or > religion but rather by its superiority in applying organized violence. > Westerners often forget this fact, non-Westerners never do." > - Samuel P. Huntington
Вложения
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. > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
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&idoc=0&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 #