pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: cnliou@eurosport.com
To: pgsql-sql@postgresql.org
Subject: Problem With A Rule (clear version)
Date: Fri, 12 Apr 2002 10:50:49 GMT
Message-ID: <200204121050.31c2@th00.opsion.fr> (raw)

Many thanks for the patience! Tom,

I am afraid that this too-complete example makes
readers headache.

Thank you all!

CN
===============
CREATE TABLE table1 (
--the invoice table.
PRIMARY KEY(column10,column1,column2),
column10 VARCHAR(255), --id#='1'
column1  VARCHAR(255), --invoice#
column2  SMALLINT, --serial number
column6  NUMERIC, --amount
column7  "char"  --indicator: either 'r' or 'E'
);

CREATE TABLE table2 (
--the voucher detail table.
CONSTRAINT c1 FOREIGN KEY (column10,column1)
REFERENCES table3 (column10,column1) ON UPDATE
CASCADE ON DELETE CASCADE,
PRIMARY KEY (column10,column1,column2),
column10 VARCHAR(255), --id#='1'
column1  VARCHAR(255), --voucher#
column6  "char",  --indicator: either '1' or '2'
column7  NUMERIC, --amount
column8  VARCHAR(255), --matched column for
table1.column7
column9  VARCHAR(255) --matched column for
table1.column2
);

CREATE TABLE table3 (
--the voucher master table.
PRIMARY KEY (column10,column1),
column10 VARCHAR(255), --id#='1'
column1  VARCHAR(255), --voucher#
column4  "char",  --status='N'
column5  VARCHAR(255) --matched column for
table1.column1
);

CREATE RULE rule1 AS ON UPDATE TO table1
DO UPDATE table2 SET column7=NEW.column6 WHERE
column10=NEW.column10 
AND column1=(SELECT column1 FROM table3 WHERE
 column10=NEW.column10 AND column4='N'
 AND column5=NEW.column1)
AND column6='2'
AND column8=OLD.column7
AND column9=OLD.column2;


db1=# SELECT column10,column1,column2,column6,column7
FROM table1;
column10 | column1  | column2 | column6 | column7
----------+----------+---------+---------+---------
 1        | Collect1 |       2 |      11 | r
 1        | Collect1 |       1 |     130 | r
(2 rows)

db1=# SELECT column1,column5 FROM table3 WHERE
column10='1' AND column4='N' AND column5='Collect1';
   column1   | column5
-------------+----------
 AC200108083 | Collect1
(1 row)

db1=# SELECT
column10,column1,column6,column7,column8,column9 FROM
table2 WHERE column10='1' AND column1=(SELECT column1
FROM table3 WHERE column10='1' AND column4='N' AND
column5='Collect1') AND column6='2' AND column8='r'
AND column9='2';

 column10 |   column1   | column6 |  column7  |
column8 | column9
----------+-------------+---------+-----------+------
---+---------
 1        | AC200108083 | 2       | 11.000000 | r    
  | 2
(1 row)

db1=# UPDATE table1 SET column6=13 WHERE column2=1;
<postgresql waits forever here. So, ctrl-c to
interrupt psql.>
Cancel request sent
ERROR:  Query was cancelled.
db1=# update table2 set column7=13 where column10='1'
and column1=(SELECT column1 FROM table3 WHERE
column10='1' AND column4='N' AND column5='Collect1')
and column6='2' and column8='r' and column9=1;
UPDATE 1

IF rule1 is changed to the following version, then
the update to table1 works:

CREATE RULE rule1 AS ON UPDATE TO table1
DO UPDATE table2 SET column7=NEW.column6 WHERE
column10=NEW.column10
AND column1='AC200108083' 
AND column6='2'
AND column8=OLD.column7
AND column9=OLD.column2;

--------------------------------------------------------
You too can have your own email address from Eurosport.
http://www.eurosport.com








view thread (3+ messages)  latest in thread

Message-ID: <200204121050.31c2@th00.opsion.fr>
Permalink:  ../200204121050.31c2@th00.opsion.fr/
Also on:    postgresql.org/message-id/200204121050.31c2@th00.opsion.fr

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: cnliou@eurosport.com
  Subject: Re: Problem With A Rule (clear version)
  In-Reply-To: <200204121050.31c2@th00.opsion.fr>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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