My friends, how i do to get a list of all rules in one table? Thanks!!!!


Tue, 20 Nov 2001 20:56:55 -0300
=?iso-8859-1?Q?F=E1bio_Santana?= <fabio3c@terra.com.br>
RULES
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

Wed, 21 Nov 2001 12:58:37 +0000
Patrick Welche <prlw1@newn.cam.ac.uk>
Re: RULES
<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

Wed, 21 Nov 2001 09:57:31 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: [HACKERS] RULES

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 :)


Wed, 21 Nov 2001 08:43:06 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: [HACKERS] RULES