Обсуждение: Triggers for inserting on VIEWS

Hi,

at least with PostgreSQL 7.1 it was possible to create a trigger on a
view. 7.2 seems to fail with:
  psql:t:25: ERROR:  CreateTrigger: relation "egg_view" is not a table

is there any replacement so that inserting somewhere acts on multiple
tables ?

Thank you.

Code reference: (stupid, real code is more complex and uses multiple                tables)

DROP TRIGGER t_egg ON egg_view;
DROP FUNCTION f_egg_insert ();
DROP VIEW egg_view;
DROP TABLE egg;
DROP SEQUENCE egg_id_seq;

CREATE TABLE egg(id SERIAL,                description TEXT,                UNIQUE(id), PRIMARY KEY(id));

CREATE VIEW egg_view  AS SELECT description FROM egg;

CREATE FUNCTION f_egg_insert ()  RETURNS opaque  AS 'BEGIN         INSERT INTO egg (description)
VALUES(NEW.description);     END;'  LANGUAGE 'plpgsql';
 

CREATE TRIGGER t_egg  BEFORE INSERT  ON egg_view  FOR EACH ROW  EXECUTE PROCEDURE f_egg_insert();

Marc,

> at least with PostgreSQL 7.1 it was possible to create a trigger on a
> view. 7.2 seems to fail with:
> 
>    psql:t:25: ERROR:  CreateTrigger: relation "egg_view" is not a
> table
> 
> is there any replacement so that inserting somewhere acts on multiple
> tables ?

Yes.  Use the RULES system, which were a lot like triggers but are
better tailored to deal with inserts and updates on VIEWs.
See:
http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/rules.html

-Josh Berkus

Marc SCHAEFER <schaefer@alphanet.ch> writes:
> at least with PostgreSQL 7.1 it was possible to create a trigger on a
> view.

Use a rule instead.  7.2 will reject an attempt to insert into a view
without a replacement rule, so a trigger would do you no good anyhow.
(I thought that behavior went back further than 7.2, actually.)
        regards, tom lane

On Sat, 3 Aug 2002, Marc SCHAEFER wrote:

> is there any replacement so that inserting somewhere acts on multiple
> tables ?

Thanks for the suggestion to use RULES. 

My solution (comments welcome):

DROP RULE r_entree_rapide_ecriture_insert;
DROP FUNCTION f_entree_rapide_ecriture_insert(TEXT,                                             DATE,
                         TEXT,                                             DATE,
    TEXT,                                             NUMERIC(10, 2),                                             INT4,
                                           INT4);
 
DROP VIEW entree_rapide_ecriture;
DROP TABLE ecriture;
DROP SEQUENCE ecriture_id_seq;
DROP SEQUENCE ecriture_lot_seq;
DROP TABLE piece;
DROP SEQUENCE piece_id_seq;
DROP TABLE compte;
DROP SEQUENCE compte_id_seq;

CREATE TABLE compte(id SERIAL NOT NULL,                   libelle TEXT NOT NULL,                   montant_initial
NUMERIC(10,2) DEFAULT 0.0 NOT NULL,                   UNIQUE(libelle),                   PRIMARY KEY(id), UNIQUE(id));
 

CREATE TABLE piece(id SERIAL NOT NULL,                  libelle TEXT NOT NULL,       date DATE NOT NULL DEFAULT
CURRENT_DATE,                 description TEXT,                  UNIQUE(libelle),                  PRIMARY KEY(id),
UNIQUE(id));

CREATE SEQUENCE ecriture_lot_seq;  
CREATE TABLE ecriture(id SERIAL NOT NULL,          piece INT4 REFERENCES piece NOT NULL,          date DATE NOT NULL
DEFAULTCURRENT_DATE,          compte INT4 REFERENCES compte NOT NULL,          description TEXT,          montant
NUMERIC(10,2) NOT NULL                        CHECK (montant > CAST(0.0 AS NUMERIC(10, 2))),                     type
CHAR(1)NOT NULL CHECK (type IN ('D', 'A')),                     lot INT4 NOT NULL DEFAULT currval('ecriture_lot_seq'),
       PRIMARY KEY(id), UNIQUE(id));
 

CREATE VIEW entree_rapide_ecriture  AS SELECT p.libelle AS piece_libelle,            p.date AS piece_date,
p.descriptionAS piece_descr,            e1.date AS ecriture_date,            e1.description AS ecriture_descr,
 e1.montant AS ecriture_montant,            e1.compte AS ecriture_de_compte,            e2.compte AS ecriture_a_compte
  FROM piece p, ecriture e1, ecriture e2     WHERE (e1.lot = e2.lot)           AND (e1.date = e2.date)           AND
