Re: Triggers for inserting on VIEWS

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 DEFAULT CURRENT_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.description AS 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_libelle ALIAS for $1;
piece_date ALIAS for $2;
piece_descr ALIAS for $3;
ecriture_date ALIAS for $4;
ecriture_descr ALIAS for $5;
ecriture_montant ALIAS for $6;
ecriture_de_compte ALIAS for $7;
ecriture_a_compte ALIAS for $8;
lot_id INT4;
piece_id INT4;
BEGIN
SELECT nextval(\'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, compte c2
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;