Thread: Problem with CREATE RULE ON DELETE (PostgreSQL only executes the first expression)

Hi,

I have found a problem with PostgreSQL as described below.
If anyone has any thoughts on this, as how to either fix it, or bypass it,
please share your wisdom :)

with kind regards,

Joost Roeleveld

ps. putting the work-around in the front-end is not an option for me, since
I have to
     use ms-access as a front-end.


============================================================================
                        POSTGRESQL BUG REPORT
============================================================================


Your name  :  Joost Roeleveld
Your email address : J.Roeleveld@softhome.net


System Configuration
---------------------
  Architecture (example: Intel Pentium)   : Intel Pentium 75, 32 meg Ram

  Operating System (example: Linux 2.0.26 ELF)  : Linux 2.0.36 i586 unknown
(Redhat 5.2)

  PostgreSQL version (example: PostgreSQL-6.5.2):   PostgreSQL-6.5.2

  Compiler used (example:  gcc 2.8.0)  : 2.7.2.3  (Installed using rpm
package obtained from ftp-site)


Please enter a FULL description of your problem:
------------------------------------------------
When creating delete-rules for views, i have found that only the first
expression is being executed, when
using multiple expressions.

I have managed to do this for Insert, and i think for Update as well...
although i haven't gotten around to
testing that yet.


Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
Here follows a SQL-script, which can be used to reproduce the problem.
I'm sorry this makes the email a bit long, but i don't want to make the
usual
mistake of producing less information then necessary.

CREATE TABLE "adressen_table" (
 "adres_id" SERIAL PRIMARY KEY,
 "straatnaam" character varying(50),
 "huisnummer" int4,
 "postcode" character varying(50),
 "land" character varying(50)
 );

CREATE TABLE "bedrijven_table" (
 "firma_id" SERIAL PRIMARY KEY,
 "firmanaam" character varying(50),
 "vestigingsadres_code" int4,
 "soort_code" int4,
 "categorie_code" int4,
 "omschrijving" character varying(250)
 );

CREATE TABLE "soort_table" (
 "soort_id" SERIAL PRIMARY KEY,
 "soortnaam" character varying(50)
 );

CREATE TABLE "categorie_table" (
 "categorie_id" SERIAL PRIMARY KEY,
 "categorienaam" character varying(50)
 );

CREATE VIEW bedrijven_view AS
 SELECT bd.firma_id,
   bd.firmanaam,
   ad.straatnaam,
   ad.huisnummer,
   ad.postcode,
   ad.land,
   sr.soortnaam,
   ct.categorienaam,
   bd.omschrijving
 FROM   bedrijven_table bd,
   adressen_table ad,
   soort_table sr,
   categorie_table ct
 WHERE  bd.vestigingsadres_code = ad.adres_id
 AND    bd.soort_code = sr.soort_id
 AND    bd.categorie_code = ct.categorie_id;

CREATE FUNCTION get_soort_nummer(varchar) RETURNS int4 AS
 'SELECT soort_id FROM soort_table
  WHERE soortnaam = $1;'
 LANGUAGE 'sql';

CREATE FUNCTION get_categorie_nummer(varchar) RETURNS int4 AS
 'SELECT categorie_id FROM categorie_table
  WHERE categorienaam = $1;'
 LANGUAGE 'sql';

CREATE FUNCTION get_adres_nummer(varchar,int4,varchar,varchar) RETURNS int4
AS
 'SELECT max(adres_id) FROM adressen_table
  WHERE straatnaam = $1 AND huisnummer = $2 AND postcode = $3 AND land =
$4;'
 LANGUAGE 'sql';

CREATE RULE insert_bedrijven_view AS ON INSERT
 TO bedrijven_view
 DO INSTEAD (
  INSERT INTO adressen_table (straatnaam,huisnummer,postcode,land)
   VALUES (NEW.straatnaam, NEW.huisnummer, NEW.postcode, NEW.land);
  INSERT INTO bedrijven_table (firmanaam,vestigingsadres_code,soort_code,
       categorie_code,omschrijving)
   VALUES (NEW.firmanaam,
    get_adres_nummer(NEW.straatnaam, NEW.huisnummer,
       NEW.postcode, NEW.land),
    get_soort_nummer(NEW.soortnaam),
    get_categorie_nummer(NEW.categorienaam),
    NEW.omschrijving);
  );

CREATE RULE update_bedrijven_view AS ON UPDATE
 TO bedrijven_view
 DO INSTEAD (
  UPDATE adressen_table
   SET straatnaam = NEW.straatnaam,
       huisnummer = NEW.huisnummer,
       postcode = NEW.postcode,
       land = NEW.land
   WHERE adres_id = get_adres_nummer(OLD.straatnaam,
     OLD.huisnummer, OLD.postcode, OLD.land);
  UPDATE bedrijven_table
   SET firmanaam = NEW.firmanaam,
       vestigingsadres_code = get_adres_nummer(OLD.straatnaam,
     OLD.huisnummer, OLD.postcode, OLD.land),
       soort_code = get_soort_nummer(NEW.soortnaam),
       categorie_code = get_categorie_nummer(NEW.categorienaam),
       omschrijving = NEW.omschrijving;
  );

CREATE RULE delete_bedrijven_view AS ON DELETE
 TO bedrijven_view
 DO INSTEAD (
  DELETE FROM adressen_table
   WHERE adres_id = get_adres_nummer(straatnaam,
     huisnummer,postcode,land);
  DELETE FROM bedrijven_table
   WHERE firma_id = firma_id;
  );

INSERT INTO soort_table (soortnaam) VALUES ('Food / Proces');
INSERT INTO soort_table (soortnaam) VALUES ('Chemie / Proces');
INSERT INTO soort_table (soortnaam) VALUES ('Tuinbouw');
INSERT INTO soort_table (soortnaam) VALUES ('Farmaceutica');
INSERT INTO soort_table (soortnaam) VALUES ('Brandbeveiliging');
INSERT INTO soort_table (soortnaam) VALUES ('Leveranciers');
INSERT INTO soort_table (soortnaam) VALUES ('Akkerbouw');
INSERT INTO soort_table (soortnaam) VALUES ('Waterbehandeling');
INSERT INTO soort_table (soortnaam) VALUES ('Overigen');
INSERT INTO soort_table (soortnaam) VALUES ('Producenten Overigen');
INSERT INTO soort_table (soortnaam) VALUES ('Ziekenhuizen');

INSERT INTO categorie_table (categorienaam) VALUES ('Dealer');
INSERT INTO categorie_table (categorienaam) VALUES ('Eindgebruiker');
INSERT INTO categorie_table (categorienaam) VALUES ('Overige');

=====> here follows the sequence of queries I entered
select * from adressen_table;
select * from bedrijven_table;
select * from bedrijven_view;

===( this to make sure the tables are really empty )

insert into bedrijven_view (firmanaam,straatnaam,
  huisnummer,postcode,land,soortnaam,
  categorienaam,omschrijving)
 values ('firmanaam','straatnaam',123,'postcode',
  'land','Ziekenhuizen','Dealer','omschrijving');
select * from adressen_table;
select * from bedrijven_table;
select * from bedrijven_view;

===( this to make sure the information has been entered, no problems so far)

delete from bedrijven_view where firma_id = 1;
select * from adressen_table;
select * from bedrijven_table;
select * from bedrijven_view;

===( the entry in 'bedrijven_table' shouldn't be there, if i were to change
the sequence of the 'delete from'
         statements in the 'on delete'-rule, the entry in adressen_table is
still there, and bedrijven_table is empty)


If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------