pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTriggers for inserting on VIEWS
4+ messages / 3 participants
[nested] [flat]
* Triggers for inserting on VIEWS
@ 2002-08-03 18:04 Marc SCHAEFER <schaefer@alphanet.ch>
0 siblings, 3 replies; 4+ messages in thread
From: Marc SCHAEFER @ 2002-08-03 18:04 UTC (permalink / raw)
To: pgsql-sql
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();
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Triggers for inserting on VIEWS
@ 2002-08-03 18:33 Josh Berkus <josh@agliodbs.com>
parent: Marc SCHAEFER <schaefer@alphanet.ch>
2 siblings, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2002-08-03 18:33 UTC (permalink / raw)
To: Marc SCHAEFER <schaefer@alphanet.ch>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Triggers for inserting on VIEWS
@ 2002-08-03 18:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marc SCHAEFER <schaefer@alphanet.ch>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2002-08-03 18:34 UTC (permalink / raw)
To: Marc SCHAEFER <schaefer@alphanet.ch>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Triggers for inserting on VIEWS
@ 2002-08-03 20:14 Marc SCHAEFER <schaefer@alphanet.ch>
parent: Marc SCHAEFER <schaefer@alphanet.ch>
2 siblings, 0 replies; 4+ messages in thread
From: Marc SCHAEFER @ 2002-08-03 20:14 UTC (permalink / raw)
To: 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 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;
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-08-03 20:14 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-03 18:04 Triggers for inserting on VIEWS Marc SCHAEFER <schaefer@alphanet.ch>
2002-08-03 18:33 ` Josh Berkus <josh@agliodbs.com>
2002-08-03 18:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-08-03 20:14 ` Marc SCHAEFER <schaefer@alphanet.ch>
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