pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRule not invoked in 7.1
10+ messages / 3 participants
[nested] [flat]
* Rule not invoked in 7.1
@ 2001-01-24 22:14 Kyle <kyle@actarg.com>
0 siblings, 1 reply; 10+ messages in thread
From: Kyle @ 2001-01-24 22:14 UTC (permalink / raw)
To: pgsql-sql
I have a number of views that seemed to work fine in 7.0.3. When I try
to do an update to these views under 7.1, I get the following error:
ERROR: Cannot update a view without an appropriate rule.
For example, there's a view that looks like this:
create view pay_req_v_prl as select
empl_id,wdate,seq,hours,hot,proj,entby,paytyp,status,poinum,added,rate,otrate,appby,gross,rgross,cost,ttype,expnum,oid
as _oid from pay_req
create rule pay_req_v_prl_update as on update to pay_req_v_prl
where old.status = 'appr' do instead
update pay_req set status = new.status, gross = new.gross, cost =
new.cost,
ttype = new.ttype, expnum = new.expnum, rgross = new.rgross, hot =
new.hot
where empl_id = old.empl_id and wdate = old.wdate and seq = old.seq;
The sql looks like this:
update pay_req_v_prl set gross = 90.09 where empl_id = 1010 and wdate =
'2001-01-08' and seq = 1;
The record it should update does seem to have status = 'appr' and it
updates fine on my 7.0.3 box.
Any ideas?
Attachments:
kyle.vcf (text/x-vcard, 184B) download
begin:vcard
n:Bateman;Kyle
x-mozilla-html:FALSE
org:Action Target Inc.
adr:;;;;;;
version:2.1
email;internet:kyle@actarg.com
title:President
x-mozilla-cpt:;0
fn:Kyle Bateman
end:vcard
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Rule not invoked in 7.1
@ 2001-01-25 04:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kyle <kyle@actarg.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2001-01-25 04:53 UTC (permalink / raw)
To: Kyle <kyle@actarg.com>; +Cc: pgsql-sql
Kyle <kyle@actarg.com> writes:
> ERROR: Cannot update a view without an appropriate rule.
7.1 insists that you provide an *unconditional* DO INSTEAD rule
for a view. What do you think was happening on your old database
when the "where old.status = 'appr'" clause wasn't satisfied?
Nothing good I'm afraid.
If you really do need conditional rules, you can satisfy the check
by writing one unconditional DO INSTEAD NOTHING rule and then one
or more conditional non-INSTEAD rules. But you should think carefully
about what you expect to happen when you use a conditional rule.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Rule not invoked in 7.1
@ 2001-01-25 13:21 Jan Wieck <janwieck@Yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Jan Wieck @ 2001-01-25 13:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Kyle <kyle@actarg.com>; pgsql-sql
Tom Lane wrote:
> Kyle <kyle@actarg.com> writes:
> > ERROR: Cannot update a view without an appropriate rule.
>
> 7.1 insists that you provide an *unconditional* DO INSTEAD rule
> for a view. What do you think was happening on your old database
> when the "where old.status = 'appr'" clause wasn't satisfied?
> Nothing good I'm afraid.
No harm in the UPDATE case, because so far there aren't any
tuples in the view that could be affected by the still
executed original query. But in an INSERT case, it would let
tuples through into the views heap file.
> If you really do need conditional rules, you can satisfy the check
> by writing one unconditional DO INSTEAD NOTHING rule and then one
> or more conditional non-INSTEAD rules. But you should think carefully
> about what you expect to happen when you use a conditional rule.
Alternatively he should be able to move the condition down
into the query itself. In that case, it's an unconditional
INSTEAD rule, causing the rewriter not to fork off another
query but replace the initial one completely. But the
condition is still there and affects the effects.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Rule not invoked in 7.1
@ 2001-01-25 17:27 Kyle <kyle@actarg.com>
parent: Jan Wieck <janwieck@Yahoo.com>
0 siblings, 1 reply; 10+ messages in thread
From: Kyle @ 2001-01-25 17:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jan Wieck <janwieck@yahoo.com>; pgsql-sql
Jan Wieck wrote:
> Tom Lane wrote:
> > Kyle <kyle@actarg.com> writes:
> > > ERROR: Cannot update a view without an appropriate rule.
> >
> > 7.1 insists that you provide an *unconditional* DO INSTEAD rule
> > for a view. What do you think was happening on your old database
> > when the "where old.status = 'appr'" clause wasn't satisfied?
> > Nothing good I'm afraid.
>
> No harm in the UPDATE case, because so far there aren't any
> tuples in the view that could be affected by the still
> executed original query. But in an INSERT case, it would let
> tuples through into the views heap file.
>
> > If you really do need conditional rules, you can satisfy the check
> > by writing one unconditional DO INSTEAD NOTHING rule and then one
> > or more conditional non-INSTEAD rules. But you should think carefully
> > about what you expect to happen when you use a conditional rule.
>
I'm using the view as a way of restricting a single class of users to only
update tuples that have a certain status in the table. Isn't this
essentially what a "dynamic view" is?
If someone happens to know the primary key of a record they should not be
able to access, and they try to update it, I would like the backend to
ignore the query (or better yet, raise an exception but I haven't figured
out how to do that). If the status is correct, the update should proceed.
I've inserted the dummy do nothing rule as follows:
create view pay_req_v_prl as select
empl_id,wdate,seq,hours,hot,proj,entby,paytyp,status,poinum,added,rate,otrate,appby,gross,rgross,cost,ttype,expnum,oid
as _oid from pay_req;
create rule pay_req_v_prl_upnull as on update to pay_req_v_prl do instead
nothing;
create rule pay_req_v_prl_update as on update to pay_req_v_prl
where old.status = 'appr' do instead
update pay_req set status = new.status, gross = new.gross, cost =
new.cost,
ttype = new.ttype, expnum = new.expnum, rgross = new.rgross, hot =
new.hot
where empl_id = old.empl_id and wdate = old.wdate and seq = old.seq;
This seems to work now when I do:
psql ati -c "update pay_req_v_prl set gross = 90.09 where empl_id = 1010
and wdate = '2001-01-08' and seq = 1;"
You see any problems with this method?
BTW, the update still returns UPDATE 0 from psql even though a record was
updated. I've never quite figured out why views with rules do this.
I've also done some testing on 7.1 for that nasty thing in 7.0 where you
had to give select,update privs to a table referenced by a foreign key. So
far, looks good. I was able to reference a table that the user didn't have
privs to at all. I think that is the desired behavior.
Good work guys! 7.1 is looking good.
Attachments:
kyle.vcf (text/x-vcard, 184B) download
begin:vcard
n:Bateman;Kyle
x-mozilla-html:FALSE
org:Action Target Inc.
adr:;;;;;;
version:2.1
email;internet:kyle@actarg.com
title:President
x-mozilla-cpt:;0
fn:Kyle Bateman
end:vcard
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Rule not invoked in 7.1
@ 2001-01-26 03:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kyle <kyle@actarg.com>
0 siblings, 3 replies; 10+ messages in thread
From: Tom Lane @ 2001-01-26 03:35 UTC (permalink / raw)
To: Kyle <kyle@actarg.com>; +Cc: Jan Wieck <janwieck@yahoo.com>; pgsql-sql
Kyle <kyle@actarg.com> writes:
> If someone happens to know the primary key of a record they should not be
> able to access, and they try to update it, I would like the backend to
> ignore the query (or better yet, raise an exception but I haven't figured
> out how to do that). If the status is correct, the update should proceed.
This might be better done with a trigger than a rule. For one thing,
a trigger can easily raise an exception. MHO is that rules are good
when you need to update multiple rows in other tables when certain
things happen. If you just want to validate or twiddle an individual
tuple as it's inserted/updated, a trigger is a good bet.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Rule not invoked in 7.1
@ 2001-01-26 16:52 Jan Wieck <janwieck@Yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 10+ messages in thread
From: Jan Wieck @ 2001-01-26 16:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Kyle <kyle@actarg.com>; Jan Wieck <janwieck@yahoo.com>; pgsql-sql
Tom Lane wrote:
> Kyle <kyle@actarg.com> writes:
> > If someone happens to know the primary key of a record they should not be
> > able to access, and they try to update it, I would like the backend to
> > ignore the query (or better yet, raise an exception but I haven't figured
> > out how to do that). If the status is correct, the update should proceed.
>
> This might be better done with a trigger than a rule. For one thing,
> a trigger can easily raise an exception. MHO is that rules are good
> when you need to update multiple rows in other tables when certain
> things happen. If you just want to validate or twiddle an individual
> tuple as it's inserted/updated, a trigger is a good bet.
But the trigger aproach requires access permissions to the
base table in the first place, and exactly that's what Kyle
want to restrict.
Kyle, I doubt if you need the condition in the update rule at
all. As far as I understood, your view restricts what the
user can see from the base table. This restricted SELECT rule
is applied to UPDATE events as well, so the UPDATE can never
affect rows which are invisible through the view.
create table t1 (
id integer,
visible bool,
data text
);
CREATE
create view v1 as select id, data from t1 where visible;
CREATE
create rule upd_v1 as on update to v1 do instead
update t1 set id = new.id, data = new.data where id = old.id;
CREATE
insert into t1 values (1, 't', 'Visible row');
INSERT 18809 1
insert into t1 values (2, 'f', 'Invisible row');
INSERT 18810 1
select * from v1;
id | data
----+-------------
1 | Visible row
(1 row)
update v1 set data = 'Updated row';
UPDATE 1
select * from t1;
id | visible | data
----+---------+---------------
2 | f | Invisible row
1 | t | Updated row
(2 rows)
update v1 set data = 'Updated row' where id = 2;
UPDATE 0
select * from t1;
id | visible | data
----+---------+---------------
2 | f | Invisible row
1 | t | Updated row
(2 rows)
As you see, neither an unqualified update of all rows, nor if
the user guesses a valid id, can touch the invisible row
filtered out by the view.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Rule not invoked in 7.1
@ 2001-01-26 17:05 Kyle <kyle@actarg.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 10+ messages in thread
From: Kyle @ 2001-01-26 17:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; janwieck@yahoo.com
Tom Lane wrote:
> Kyle <kyle@actarg.com> writes:
> > If someone happens to know the primary key of a record they should not be
> > able to access, and they try to update it, I would like the backend to
> > ignore the query (or better yet, raise an exception but I haven't figured
> > out how to do that). If the status is correct, the update should proceed.
>
> This might be better done with a trigger than a rule. For one thing,
> a trigger can easily raise an exception. MHO is that rules are good
> when you need to update multiple rows in other tables when certain
> things happen. If you just want to validate or twiddle an individual
> tuple as it's inserted/updated, a trigger is a good bet.
>
The underlying table contains payroll data. Each employee should be able to
enter payroll
requests, but there is a very strict set of rules about which rows he should be
able to access,
how he can access them, and when.
For example, an employee can enter new time records, but once the records have
been
approved or paid, he can no longer modify them.
I have set up several views that allow access to the rows depending on their
status. For example,
employees only have access to "working records." Once they are satisfied with
the data they
have entered, they change the status to "open" at which point they can no
longer edit it.
Supervisors then have access to the record and can approve it, changing its
status to "approved"
and so on.
The problem I had with trying to use a trigger was that the trigger fires on
the underlying table,
regardless of which view the user comes in on. (At least it seemed that way
when I tested it.)
I need to apply a different set of rules based on which view the user is coming
in on--not simply
who the user is.
Is there a way to configure a trigger to fire on a view rather than the
underlying table? I tried linking
a trigger to a view but it seemed to not get called at all. I assumed this was
because the rewriting
rules were directing the query away from the view class and so the trigger was
never getting called
at all.
Attachments:
kyle.vcf (text/x-vcard, 184B) download
begin:vcard
n:Bateman;Kyle
x-mozilla-html:FALSE
org:Action Target Inc.
adr:;;;;;;
version:2.1
email;internet:kyle@actarg.com
title:President
x-mozilla-cpt:;0
fn:Kyle Bateman
end:vcard
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Rule not invoked in 7.1
@ 2001-01-26 17:13 Kyle <kyle@actarg.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 10+ messages in thread
From: Kyle @ 2001-01-26 17:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jan Wieck <janwieck@yahoo.com>; pgsql-sql
Tom Lane wrote:
> Kyle <kyle@actarg.com> writes:
> > If someone happens to know the primary key of a record they should not be
> > able to access, and they try to update it, I would like the backend to
> > ignore the query (or better yet, raise an exception but I haven't figured
> > out how to do that). If the status is correct, the update should proceed.
>
> This might be better done with a trigger than a rule. For one thing,
> a trigger can easily raise an exception. MHO is that rules are good
> when you need to update multiple rows in other tables when certain
> things happen. If you just want to validate or twiddle an individual
> tuple as it's inserted/updated, a trigger is a good bet.
>
I have another interesting use of this same concept you may be interested in
(or slightly nausious, as the case may be):
The underlying database (empl) contains all employees in the organization. The
view empl_v_sup calls a recursive function to determine if
a given employee works for the current user (either directly, or anywhere under
him in the company heirarchy). The view only includes
employees that work under the that user. There is also an exception for users
who have certain types of privileges who get to see the whole
company.
This dynamic view is very cool as it allows different people to see different
data in the same view depending on who they are, and how the
hierarchical data is arranged in the employee database.
-- Determine if an employee has another employee as his supervisor.
-- An employee is, by definition, not his own supervisor
-- Returns true or false
-- calling sequence: _empl_ancest(employee,ancestor,level)
create function _empl_ancest(int4,int4,int4) returns boolean as '
declare
trec record;
begin
if $3 > 15 then
raise exception \'Supervisor loop found on employee %\', $1;
end if;
-- a person can not be his own supervisor
-- also if null or 0, we reached top of the ladder so return false
if $1 = $2 or $1 is null or $1 = 0 then return false; end if;
-- get the employees record
select * into trec from empl_pub where empl_id = $1;
if not found then
raise exception \'Record not found for employee %\', $1;
end if;
-- if he is his own supervisor, we have probably reached the top so false
if trec.superv = $1 then return false; end if;
-- if his supervisor is the ancestor, return true
if trec.superv = $2 then return true; end if;
-- else check the parent recursively
return _empl_ancest(trec.superv, $2, $3+1);
end;' language 'plpgsql';
-- Determine if an employee has another employee as his ancestor.
-- This adds a level parm to prevent infinite recursion.
-- calling sequence: empl_ancest(employee,ancestor)
create function empl_ancest(int4,int4) returns boolean as '
select _empl_ancest($1,$2,0);
' language 'sql';
--View with limited privileges for supervisors to see their own people
create view empl_v_sup as select *,oid as _oid from empl where
exists (select * from priv where empl_id = getpguid() and ((priv = 'emplim'
and alevel = 'super') or (priv = 'payroll'))) or
empl_ancest(empl_id,getpguid());
--Only the emplim-super can insert records
create rule empl_v_sup_innull as on insert to empl_v_sup do instead nothing;
create rule empl_v_sup_insert as on insert to empl_v_sup where
(select count(*) from priv where priv = 'emplim' and alevel = 'super' and
empl_id = getpguid()) > 0
do instead
insert into empl
(empl_id,pertitle,surname,givnames,prefname,jobtitle,addr,city,state,zip,country,phone,workph,mobile,email,ssn,bday,hiredate,termdate,lrevdate,nrevdate,paytyp,empltyp,superv,proxy,status,mstat,payrate,allow,wccode,eic,cmt)
values
(new.empl_id,new.pertitle,new.surname,new.givnames,new.prefname,new.jobtitle,new.addr,new.city,new.state,new.zip,new.country,new.phone,new.workph,new.mobile,new.email,new.ssn,new.bday,new.hiredate,new.termdate,new.lrevdate,new.nrevdate,new.paytyp,new.empltyp,new.superv,new.proxy,new.status,new.mstat,new.payrate,new.allow,new.wccode,new.eic,new.cmt);
--Emplim-super can update any field
create rule empl_v_sup_upnull as on update to empl_v_sup do instead nothing;
create rule empl_v_sup_update as on update to empl_v_sup where
(select count(*) from priv where priv = 'emplim' and alevel = 'super' and
empl_id = getpguid()) > 0
do instead
update empl set empl_id = new.empl_id, pertitle = new.pertitle, surname =
new.surname, givnames = new.givnames, prefname = new.prefname, jobtitle =
new.jobtitle, addr = new.addr, city = new.city, state = new.state, zip =
new.zip, country = new.country, phone = new.phone, workph = new.workph, mobile
= new.mobile, email = new.email, ssn = new.ssn, bday = new.bday, hiredate =
new.hiredate, termdate = new.termdate, lrevdate = new.lrevdate, nrevdate =
new.nrevdate, paytyp = new.paytyp, empltyp
where empl_id = old.empl_id;
--Emplim-user can update certain fields
create rule empl_v_sup_update1 as on update to empl_v_sup where
(select count(*) from priv where priv = 'emplim' and alevel = 'user' and
empl_id = getpguid()) > 0 and
empl_ancest(old.empl_id,getpguid())
do instead
update empl set pertitle = new.pertitle, prefname = new.prefname, jobtitle
= new.jobtitle, phone = new.phone, workph = new.workph, mobile = new.mobile,
email = new.email, lrevdate = new.lrevdate, nrevdate = new.nrevdate, payrate =
new.payrate
where empl_id = old.empl_id;
Attachments:
kyle.vcf (text/x-vcard, 184B) download
begin:vcard
n:Bateman;Kyle
x-mozilla-html:FALSE
org:Action Target Inc.
adr:;;;;;;
version:2.1
email;internet:kyle@actarg.com
title:President
x-mozilla-cpt:;0
fn:Kyle Bateman
end:vcard
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Rule not invoked in 7.1
@ 2001-01-26 17:20 Kyle <kyle@actarg.com>
parent: Jan Wieck <janwieck@Yahoo.com>
0 siblings, 0 replies; 10+ messages in thread
From: Kyle @ 2001-01-26 17:20 UTC (permalink / raw)
To: Jan Wieck <janwieck@yahoo.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
Jan Wieck wrote:
> Tom Lane wrote:
>
> > This might be better done with a trigger than a rule. For one thing,
> > a trigger can easily raise an exception. MHO is that rules are good
> > when you need to update multiple rows in other tables when certain
> > things happen. If you just want to validate or twiddle an individual
> > tuple as it's inserted/updated, a trigger is a good bet.
>
> But the trigger aproach requires access permissions to the
> base table in the first place, and exactly that's what Kyle
> want to restrict.
That's right.
> Kyle, I doubt if you need the condition in the update rule at
> all. As far as I understood, your view restricts what the
> user can see from the base table. This restricted SELECT rule
> is applied to UPDATE events as well, so the UPDATE can never
> affect rows which are invisible through the view.
This hadn't occurred to me but makes sense now that you say it. Taking that into
consideration will
make my job a bit simpler.
The only complication is
that there are a class of records which the user should be able to view, but not
modify. For example,
the employee can create and modify working records as long as the only
modification to their status
is to move them on to "open status" (creating an "approved" record would be a bad
idea.)
But the user should be able to view all their records (working, open, approved,
and even paid).
Hence, the restrictions on update are more stringent than those on select.
Attachments:
kyle.vcf (text/x-vcard, 184B) download
begin:vcard
n:Bateman;Kyle
x-mozilla-html:FALSE
org:Action Target Inc.
adr:;;;;;;
version:2.1
email;internet:kyle@actarg.com
title:President
x-mozilla-cpt:;0
fn:Kyle Bateman
end:vcard
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Rule not invoked in 7.1
@ 2001-01-26 19:15 Jan Wieck <janwieck@Yahoo.com>
0 siblings, 0 replies; 10+ messages in thread
From: Jan Wieck @ 2001-01-26 19:15 UTC (permalink / raw)
To: pgsql-sql
Kyle wrote:
> The only complication is
> that there are a class of records which the user should be able to view, but not
> modify. For example,
> the employee can create and modify working records as long as the only
> modification to their status
> is to move them on to "open status" (creating an "approved" record would be a bad
> idea.)
>
> But the user should be able to view all their records (working, open, approved,
> and even paid).
>
> Hence, the restrictions on update are more stringent than those on select.
Ah.
Describe the entire problem and you'll get a complete answer:
CREATE TABLE wr_table (
w_id serial PRIMARY KEY,
w_user name,
w_state text,
w_data text
);
CREATE
CREATE VIEW wr_view AS
SELECT * FROM wr_table WHERE w_user = CURRENT_USER;
CREATE
CREATE RULE wr_view_ins AS ON INSERT TO wr_view DO INSTEAD
INSERT INTO wr_table (w_user, w_state, w_data)
VALUES (
CURRENT_USER,
'OPEN',
new.w_data
);
CREATE
CREATE RULE wr_view_upd AS ON UPDATE TO wr_view DO INSTEAD
UPDATE wr_table SET w_data = new.w_data
WHERE w_id = old.w_id AND w_state = 'OPEN';
CREATE
CREATE RULE wr_view_del AS ON DELETE TO wr_view DO INSTEAD
DELETE FROM wr_table
WHERE w_id = old.w_id AND w_state = 'OPEN';
CREATE
INSERT INTO wr_table (w_user, w_state, w_data)
VALUES ('pgsql', 'OPEN', 'Open item 1 of pgsql');
INSERT 19392 1
INSERT INTO wr_table (w_user, w_state, w_data)
VALUES ('pgsql', 'OPEN', 'Open item 2 of pgsql');
INSERT 19393 1
INSERT INTO wr_table (w_user, w_state, w_data)
VALUES ('pgsql', 'CLOSED', 'Closed item 3 of pgsql');
INSERT 19394 1
INSERT INTO wr_table (w_user, w_state, w_data)
VALUES ('someone', 'OPEN', 'Open item of someone else');
INSERT 19395 1
INSERT INTO wr_table (w_user, w_state, w_data)
VALUES ('someone', 'CLOSED', 'Closed item of someone else');
INSERT 19396 1
SELECT CURRENT_USER;
current_user
--------------
pgsql
(1 row)
SELECT * FROM wr_table ORDER BY w_id;
w_id | w_user | w_state | w_data
------+---------+---------+-----------------------------
1 | pgsql | OPEN | Open item 1 of pgsql
2 | pgsql | OPEN | Open item 2 of pgsql
3 | pgsql | CLOSED | Closed item 3 of pgsql
4 | someone | OPEN | Open item of someone else
5 | someone | CLOSED | Closed item of someone else
(5 rows)
UPDATE wr_view SET w_data = 'Changed item 2 of pgsql'
WHERE w_id = 2;
UPDATE 1
SELECT * FROM wr_table ORDER BY w_id;
w_id | w_user | w_state | w_data
------+---------+---------+-----------------------------
1 | pgsql | OPEN | Open item 1 of pgsql
2 | pgsql | OPEN | Changed item 2 of pgsql
3 | pgsql | CLOSED | Closed item 3 of pgsql
4 | someone | OPEN | Open item of someone else
5 | someone | CLOSED | Closed item of someone else
(5 rows)
UPDATE wr_view SET w_data = 'Changed item of someone else'
WHERE w_id = 4;
UPDATE 0
SELECT * FROM wr_table ORDER BY w_id;
w_id | w_user | w_state | w_data
------+---------+---------+-----------------------------
1 | pgsql | OPEN | Open item 1 of pgsql
2 | pgsql | OPEN | Changed item 2 of pgsql
3 | pgsql | CLOSED | Closed item 3 of pgsql
4 | someone | OPEN | Open item of someone else
5 | someone | CLOSED | Closed item of someone else
(5 rows)
UPDATE wr_view SET w_data = 'Changed item 3 of pgsql'
WHERE w_id = 3;
UPDATE 0
SELECT * FROM wr_table ORDER BY w_id;
w_id | w_user | w_state | w_data
------+---------+---------+-----------------------------
1 | pgsql | OPEN | Open item 1 of pgsql
2 | pgsql | OPEN | Changed item 2 of pgsql
3 | pgsql | CLOSED | Closed item 3 of pgsql
4 | someone | OPEN | Open item of someone else
5 | someone | CLOSED | Closed item of someone else
(5 rows)
DELETE FROM wr_view;
DELETE 2
SELECT * FROM wr_table ORDER BY w_id;
w_id | w_user | w_state | w_data
------+---------+---------+-----------------------------
3 | pgsql | CLOSED | Closed item 3 of pgsql
4 | someone | OPEN | Open item of someone else
5 | someone | CLOSED | Closed item of someone else
(3 rows)
INSERT INTO wr_view VALUES (99, 'someone', 'CLOSED', 'Meant for someone');
INSERT 19397 1
SELECT * FROM wr_table ORDER BY w_id;
w_id | w_user | w_state | w_data
------+---------+---------+-----------------------------
3 | pgsql | CLOSED | Closed item 3 of pgsql
4 | someone | OPEN | Open item of someone else
5 | someone | CLOSED | Closed item of someone else
6 | pgsql | OPEN | Meant for someone
(4 rows)
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2001-01-26 19:15 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-24 22:14 Rule not invoked in 7.1 Kyle <kyle@actarg.com>
2001-01-25 04:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-01-25 13:21 ` Jan Wieck <janwieck@Yahoo.com>
2001-01-25 17:27 ` Kyle <kyle@actarg.com>
2001-01-26 03:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-01-26 16:52 ` Jan Wieck <janwieck@Yahoo.com>
2001-01-26 17:20 ` Kyle <kyle@actarg.com>
2001-01-26 17:05 ` Kyle <kyle@actarg.com>
2001-01-26 17:13 ` Kyle <kyle@actarg.com>
2001-01-26 19:15 Re: Rule not invoked in 7.1 Jan Wieck <janwieck@Yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox