pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedrules
22+ messages / 17 participants
[nested] [flat]
* rules
@ 1999-03-05 11:53 Remigiusz Sokolowski <rems@gdansk.sprint.pl>
1999-03-05 15:58 ` Re: [SQL] rules Jan Wieck <jwieck@debis.com>
1999-03-05 18:01 ` Re: [SQL] rules Emils Klotins <emils@mail.usis.bkc.lv>
0 siblings, 2 replies; 22+ messages in thread
From: Remigiusz Sokolowski @ 1999-03-05 11:53 UTC (permalink / raw)
To: pgsql-sql
Hi!
I try to learn how to create rules in postgres, but something goes wrong,
cause nothing happens (or eventually some errors).
I have two tables
create table one (
one_id int4 primary key not null default nextval('schem_seq'),
one_name text,
one_lock text
);
create table many (
many_id int4 primary key not null default
nextval('schem_seq'),
many_onef int4 not null, //this is foreign key equal
to one_id from one table
many_name text,
many_lock text
);
I try to create two rules
-first one on update one_id in one table
CREATE RULE rupd_one AS ON UPDATE TO one
DO UPDATE many
SET many_onef = new.one_id
WHERE many_onef = current.one_id;
-second one on delete record from one table
CREATE RULE rdel_one AS ON DELETE TO one
DO DELETE FROM many
WHERE many_onef = old.one_id;
In short these two seem don't work. I'm not sure of either syntax or some
other errors - psql at first rule don't report any errors, just output
UPDATE 0
and second one usually something like this:
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.
Thanks for any response
Rem
-------------------------------------------------------------------*------------
Remigiusz Sokolowski e-mail: rems@gdansk.sprint.pl * *
-----------------------------------------------------------------*****----------
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] rules
1999-03-05 11:53 rules Remigiusz Sokolowski <rems@gdansk.sprint.pl>
@ 1999-03-05 15:58 ` Jan Wieck <jwieck@debis.com>
1999-03-08 07:29 ` Re: [SQL] rules Remigiusz Sokolowski <rems@gdansk.sprint.pl>
1 sibling, 1 reply; 22+ messages in thread
From: jwieck@debis.com @ 1999-03-05 15:58 UTC (permalink / raw)
To: rems@gdansk.sprint.pl; +Cc: pgsql-sql
> I try to create two rules
> -first one on update one_id in one table
> CREATE RULE rupd_one AS ON UPDATE TO one
> DO UPDATE many
> SET many_onef = new.one_id
> WHERE many_onef = current.one_id;
>
> -second one on delete record from one table
> CREATE RULE rdel_one AS ON DELETE TO one
> DO DELETE FROM many
> WHERE many_onef = old.one_id;
>
> In short these two seem don't work. I'm not sure of either syntax or some
> other errors - psql at first rule don't report any errors, just output
> UPDATE 0
> and second one usually something like this:
> 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.
There seems nothing wrong with the rules, except that you
should use OLD instead of CURRENT in the update rule too
because CURRENT will disappear in v6.5.
But from the 'UPDATE 0' I guess you're using a Postgres
version prior to v6.4 - right? I fixed the rule system for
v6.4, before that it was so badly broken that you should not
even try.
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] 22+ messages in thread
* Re: [SQL] rules
1999-03-05 11:53 rules Remigiusz Sokolowski <rems@gdansk.sprint.pl>
1999-03-05 15:58 ` Re: [SQL] rules Jan Wieck <jwieck@debis.com>
@ 1999-03-08 07:29 ` Remigiusz Sokolowski <rems@gdansk.sprint.pl>
0 siblings, 0 replies; 22+ messages in thread
From: Remigiusz Sokolowski @ 1999-03-08 07:29 UTC (permalink / raw)
To: Jan Wieck <jwieck@debis.com>; +Cc: pgsql-sql
>
> There seems nothing wrong with the rules, except that you
> should use OLD instead of CURRENT in the update rule too
> because CURRENT will disappear in v6.5.
>
> But from the 'UPDATE 0' I guess you're using a Postgres
> version prior to v6.4 - right? I fixed the rule system for
> v6.4, before that it was so badly broken that you should not
> even try.
>
You're right - I use ver. 6.3.2
Ufff - I feel better, when I know :-)
thanks
Rem
-------------------------------------------------------------------*------------
Remigiusz Sokolowski e-mail: rems@gdansk.sprint.pl * *
-----------------------------------------------------------------*****----------
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] rules
1999-03-05 11:53 rules Remigiusz Sokolowski <rems@gdansk.sprint.pl>
@ 1999-03-05 18:01 ` Emils Klotins <emils@mail.usis.bkc.lv>
1 sibling, 0 replies; 22+ messages in thread
From: Emils Klotins @ 1999-03-05 18:01 UTC (permalink / raw)
To: jwieck@debis.com; pgsql-sql
[5 Mar 99,, 16:58] Jan Wieck wrote:
>
> But from the 'UPDATE 0' I guess you're using a Postgres
> version prior to v6.4 - right? I fixed the rule system for
> v6.4, before that it was so badly broken that you should not
> even try.
Thanks a lot. I forgot to mention that I indeed use Postgres v6.3.2
^ permalink raw reply [nested|flat] 22+ messages in thread
* Rules
@ 2000-11-09 08:50 Peeter Smitt <peeter.smitt@online.ee>
2000-12-31 12:18 ` Re: Rules Peter Eisentraut <peter_e@gmx.net>
2001-01-02 23:51 ` Re: Rules Jan Wieck <janwieck@Yahoo.com>
0 siblings, 2 replies; 22+ messages in thread
From: Peeter Smitt @ 2000-11-09 08:50 UTC (permalink / raw)
To: pgsql-sql
Hi
I'm trying to make updateable view useing rules.
CREATE RULE update_rule AS ON UPDATE TO table DO INSTEAD
SELECT fun1(new);
Thing is that backend gives this error.
ERROR: parser: parse error at or near ")"
What i'm doing wrong? Are there any other ways to make updatebale multi-table views?
thanks and joyfull new year.
-----------------------------------------
Peeter Smitt
AS Delfi Online
Pärnu mnt. 158
11317 Tallinn, Estonia
Tel +3726501734
GSM +37256647653
peeter.smitt@online.ee
www.delfi.ee
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Rules
2000-11-09 08:50 Rules Peeter Smitt <peeter.smitt@online.ee>
@ 2000-12-31 12:18 ` Peter Eisentraut <peter_e@gmx.net>
1 sibling, 0 replies; 22+ messages in thread
From: Peter Eisentraut @ 2000-12-31 12:18 UTC (permalink / raw)
To: Peeter Smitt <peeter.smitt@online.ee>; +Cc: pgsql-sql
Peeter Smitt writes:
> CREATE RULE update_rule AS ON UPDATE TO table DO INSTEAD
> SELECT fun1(new);
>
> Thing is that backend gives this error.
> ERROR: parser: parse error at or near ")"
Try double quoting "new", since it's a reserved word.
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Rules
2000-11-09 08:50 Rules Peeter Smitt <peeter.smitt@online.ee>
@ 2001-01-02 23:51 ` Jan Wieck <janwieck@Yahoo.com>
1 sibling, 0 replies; 22+ messages in thread
From: Jan Wieck @ 2001-01-02 23:51 UTC (permalink / raw)
To: Peeter Smitt <peeter.smitt@online.ee>; +Cc: pgsql-sql
Peeter Smitt wrote:
> Hi
>
> I'm trying to make updateable view useing rules.
>
> CREATE RULE update_rule AS ON UPDATE TO table DO INSTEAD
> SELECT fun1(new);
>
> Thing is that backend gives this error.
> ERROR: parser: parse error at or near ")"
>
> What i'm doing wrong? Are there any other ways to make updatebale multi-table views?
There are, but you haven't read the documentation about the
rule system, which is a common mistake.
Read chapter 8 of the PostgreSQL Programmers manual for
details on how to setup updateable views.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 22+ messages in thread
* rules
@ 2001-04-26 14:21 Martín Marqués <martin@bugs.unl.edu.ar>
2001-04-26 20:53 ` Re: rules Joel Burton <jburton@scw.org>
2001-04-26 21:24 ` Re: rules Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 2 replies; 22+ messages in thread
From: Martín Marqués @ 2001-04-26 14:21 UTC (permalink / raw)
To: pgsql-sql
Is it posible to make a rule execute more then one query?
Something like:
CREATE RULE rule_name AS ON INSERT TO table1
DO INSTEAD
INSERT INTO table2 VALUES
(new.value1,new.value2)
INSERT INTO table3 VALUES
(x,y)
If not, is there a way to do this? Triggers maybe?
Saludos... :-)
--
El mejor sistema operativo es aquel que te da de comer.
Cuida tu dieta.
-----------------------------------------------------------------
Martin Marques | mmarques@unl.edu.ar
Programador, Administrador | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: rules
2001-04-26 14:21 rules Martín Marqués <martin@bugs.unl.edu.ar>
@ 2001-04-26 20:53 ` Joel Burton <jburton@scw.org>
1 sibling, 0 replies; 22+ messages in thread
From: Joel Burton @ 2001-04-26 20:53 UTC (permalink / raw)
To: Martín Marqués <martin@bugs.unl.edu.ar>; +Cc: pgsql-sql
On Thu, 26 Apr 2001, [iso-8859-1] Martín Marqués wrote:
> Is it posible to make a rule execute more then one query?
>
> Something like:
>
> CREATE RULE rule_name AS ON INSERT TO table1
> DO INSTEAD
> INSERT INTO table2 VALUES
> (new.value1,new.value2)
> INSERT INTO table3 VALUES
> (x,y)
test=# \h create rule
Command: CREATE RULE
Description: Defines a new rule
Syntax:
CREATE RULE name AS ON event
TO object [ WHERE condition ]
DO [ INSTEAD ] action
where action can be:
NOTHING
|
query
|
( query ; query ... )
|
[ query ; query ... ]
ie
CREATE RULE snog AS ON UPDATE TO foo DO INSTEAD ( ...; ... );
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: rules
2001-04-26 14:21 rules Martín Marqués <martin@bugs.unl.edu.ar>
@ 2001-04-26 21:24 ` Jan Wieck <JanWieck@Yahoo.com>
1 sibling, 0 replies; 22+ messages in thread
From: Jan Wieck @ 2001-04-26 21:24 UTC (permalink / raw)
To: Martín Marqués <martin@bugs.unl.edu.ar>; +Cc: pgsql-sql
Martín Marqués wrote:
> Is it posible to make a rule execute more then one query?
>
> Something like:
>
> CREATE RULE rule_name AS ON INSERT TO table1
> DO INSTEAD
> INSERT INTO table2 VALUES
> (new.value1,new.value2)
> INSERT INTO table3 VALUES
> (x,y)
Yes:
CREATE RULE rule_name AS ON INSERT TO table1
DO INSTEAD (
INSERT INTO table2 VALUES
(new.value1,new.value2);
INSERT INTO table3 VALUES
(x,y);
);
You just omitted the parens and semicoli :-)
>
> If not, is there a way to do this? Triggers maybe?
Triggers too (even if yes above and effectively you haven't
asked for):
CREATE FUNCTION whatever () RETURNS opaque AS '
BEGIN
INSERT INTO table2 VALUES
(new.value1,new.value2);
INSERT INTO table3 VALUES
(...);
RETURN NULL; -- returning NULL from a BEFORE trigger
-- suppresses the triggering INSERT to
-- happen.
END;'
LANGUAGE 'plpgsql';
CREATE TRIGGER table1_ins BEFORE INSERT ON table1
FOR EACH ROW EXECUTE whatever();
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] 22+ messages in thread
* RULES
@ 2001-05-22 09:56 J.Fernando Moyano <txinete@wanadoo.es>
0 siblings, 0 replies; 22+ messages in thread
From: J.Fernando Moyano @ 2001-05-22 09:56 UTC (permalink / raw)
To: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I need something like this .....
CREATE RULE piezas_add AS
ON insert TO piezas
DO update materia_prima set usadas=(usadas+1)
where n_material=new.n_material;
CREATE RULE piezas_delete AS
ON delete TO piezas
DO update materia_prima set usadas=(usadas-1)
where n_material=old.n_material;
Inserting all is OK.
... but in deleting operations nothing changes.
Can someone help me ??
And .... i would like to do the same when updating ....
Can i do something like this ??
What is the rigth syntax ??
Must i create 2 rules to perform the action ???
CREATE RULE piezas_update AS
ON update TO piezas.n_material
DO
update materia_prima set usadas=(usadas-1) where
n_material=old.n_material
???WHAAAT???
update materia_prima set usadas=(usadas+1) where
n_material=new.n_material
thanks !!!
fer
- --
Fernando Moyano
Frase del día:
- --------------
El que retiene algo que no necesita es igual a un ladrón. (M Gandhi)
(*) SymeX ==> http://symex.lantik.com
(*) WDBIL ==> http://wdbil.sourceforge.net
(*) Informate sobre LINUX en http://www.linux.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.4 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQE7CjfWoZaf9MvtDvcRAkYbAJ9VfQGtF4NCQ8nOKegtLPKn0NwX5ACeLA7O
wyQnOfKGH1xRYnYBtB6jvW8=
=OB6B
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 22+ messages in thread
* RULES
@ 2001-05-22 17:19 J.Fernando Moyano <txinete@wanadoo.es>
2001-05-22 07:44 ` Re: RULES Martín Marqués <martin@bugs.unl.edu.ar>
0 siblings, 1 reply; 22+ messages in thread
From: J.Fernando Moyano @ 2001-05-22 17:19 UTC (permalink / raw)
To: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Well ... i'm testing carefully the two rules ==>
CREATE RULE piezas_add AS
ON insert TO piezas
DO update materia_prima set usadas=(usadas+1)
where n_material=new.n_material;
CREATE RULE piezas_delete AS
ON delete TO piezas
DO update materia_prima set usadas=(usadas-1)
where n_material=old.n_material;
Inserting is OK.
Deleting is broken if it deletes more than one row. The rule is executed only
one time each delete command, and not one time each deleted row.
It's this OK ??? Am i wrong ???
It's a "feature" ?? ;-)
Thanks
- --
Fernando Moyano
Frase del día:
- --------------
¡CocaCola esasín! ¡prrrrrrrrrts, bruuuuuuuups!
(*) SymeX ==> http://symex.lantik.com
(*) WDBIL ==> http://wdbil.sourceforge.net
(*) Informate sobre LINUX en http://www.linux.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.4 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQE7Cp+HoZaf9MvtDvcRAqV/AKCQLhl34VF3sfPt3O5i5w5MFpcZRQCgmm5b
duRciSRJhzTJLuhFrNLUcWQ=
=It2U
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: RULES
2001-05-22 17:19 RULES J.Fernando Moyano <txinete@wanadoo.es>
@ 2001-05-22 07:44 ` Martín Marqués <martin@bugs.unl.edu.ar>
2001-05-22 14:53 ` Re: RULES Ross J. Reedstrom <reedstrm@rice.edu>
0 siblings, 1 reply; 22+ messages in thread
From: Martín Marqués @ 2001-05-22 07:44 UTC (permalink / raw)
To: J.Fernando Moyano <txinete@wanadoo.es>; pgsql-sql
On Mar 22 May 2001 20:19, J.Fernando Moyano wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> Well ... i'm testing carefully the two rules ==>
>
> CREATE RULE piezas_add AS
> ON insert TO piezas
> DO update materia_prima set usadas=(usadas+1)
> where n_material=new.n_material;
>
> CREATE RULE piezas_delete AS
> ON delete TO piezas
> DO update materia_prima set usadas=(usadas-1)
> where n_material=old.n_material;
>
> Inserting is OK.
> Deleting is broken if it deletes more than one row. The rule is executed
> only one time each delete command, and not one time each deleted row.
>
> It's this OK ??? Am i wrong ???
> It's a "feature" ?? ;-)
That's the right behaviour!
Each time you do a delete, besides the delete, it will execute the update....
If more then one row is deletes, update.... gets executed only once.
You'll have to do more advance programing there. See if count(*) can help you
in any way.
I see you have to update usadas=(usadas-1). Try something like:
usadas=(usadas-(select count(*) from piezas WHERE condition_delete) )
Saludos... :-)
--
Cualquiera administra un NT.
Ese es el problema, que cualquiera administre.
-----------------------------------------------------------------
Martin Marques | mmarques@unl.edu.ar
Programador, Administrador | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: RULES
2001-05-22 17:19 RULES J.Fernando Moyano <txinete@wanadoo.es>
2001-05-22 07:44 ` Re: RULES Martín Marqués <martin@bugs.unl.edu.ar>
@ 2001-05-22 14:53 ` Ross J. Reedstrom <reedstrm@rice.edu>
0 siblings, 0 replies; 22+ messages in thread
From: Ross J. Reedstrom @ 2001-05-22 14:53 UTC (permalink / raw)
To: pgsql-sql
On Tue, May 22, 2001 at 10:44:06AM +0300, Mart?n Marqu?s wrote:
> On Mar 22 May 2001 20:19, J.Fernando Moyano wrote:
> > Deleting is broken if it deletes more than one row. The rule is executed
> > only one time each delete command, and not one time each deleted row.
> >
> > It's this OK ??? Am i wrong ???
> > It's a "feature" ?? ;-)
>
> That's the right behaviour!
> Each time you do a delete, besides the delete, it will execute the update....
> If more then one row is deletes, update.... gets executed only once.
>
Rules are rewrites of the SQL query, before it gets planned and
optimized, so they are good for things that need to happen once per
statement. Triggers, on the other hand, fire per tuple, when the actual
bits are going into/out of the underlying table. They're good for things
that need to happen per tuple.
See Bruce's book for the basics:
http://www.ca.postgresql.org/docs/aw_pgsql_book/node166.html
Ross
^ permalink raw reply [nested|flat] 22+ messages in thread
* RULES
@ 2001-11-20 23:56 Fábio Santana <fabio3c@terra.com.br>
2001-11-21 12:58 ` Re: RULES Patrick Welche <prlw1@newn.cam.ac.uk>
0 siblings, 1 reply; 22+ messages in thread
From: Fábio Santana @ 2001-11-20 23:56 UTC (permalink / raw)
To: Hackers Postgresql <pgsql-hackers@postgresql.org>
My friends, how i do to get a list of all rules in one table? Thanks!!!!
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: RULES
2001-11-20 23:56 RULES Fábio Santana <fabio3c@terra.com.br>
@ 2001-11-21 12:58 ` Patrick Welche <prlw1@newn.cam.ac.uk>
2001-11-21 15:57 ` Re: [HACKERS] RULES Ross J. Reedstrom <reedstrm@rice.edu>
0 siblings, 1 reply; 22+ messages in thread
From: Patrick Welche @ 2001-11-21 12:58 UTC (permalink / raw)
To: Fábio Santana <fabio3c@terra.com.br>; +Cc: Hackers Postgresql <pgsql-hackers@postgresql.org>
On Tue, Nov 20, 2001 at 08:56:55PM -0300, Fábio Santana wrote:
> My friends, how i do to get a list of all rules in one table? Thanks!!!!
select rulename,definition from pg_rules where tablename='YourTable';
(Don't have any defined, so can't check)
I have a similar question on triggers:
create table a (
id integer primary key
);
create table b (
a_id integer references a(id) match full
);
select * from pg_trigger where tgname ~* '^RI_';
Gives me 3 rows. They all contain the same tgargs. Is it therefore
sufficient to select distinct tgnargs,tgargs if I just want to be able to
recreate the "references... match full" part of the create table statement?
It seems that the rows differ in
tgtype tgrelid tgconstrrelid tgfoid
9 table a table b RI_FKey_noaction_del
17 table a table b RI_FKey_noaction_upd
21 table b table a RI_FKey_check_ins
9=row,delete, 17=row,update, 21=row,insert,update ?
Why are the first 2 constraints there? It seems to be the last one which
says "If I insert,update table b, check it is a valid entry with table a"
Is that right?
Patrick
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [HACKERS] RULES
2001-11-20 23:56 RULES Fábio Santana <fabio3c@terra.com.br>
2001-11-21 12:58 ` Re: RULES Patrick Welche <prlw1@newn.cam.ac.uk>
@ 2001-11-21 15:57 ` Ross J. Reedstrom <reedstrm@rice.edu>
2001-11-21 16:43 ` Re: [HACKERS] RULES Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 22+ messages in thread
From: Ross J. Reedstrom @ 2001-11-21 15:57 UTC (permalink / raw)
To: Patrick Welche <prlw1@newn.cam.ac.uk>; +Cc: pgsql-sql
<note that this is not really HACKERs type material, so I moved the
response to the SQL list: I'm CCing Patrick directly, since I don't
know if he reads that list>
On Wed, Nov 21, 2001 at 12:58:37PM +0000, Patrick Welche wrote:
>
> create table a (
> id integer primary key
> );
>
> create table b (
> a_id integer references a(id) match full
> );
>
> select * from pg_trigger where tgname ~* '^RI_';
>
> Gives me 3 rows. They all contain the same tgargs. Is it therefore
> sufficient to select distinct tgnargs,tgargs if I just want to be able to
> recreate the "references... match full" part of the create table statement?
>
> It seems that the rows differ in
>
> tgtype tgrelid tgconstrrelid tgfoid
> 9 table a table b RI_FKey_noaction_del
> 17 table a table b RI_FKey_noaction_upd
> 21 table b table a RI_FKey_check_ins
>
> 9=row,delete, 17=row,update, 21=row,insert,update ?
>
> Why are the first 2 constraints there? It seems to be the last one which
> says "If I insert,update table b, check it is a valid entry with table a"
>
> Is that right?
As far as it goes. Realize that a primary key <-> foreign key relationship
is two way: it constrains the parent table as well as the child.
Consider what happens if you have something like this:
test=# select * from a;
id
----
1
2
3
4
(4 rows)
test=# select * from b;
a_id
------
1
1
3
3
2
1
3
(7 rows)
test=#
So, what happens if you do:
test=# delete from a where id=4;
DELETE 1
test=# delete from a where id=3;
ERROR: <unnamed> referential integrity violation - key in a still referenced from b
test=# update a set id=4 where id=3;
ERROR: <unnamed> referential integrity violation - key in a still referenced from b
Since the key is still in use in b, it can't be deleted or modified in a.
Note that if the key had been setup as a CASCADE, then modifying (or deleting)
from a would effect b as well, as so:
drop table b;
create table b ( a_id integer references a(id) match full ON UPDATE cascade);
<fill with some data>
test=# select * from b;
a_id
------
3
3
1
1
2
3
(6 rows)
test=# update a set id=4 where id=3;
UPDATE 1
test=# select * from b;
a_id
------
1
1
2
4
4
4
(6 rows)
Pretty cool, huh?
Ross
--
Ross Reedstrom, Ph.D. reedstrm@rice.edu
Executive Director phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics fax: 713-348-6182
Rice University MS-39
Houston, TX 77005
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [HACKERS] RULES
2001-11-20 23:56 RULES Fábio Santana <fabio3c@terra.com.br>
2001-11-21 12:58 ` Re: RULES Patrick Welche <prlw1@newn.cam.ac.uk>
2001-11-21 15:57 ` Re: [HACKERS] RULES Ross J. Reedstrom <reedstrm@rice.edu>
@ 2001-11-21 16:43 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 22+ messages in thread
From: Stephan Szabo @ 2001-11-21 16:43 UTC (permalink / raw)
To: Ross J. Reedstrom <reedstrm@rice.edu>; +Cc: Patrick Welche <prlw1@newn.cam.ac.uk>; pgsql-sql
On Wed, 21 Nov 2001, Ross J. Reedstrom wrote:
Want to pop in here.
> On Wed, Nov 21, 2001 at 12:58:37PM +0000, Patrick Welche wrote:
> >
> > create table a (
> > id integer primary key
> > );
> >
> > create table b (
> > a_id integer references a(id) match full
> > );
> >
> > select * from pg_trigger where tgname ~* '^RI_';
> >
> > Gives me 3 rows. They all contain the same tgargs. Is it therefore
> > sufficient to select distinct tgnargs,tgargs if I just want to be able to
> > recreate the "references... match full" part of the create table statement?
Not quite, you'll lose the referential action information if you don't
include info out of tgfoids on the pk table's triggers and you'll lose the
deferment info if you don't pay attention to tgdeferrable and
tginitdeferred. In your case you're not using those, but...
There have been messages in the past about how to get the reference
information. You should be able to find a function or something in
the archives :)
^ permalink raw reply [nested|flat] 22+ messages in thread
* rules
@ 2004-05-19 17:20 Jie Liang <jie@stbernard.com>
2004-05-19 17:50 ` Re: [SQL] rules Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 22+ messages in thread
From: Jie Liang @ 2004-05-19 17:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: postgres-list <pgsql-admin@postgresql.org>; pgsql-sql
According to the document of rule:
CREATE RULE rulename AS ON delete TO mytablename DO
(
delete from aaa where id=OLD.id;
Delete from bbb where id=OLD.id;
Delete from ccc where id=OLD.id
);
Should work, but it doesn't, what wrong with it?
Even I use { .... }
Jie Liang
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] rules
2004-05-19 17:20 rules Jie Liang <jie@stbernard.com>
@ 2004-05-19 17:50 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-06-25 07:16 ` Re: rules Jan Norman Pedersen <jan@multimediasyd.dk>
0 siblings, 1 reply; 22+ messages in thread
From: Stephan Szabo @ 2004-05-19 17:50 UTC (permalink / raw)
To: Jie Liang <jie@stbernard.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; postgres-list <pgsql-admin@postgresql.org>; pgsql-sql
On Wed, 19 May 2004, Jie Liang wrote:
> According to the document of rule:
>
> CREATE RULE rulename AS ON delete TO mytablename DO
> (
> delete from aaa where id=OLD.id;
> Delete from bbb where id=OLD.id;
> Delete from ccc where id=OLD.id
> );
>
>
> Should work, but it doesn't, what wrong with it?
What is it doing and what version is this on?
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: rules
2004-05-19 17:20 rules Jie Liang <jie@stbernard.com>
2004-05-19 17:50 ` Re: [SQL] rules Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-06-25 07:16 ` Jan Norman Pedersen <jan@multimediasyd.dk>
0 siblings, 0 replies; 22+ messages in thread
From: Jan Norman Pedersen @ 2004-06-25 07:16 UTC (permalink / raw)
To: pgsql-sql
Anslag
"Stephan Szabo" <sszabo@megazone.bigpanda.com> wrote in message
news:20040519104830.J85664@megazone.bigpanda.com...
> On Wed, 19 May 2004, Jie Liang wrote:
>
> > According to the document of rule:
> >
> > CREATE RULE rulename AS ON delete TO mytablename DO
> > (
> > delete from aaa where id=OLD.id;
> > Delete from bbb where id=OLD.id;
> > Delete from ccc where id=OLD.id
> > );
> >
> >
> > Should work, but it doesn't, what wrong with it?
>
> What is it doing and what version is this on?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: rules
@ 2004-05-19 17:41 Jie Liang <jie@stbernard.com>
0 siblings, 0 replies; 22+ messages in thread
From: Jie Liang @ 2004-05-19 17:41 UTC (permalink / raw)
To: Jie Liang <jie@stbernard.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: postgres-list <pgsql-admin@postgresql.org>; pgsql-sql
Sorry, wrong question.
-----Original Message-----
From: Jie Liang
Sent: Wednesday, May 19, 2004 10:20 AM
To: Tom Lane
Cc: postgres-list; pgsql-sql@postgresql.org
Subject: [ADMIN] rules
According to the document of rule:
CREATE RULE rulename AS ON delete TO mytablename DO
(
delete from aaa where id=OLD.id;
Delete from bbb where id=OLD.id;
Delete from ccc where id=OLD.id
);
Should work, but it doesn't, what wrong with it?
Even I use { .... }
Jie Liang
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column's datatypes do not match
^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2004-06-25 07:16 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-03-05 11:53 rules Remigiusz Sokolowski <rems@gdansk.sprint.pl>
1999-03-05 15:58 ` Jan Wieck <jwieck@debis.com>
1999-03-08 07:29 ` Remigiusz Sokolowski <rems@gdansk.sprint.pl>
1999-03-05 18:01 ` Emils Klotins <emils@mail.usis.bkc.lv>
2000-11-09 08:50 Rules Peeter Smitt <peeter.smitt@online.ee>
2000-12-31 12:18 ` Re: Rules Peter Eisentraut <peter_e@gmx.net>
2001-01-02 23:51 ` Re: Rules Jan Wieck <janwieck@Yahoo.com>
2001-04-26 14:21 rules Martín Marqués <martin@bugs.unl.edu.ar>
2001-04-26 20:53 ` Joel Burton <jburton@scw.org>
2001-04-26 21:24 ` Jan Wieck <JanWieck@Yahoo.com>
2001-05-22 09:56 RULES J.Fernando Moyano <txinete@wanadoo.es>
2001-05-22 17:19 RULES J.Fernando Moyano <txinete@wanadoo.es>
2001-05-22 07:44 ` Re: RULES Martín Marqués <martin@bugs.unl.edu.ar>
2001-05-22 14:53 ` Re: RULES Ross J. Reedstrom <reedstrm@rice.edu>
2001-11-20 23:56 RULES Fábio Santana <fabio3c@terra.com.br>
2001-11-21 12:58 ` Re: RULES Patrick Welche <prlw1@newn.cam.ac.uk>
2001-11-21 15:57 ` Re: [HACKERS] RULES Ross J. Reedstrom <reedstrm@rice.edu>
2001-11-21 16:43 ` Re: [HACKERS] RULES Stephan Szabo <sszabo@megazone23.bigpanda.com>
2004-05-19 17:20 rules Jie Liang <jie@stbernard.com>
2004-05-19 17:50 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-06-25 07:16 ` Jan Norman Pedersen <jan@multimediasyd.dk>
2004-05-19 17:41 Re: rules Jie Liang <jie@stbernard.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