pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRules (was: Re: [SQL] How can I optimize...)
4+ messages / 2 participants
[nested] [flat]
* Rules (was: Re: [SQL] How can I optimize...)
@ 1998-11-04 11:35 Jan Wieck <jwieck@debis.com>
1998-11-05 15:36 ` Re: Rules (was: Re: [SQL] How can I optimize...) G.Elangovan <elangog@wipinfo.soft.net>
1998-11-05 16:54 ` Re: Rules (was: Re: [SQL] How can I optimize...) G.Elangovan <elangog@wipinfo.soft.net>
0 siblings, 2 replies; 4+ messages in thread
From: jwieck@debis.com @ 1998-11-04 11:35 UTC (permalink / raw)
To: elangog@wipinfo.soft.net; +Cc: mwm@phone.net; pgsql-sql
G.Elangovan wrote:
>
> Can any one help me to CREATE RULE
> please give me an example with
> a simple table(s) definition and to create rule on that table
It's on my personal TODO to create a rule system
documentation for users too. The one in the programmers
manual for v6.4 is a bit technical, but you might want to
read it anyway.
Anything below addresses the v6.4 rule system!
Sample 1 - constraint follow/delete
CREATE TABLE prim (
pkey text,
comment text);
CREATE TABLE ref (
rkey text,
skey integer,
comment text);
--
-- Rule to let ref.rkey follow prim.pkey
--
CREATE RULE prim_follow AS ON UPDATE TO prim
WHERE NEW.pkey != OLD.pkey DO
UPDATE ref SET rkey = NEW.pkey WHERE rkey = OLD.pkey;
--
-- Rule to constraint delete references from ref to pkey
--
CREATE RULE prim_delete AS ON DELETE TO prim DO
DELETE FROM ref WHERE rkey = OLD.pkey;
--
-- Some tests
--
INSERT INTO prim VALUES ('k1', 'to get updated');
INSERT INTO prim VALUES ('k2', 'to get deleted');
INSERT INTO ref VALUES ('k1', 1, 'must follow');
INSERT INTO ref VALUES ('k1', 2, 'must follow');
INSERT INTO ref VALUES ('k2', 1, 'must get deleted');
INSERT INTO ref VALUES ('k2', 2, 'must get deleted');
UPDATE prim SET pkey = 'new1' WHERE pkey = 'k1';
SELECT * FROM ref;
rkey|skey|comment
----+----+----------------
k2 | 1|must get deleted
k2 | 2|must get deleted
new1| 1|must follow
new1| 2|must follow
(4 rows)
DELETE FROM prim WHERE pkey = 'k2';
SELECT * FROM ref;
rkey|skey|comment
----+----+-----------
new1| 1|must follow
new1| 2|must follow
(2 rows)
Sample 2 - logging of table changes
CREATE TABLE emp (
ename text,
dept text,
salary money);
CREATE TABLE emp_log (
ename text,
action text,
newsal money,
uname name,
when datetime);
--
-- Rule to log new created employees
--
CREATE RULE emp_ins AS ON INSERT TO emp DO
INSERT INTO emp_log VALUES (
NEW.ename,
'employed',
NEW.salary,
getpgusername(),
'now'::text);
--
-- Rule to log when salary raises
--
CREATE RULE emp_raise AS ON UPDATE TO emp
WHERE NEW.salary > OLD.salary DO
INSERT INTO emp_log VALUES (
NEW.ename,
'raised',
NEW.salary,
getpgusername(),
'now'::text);
--
-- Rule to log when salary is lowered
--
CREATE RULE emp_lower AS ON UPDATE TO emp
WHERE NEW.salary < OLD.salary DO
INSERT INTO emp_log VALUES (
NEW.ename,
'lowered',
NEW.salary,
getpgusername(),
'now'::text);
--
-- Rule to log when employee is fired
--
CREATE RULE emp_fire AS ON DELETE TO emp DO
INSERT INTO emp_log VALUES (
OLD.ename,
'fired',
'$0',
getpgusername(),
'now'::text);
--
-- Some tests
--
INSERT INTO emp VALUES ('wieck', 'development', '$8000.00');
INSERT INTO emp VALUES ('gates', 'management', '$15000.00');
UPDATE emp SET salary = '$10000.00' WHERE ename = 'wieck';
UPDATE emp SET salary = '$12000.00' WHERE ename = 'gates';
DELETE FROM emp WHERE ename = 'gates';
SELECT * FROM emp_log;
ename|action |newsal |uname|when
-----+--------+----------+-----+----------------------------
wieck|employed|$8,000.00 |pgsql|Wed Nov 04 12:19:24 1998 MET
gates|employed|$15,000.00|pgsql|Wed Nov 04 12:19:26 1998 MET
wieck|raised |$10,000.00|pgsql|Wed Nov 04 12:19:27 1998 MET
gates|lowered |$12,000.00|pgsql|Wed Nov 04 12:19:28 1998 MET
gates|fired |$0.00 |pgsql|Wed Nov 04 12:19:30 1998 MET
(5 rows)
Note: The explicit 'now'::text is required. Otherwise 'now'
is interpreted at CREATE RULE time and all log entries will
contain that date instead of the time it happened.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Rules (was: Re: [SQL] How can I optimize...)
1998-11-04 11:35 Rules (was: Re: [SQL] How can I optimize...) Jan Wieck <jwieck@debis.com>
@ 1998-11-05 15:36 ` G.Elangovan <elangog@wipinfo.soft.net>
1998-11-05 12:08 ` Re: Rules (was: Re: [SQL] How can I optimize...) Jan Wieck <jwieck@debis.com>
1 sibling, 1 reply; 4+ messages in thread
From: G.Elangovan @ 1998-11-05 15:36 UTC (permalink / raw)
To: Jan Wieck <jwieck@debis.com>; +Cc: mwm@phone.net; pgsql-sql
I tried Creating RULE with both the example
In first example both the rule failed with error "old table not found"
In case of second example
i managed to create
but other three rule failed
CREATE RULE emp_lower AS ON UPDATE TO emp
WHERE NEW.salary < OLD.salary DO
INSERT INTO emp_log VALUES (
NEW.ename,
'lowered',
NEW.salary,
getpgusername(),
'now'::text);
ERROR: old: Table does not exist.
CREATE RULE emp_fire AS ON DELETE TO emp DO
INSERT INTO emp_log VALUES (
OLD.ename,
'fired',
'$0',
getpgusername(),
'now'::text);
ERROR: old: Table does not exist.
Please giveme some more info on the KEY word (NEW and OLD).attribute
Note : iam using postgresql 6.3.2
Thanku , for ur kind help
On Wed, 4 Nov 1998, Jan Wieck wrote:
> G.Elangovan wrote:
>
> >
> > Can any one help me to CREATE RULE
> > please give me an example with
> > a simple table(s) definition and to create rule on that table
>
> It's on my personal TODO to create a rule system
> documentation for users too. The one in the programmers
> manual for v6.4 is a bit technical, but you might want to
> read it anyway.
>
> Anything below addresses the v6.4 rule system!
>
> Sample 1 - constraint follow/delete
>
> CREATE TABLE prim (
> pkey text,
> comment text);
>
> CREATE TABLE ref (
> rkey text,
> skey integer,
> comment text);
>
> --
> -- Rule to let ref.rkey follow prim.pkey
> --
> CREATE RULE prim_follow AS ON UPDATE TO prim
> WHERE NEW.pkey != OLD.pkey DO
> UPDATE ref SET rkey = NEW.pkey WHERE rkey = OLD.pkey;
>
> --
> -- Rule to constraint delete references from ref to pkey
> --
> CREATE RULE prim_delete AS ON DELETE TO prim DO
> DELETE FROM ref WHERE rkey = OLD.pkey;
>
> --
> -- Some tests
> --
> INSERT INTO prim VALUES ('k1', 'to get updated');
> INSERT INTO prim VALUES ('k2', 'to get deleted');
> INSERT INTO ref VALUES ('k1', 1, 'must follow');
> INSERT INTO ref VALUES ('k1', 2, 'must follow');
> INSERT INTO ref VALUES ('k2', 1, 'must get deleted');
> INSERT INTO ref VALUES ('k2', 2, 'must get deleted');
>
> UPDATE prim SET pkey = 'new1' WHERE pkey = 'k1';
> SELECT * FROM ref;
> rkey|skey|comment
> ----+----+----------------
> k2 | 1|must get deleted
> k2 | 2|must get deleted
> new1| 1|must follow
> new1| 2|must follow
> (4 rows)
>
> DELETE FROM prim WHERE pkey = 'k2';
> SELECT * FROM ref;
> rkey|skey|comment
> ----+----+-----------
> new1| 1|must follow
> new1| 2|must follow
> (2 rows)
>
> Sample 2 - logging of table changes
>
> CREATE TABLE emp (
> ename text,
> dept text,
> salary money);
>
> CREATE TABLE emp_log (
> ename text,
> action text,
> newsal money,
> uname name,
> when datetime);
>
> --
> -- Rule to log new created employees
> --
> CREATE RULE emp_ins AS ON INSERT TO emp DO
> INSERT INTO emp_log VALUES (
> NEW.ename,
> 'employed',
> NEW.salary,
> getpgusername(),
> 'now'::text);
>
> --
> -- Rule to log when salary raises
> --
> CREATE RULE emp_raise AS ON UPDATE TO emp
> WHERE NEW.salary > OLD.salary DO
> INSERT INTO emp_log VALUES (
> NEW.ename,
> 'raised',
> NEW.salary,
> getpgusername(),
> 'now'::text);
>
> --
> -- Rule to log when salary is lowered
> --
> CREATE RULE emp_lower AS ON UPDATE TO emp
> WHERE NEW.salary < OLD.salary DO
> INSERT INTO emp_log VALUES (
> NEW.ename,
> 'lowered',
> NEW.salary,
> getpgusername(),
> 'now'::text);
>
> --
> -- Rule to log when employee is fired
> --
> CREATE RULE emp_fire AS ON DELETE TO emp DO
> INSERT INTO emp_log VALUES (
> OLD.ename,
> 'fired',
> '$0',
> getpgusername(),
> 'now'::text);
>
>
> --
> -- Some tests
> --
> INSERT INTO emp VALUES ('wieck', 'development', '$8000.00');
> INSERT INTO emp VALUES ('gates', 'management', '$15000.00');
>
> UPDATE emp SET salary = '$10000.00' WHERE ename = 'wieck';
> UPDATE emp SET salary = '$12000.00' WHERE ename = 'gates';
>
> DELETE FROM emp WHERE ename = 'gates';
>
> SELECT * FROM emp_log;
> ename|action |newsal |uname|when
> -----+--------+----------+-----+----------------------------
> wieck|employed|$8,000.00 |pgsql|Wed Nov 04 12:19:24 1998 MET
> gates|employed|$15,000.00|pgsql|Wed Nov 04 12:19:26 1998 MET
> wieck|raised |$10,000.00|pgsql|Wed Nov 04 12:19:27 1998 MET
> gates|lowered |$12,000.00|pgsql|Wed Nov 04 12:19:28 1998 MET
> gates|fired |$0.00 |pgsql|Wed Nov 04 12:19:30 1998 MET
> (5 rows)
>
> Note: The explicit 'now'::text is required. Otherwise 'now'
> is interpreted at CREATE RULE time and all log entries will
> contain that date instead of the time it happened.
>
> Jan
>
> --
>
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me. #
> #======================================== jwieck@debis.com (Jan Wieck) #
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Rules (was: Re: [SQL] How can I optimize...)
1998-11-04 11:35 Rules (was: Re: [SQL] How can I optimize...) Jan Wieck <jwieck@debis.com>
1998-11-05 15:36 ` Re: Rules (was: Re: [SQL] How can I optimize...) G.Elangovan <elangog@wipinfo.soft.net>
@ 1998-11-05 12:08 ` Jan Wieck <jwieck@debis.com>
0 siblings, 0 replies; 4+ messages in thread
From: jwieck@debis.com @ 1998-11-05 12:08 UTC (permalink / raw)
To: elangog@wipinfo.soft.net; +Cc: jwieck@debis.com; mwm@phone.net; pgsql-sql
>
> I tried Creating RULE with both the example
> In first example both the rule failed with error "old table not found"
> In case of second example
> i managed to create
>
> [...]
>
> ERROR: old: Table does not exist.
> Please giveme some more info on the KEY word (NEW and OLD).attribute
> Note : iam using postgresql 6.3.2
>
> Thanku , for ur kind help
DON'T TRY TO USE THE RULE SYSTEM IN A v6.3.2 DATABASE. IT'S
BUGGY AND DOES NOT WORK AT ALL.
All I wrote depends on the major changes I've done in the
rule system for v6.4.
In v6.4 CURRENT is aliased with OLD and in v6.5 CURRENT will
disappear completely.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Rules (was: Re: [SQL] How can I optimize...)
1998-11-04 11:35 Rules (was: Re: [SQL] How can I optimize...) Jan Wieck <jwieck@debis.com>
@ 1998-11-05 16:54 ` G.Elangovan <elangog@wipinfo.soft.net>
1 sibling, 0 replies; 4+ messages in thread
From: G.Elangovan @ 1998-11-05 16:54 UTC (permalink / raw)
To: Jan Wieck <jwieck@debis.com>; +Cc: mwm@phone.net; pgsql-sql
My first mail was bit incomplete
Note : OLD is not recognized when i used to create rule on example given
below,i tried with CURRENT instead of OLD , rule was created
but when i execute some operation on it backend connection get closed
my practical result is
CREATE TABLE prim (
> pkey text,
> comment text);
>
> CREATE TABLE ref (
> rkey text,
> skey integer,
> comment text);
>
is Ok
CREATE RULE emp_ins AS ON INSERT TO emp DO
> INSERT INTO emp_log VALUES (
> NEW.ename,
> 'employed',
> NEW.salary,
> getpgusername(),
> 'now'::text);
is also OK Created
CREATE RULE emp_del as on DELETE to emp
do DELETE from emp_log where ename=OLD.ename
old table not found
but
systemdb=> CREATE RULE emp_del as on DELETE to emp
systemdb-> do DELETE from emp_log where ename=current.ename
systemdb-> ;
CREATE
systemdb=>
systemdb=> select * from emp;
ename |dept |salary
-------+-----------+----------
wieck |development|$8,000.00
gates |management |$15,000.00
elangog|lucent |$1,000.00
(3 rows)
systemdb=> delete from emp where ename='gates';
PQexec() -- Request was sent to backend, but backend closed the channel
before responding.
This probably means the backend terminated abnormally before or
while processing the request.
systemdb=>
please help me
to create a rule on DELETE
Thanku ,expecting helpful reply
On Wed, 4 Nov 1998, Jan Wieck wrote:
> G.Elangovan wrote:
>
> >
> > Can any one help me to CREATE RULE
> > please give me an example with
> > a simple table(s) definition and to create rule on that table
>
> It's on my personal TODO to create a rule system
> documentation for users too. The one in the programmers
> manual for v6.4 is a bit technical, but you might want to
> read it anyway.
>
> Anything below addresses the v6.4 rule system!
>
> Sample 1 - constraint follow/delete
>
> CREATE TABLE prim (
> pkey text,
> comment text);
>
> CREATE TABLE ref (
> rkey text,
> skey integer,
> comment text);
>
> --
> -- Rule to let ref.rkey follow prim.pkey
> --
> CREATE RULE prim_follow AS ON UPDATE TO prim
> WHERE NEW.pkey != OLD.pkey DO
> UPDATE ref SET rkey = NEW.pkey WHERE rkey = OLD.pkey;
>
> --
> -- Rule to constraint delete references from ref to pkey
> --
> CREATE RULE prim_delete AS ON DELETE TO prim DO
> DELETE FROM ref WHERE rkey = OLD.pkey;
>
> --
> -- Some tests
> --
> INSERT INTO prim VALUES ('k1', 'to get updated');
> INSERT INTO prim VALUES ('k2', 'to get deleted');
> INSERT INTO ref VALUES ('k1', 1, 'must follow');
> INSERT INTO ref VALUES ('k1', 2, 'must follow');
> INSERT INTO ref VALUES ('k2', 1, 'must get deleted');
> INSERT INTO ref VALUES ('k2', 2, 'must get deleted');
>
> UPDATE prim SET pkey = 'new1' WHERE pkey = 'k1';
> SELECT * FROM ref;
> rkey|skey|comment
> ----+----+----------------
> k2 | 1|must get deleted
> k2 | 2|must get deleted
> new1| 1|must follow
> new1| 2|must follow
> (4 rows)
>
> DELETE FROM prim WHERE pkey = 'k2';
> SELECT * FROM ref;
> rkey|skey|comment
> ----+----+-----------
> new1| 1|must follow
> new1| 2|must follow
> (2 rows)
>
> Sample 2 - logging of table changes
>
> CREATE TABLE emp (
> ename text,
> dept text,
> salary money);
>
> CREATE TABLE emp_log (
> ename text,
> action text,
> newsal money,
> uname name,
> when datetime);
>
> --
> -- Rule to log new created employees
> --
> CREATE RULE emp_ins AS ON INSERT TO emp DO
> INSERT INTO emp_log VALUES (
> NEW.ename,
> 'employed',
> NEW.salary,
> getpgusername(),
> 'now'::text);
>
> --
> -- Rule to log when salary raises
> --
> CREATE RULE emp_raise AS ON UPDATE TO emp
> WHERE NEW.salary > OLD.salary DO
> INSERT INTO emp_log VALUES (
> NEW.ename,
> 'raised',
> NEW.salary,
> getpgusername(),
> 'now'::text);
>
> --
> -- Rule to log when salary is lowered
> --
> CREATE RULE emp_lower AS ON UPDATE TO emp
> WHERE NEW.salary < OLD.salary DO
> INSERT INTO emp_log VALUES (
> NEW.ename,
> 'lowered',
> NEW.salary,
> getpgusername(),
> 'now'::text);
>
> --
> -- Rule to log when employee is fired
> --
> CREATE RULE emp_fire AS ON DELETE TO emp DO
> INSERT INTO emp_log VALUES (
> OLD.ename,
> 'fired',
> '$0',
> getpgusername(),
> 'now'::text);
>
>
> --
> -- Some tests
> --
> INSERT INTO emp VALUES ('wieck', 'development', '$8000.00');
> INSERT INTO emp VALUES ('gates', 'management', '$15000.00');
>
> UPDATE emp SET salary = '$10000.00' WHERE ename = 'wieck';
> UPDATE emp SET salary = '$12000.00' WHERE ename = 'gates';
>
> DELETE FROM emp WHERE ename = 'gates';
>
> SELECT * FROM emp_log;
> ename|action |newsal |uname|when
> -----+--------+----------+-----+----------------------------
> wieck|employed|$8,000.00 |pgsql|Wed Nov 04 12:19:24 1998 MET
> gates|employed|$15,000.00|pgsql|Wed Nov 04 12:19:26 1998 MET
> wieck|raised |$10,000.00|pgsql|Wed Nov 04 12:19:27 1998 MET
> gates|lowered |$12,000.00|pgsql|Wed Nov 04 12:19:28 1998 MET
> gates|fired |$0.00 |pgsql|Wed Nov 04 12:19:30 1998 MET
> (5 rows)
>
> Note: The explicit 'now'::text is required. Otherwise 'now'
> is interpreted at CREATE RULE time and all log entries will
> contain that date instead of the time it happened.
>
> Jan
>
> --
>
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me. #
> #======================================== jwieck@debis.com (Jan Wieck) #
>
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1998-11-05 16:54 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-11-04 11:35 Rules (was: Re: [SQL] How can I optimize...) Jan Wieck <jwieck@debis.com>
1998-11-05 15:36 ` G.Elangovan <elangog@wipinfo.soft.net>
1998-11-05 12:08 ` Jan Wieck <jwieck@debis.com>
1998-11-05 16:54 ` G.Elangovan <elangog@wipinfo.soft.net>
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