(e1.montant= e2.montant)           AND (e1.piece = e2.piece)           AND (e1.type != e2.type)           AND (e1.piece
=p.id)           AND (e1.type = 'D');
 

CREATE FUNCTION f_entree_rapide_ecriture_insert(TEXT,                                               DATE,
                               TEXT,                                               DATE,
              TEXT,                                               NUMERIC(10, 2),
       INT4,                                               INT4)  RETURNS INT4 -- void  AS 'DECLARE
piece_libelleALIAS for $1;         piece_date ALIAS for $2;         piece_descr ALIAS for $3;         ecriture_date
ALIASfor $4;         ecriture_descr ALIAS for $5;         ecriture_montant ALIAS for $6;         ecriture_de_compte
ALIASfor $7;         ecriture_a_compte ALIAS for $8;         lot_id INT4;         piece_id INT4;      BEGIN
SELECTnextval(\'ecriture_lot_seq\') INTO lot_id;
 
         SELECT nextval(\'piece_id_seq\') INTO piece_id;
         INSERT INTO piece (id, libelle, date, description)            VALUES(piece_id,
piece_libelle,       piece_date,        piece_descr);
 
         INSERT INTO ecriture(piece,                              date,                              compte,
                 description,                              montant,                              type,
           lot)            VALUES(piece_id,                   ecriture_date,                   ecriture_de_compte,
            ecriture_descr,                   ecriture_montant,                   \'D\',                   lot_id);
 
         INSERT INTO ecriture(piece,                              date,                              compte,
                 description,                              montant,                              type,
           lot)            VALUES(piece_id,                   ecriture_date,                   ecriture_a_compte,
           ecriture_descr,                   ecriture_montant,                   \'A\',                   lot_id);
 
         RETURN 0; -- Assumes won\'t do anything.      END;'  LANGUAGE 'plpgsql';

-- NOTES
--    - Triggers do not work in this case (VIEWs) since 7.1 final.
CREATE RULE r_entree_rapide_ecriture_insert  AS ON INSERT TO entree_rapide_ecriture  DO INSTEAD SELECT
f_entree_rapide_ecriture_insert(NEW.piece_libelle,                        NEW.piece_date,
NEW.piece_descr,                        NEW.ecriture_date,                         NEW.ecriture_descr,
      NEW.ecriture_montant,                         NEW.ecriture_de_compte,
NEW.ecriture_a_compte);

INSERT INTO compte(libelle) VALUES ('Caisse');
INSERT INTO compte(libelle) VALUES ('CCP');
INSERT INTO compte(libelle) VALUES ('Créanciers');
INSERT INTO compte(libelle) VALUES ('Débiteurs');
INSERT INTO compte(libelle) VALUES ('Frais généraux');
INSERT INTO compte(libelle) VALUES ('Equipement');
INSERT INTO compte(libelle) VALUES ('Assurances');
INSERT INTO compte(libelle) VALUES ('Privé');
INSERT INTO compte(libelle) VALUES ('Capital');

INSERT INTO entree_rapide_ecriture(piece_libelle,                                  piece_date,
       piece_descr,                                  ecriture_date,                                  ecriture_descr,
                             ecriture_montant,                                  ecriture_de_compte,
            ecriture_a_compte)  SELECT 'ASS-1',         '2002-07-10',         'Assurance RC prof.: 2002-06-25 au
2002-12-31',        '2002-07-10',         'Facture',         654.0,         c1.id,         c2.id  FROM compte c1,
comptec2  WHERE (c1.libelle = 'Caisse')        AND (c2.libelle = 'Assurances');
 

INSERT INTO entree_rapide_ecriture(piece_libelle,                                  piece_date,
       piece_descr,                                  ecriture_date,                                  ecriture_descr,
                             ecriture_montant,                                  ecriture_de_compte,
            ecriture_a_compte)  SELECT 'FACT-1',         '2002-07-30',         'Facture XX',         '2002-07-30',
  'Facture',         456.0,         c1.id,         c2.id  FROM compte c1, compte c2  WHERE (c1.libelle = 'Débiteurs')
    AND (c2.libelle = 'Caisse');         
 

SELECT * FROM entree_rapide_ecriture;