Re: Triggers for inserting on VIEWS - Mailing list pgsql-sql

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;