Thread: Updates on Views?
Hi, are views in PG read-only or is this possible? Konstantin -- Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185 ---------------------------------------------------------------------------- "Captain, this ship will not sustain the forming of the cosmos." B'Elana Torres
On 21 Mar 2001, Konstantinos Agouros wrote: > Hi, > > are views in PG read-only or is this possible? You can make updatable views by making the appropriate rules on the view for handling insert/update/delete (whatever you want).
Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote: >You can make updatable views by making the appropriate rules on the view >for handling insert/update/delete (whatever you want). An example can be found in Bruce Momjian's book in the section on Rules (http://www.postgresql.org/docs/aw_pgsql_book/node150.html). Ray -- [Open Source] is the finest expression of the free market. Ideas are encouraged to proliferate and the best thinking wins. By contrast, most corporations today operate in a central planning straitjacket. http://www.thestandard.com/article/display/0,1151,15772,00.html
In <Pine.BSF.4.21.0103212025030.38659-100000@megazone23.bigpanda.com> sszabo@megazone23.bigpanda.com (Stephan Szabo) writes: >On 21 Mar 2001, Konstantinos Agouros wrote: >> Hi, >> >> are views in PG read-only or is this possible? >You can make updatable views by making the appropriate rules >on the view for handling insert/update/delete (whatever >you want). Could You give me a hint on where to read up on this? Konstantin >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster -- Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185 ---------------------------------------------------------------------------- "Captain, this ship will not sustain the forming of the cosmos." B'Elana Torres
On Thu, Mar 22, 2001 at 09:48:43PM +0100, Konstantinos Agouros wrote:
> In <Pine.BSF.4.21.0103212025030.38659-100000@megazone23.bigpanda.com> sszabo@megazone23.bigpanda.com (Stephan Szabo)
writes:
> >On 21 Mar 2001, Konstantinos Agouros wrote:
> >> are views in PG read-only or is this possible?
views are read-only -- but interestingly, a view is actually an
empty table with interference-running rules attached. you can
overlay your own 'do instead' rules to effect various
behind-the-scenes inserts and updates...
> >You can make updatable views by making the appropriate rules
> >on the view for handling insert/update/delete (whatever
> >you want).
> Could You give me a hint on where to read up on this?
on my debian system it's mentioned in the postgresql-doc package
at...
% grep -rl INSTEAD /usr/share/doc/postgresql-doc/html
/usr/share/doc/postgresql-doc/html/programmer/rules1139.htm
/usr/share/doc/postgresql-doc/html/programmer/rules978.htm
/usr/share/doc/postgresql-doc/html/user/sql-createrule.htm
/usr/share/doc/postgresql-doc/html/user/syntax.htm
i'd look in USER documentation under CREATE RULE to get started.
(also at postgresql.org user-lounge area)
--
okay, here's a quickie example:
CREATE VIEW who AS
SELECT * from _who;
CREATE RULE
who_insert
AS ON
INSERT TO who
DO INSTEAD
INSERT INTO "_who" (
login,
"password",
hint,
name,
email,
editor,
status,
modified,
created,
id
) VALUES (
NEW.login,
NEW."password",
NEW.hint,
NEW.name,
NEW.email,
NEW.editor,
'U'::bpchar, -- uncertain until confirmed
"timestamp"('now'::text), -- last mod
"timestamp"('now'::text), -- created now
nextval('_who_id_seq'::text)
);
-- all non-mentioned fields from _who are silently
-- ignored (and dropped).
--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
-- Isaac Asimov, 'The Genetic Code'
will@serensoft.com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
On Thu, Mar 22, 2001 at 07:14:49PM -0600, will trillich wrote:
> CREATE VIEW who AS
> SELECT * from _who;
>
> CREATE RULE
> who_insert
> AS ON
> INSERT TO who
> DO INSTEAD
> INSERT INTO "_who" (
> login,
> "password",
> hint,
> name,
> email,
> editor,
> status,
> modified,
> created,
> id
> ) VALUES (
> NEW.login,
> NEW."password",
> NEW.hint,
> NEW.name,
> NEW.email,
> NEW.editor,
> 'U'::bpchar, -- uncertain until confirmed
> "timestamp"('now'::text), -- last mod
> "timestamp"('now'::text), -- created now
> nextval('_who_id_seq'::text)
> );
> -- all non-mentioned fields from _who are silently
> -- ignored (and dropped).
now that i think about it...
is it possible to have a rule DO INSTEAD more-than-one-thing?
create rule split_it as
on insert to someview
do instead
insert into tableone ....
then
intert into tabletwo ....
then
insert into tablethree ...
then
update someothertable ...
--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
-- Isaac Asimov, 'The Genetic Code'
will@serensoft.com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
will trillich <will@serensoft.com> writes:
> is it possible to have a rule DO INSTEAD more-than-one-thing?
Sure. Observe the CREATE RULE reference page:
CREATE RULE name AS ON event
TO object [ WHERE condition ]
DO [ INSTEAD ] action
where action can be:
NOTHING
|
query
|
( query ; query ... )
|
[ query ; query ... ]
regards, tom lane