Thread: Some additional PostgreSQL questions
> > Question 2:
> > How can I implement a constraint which would always ensure the
> > SUM(money_amount) WHERE type = 1 in a specified table is always
> > zero ?
>
> I would think you'd want your function to run AFTER INSERT not
> BEFORE INSERT.
Yes, this now works, thank you.
I also implemented UPDATE, DELETE, and I have a question:
compta=> SELECT id, montant_signe, lot FROM ecriture;id | montant_signe | lot
----+---------------+----- 2 | 200.00 | 0 3 | 50.00 | 0 1 | -250.00 | 0
(3 rows)
compta=> UPDATE ecriture SET montant_signe = -249 WHERE id = 1;
ERROR: Sum of ecritures in lot is not zero
compta=> UPDATE ecriture SET montant_signe = -250 WHERE id = 1;
UPDATE 1
compta=> DELETE FROM ecriture WHERE id = 1;
DELETE 0
The funny thing is the DELETE not saying an error, but not deleting (which
is good, but I would like an error).
Now, something else:
compta=> BEGIN WORK;
BEGIN
compta=> SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINTS
compta=> UPDATE ecriture SET montant_signe = -249 WHERE id = 1;
ERROR: Sum of ecritures in lot is not zero
How can I defer the trigger call til the end of the transaction ?
For reference:
CREATE TRIGGER t_ecriture_balance_insert AFTER INSERT OR UPDATE OR DELETE ON ecriture FOR EACH ROW EXECUTE
PROCEDUREf_ecriture_balance_check ();
> > The following works, once. The second time it doesn't work (in the
> > same session/backend, see below for the error).
>
> If you want to build dynamically-modified queries in plpgsql, you need
> to use EXECUTE. That includes references to tables that you're dropping
> and recreating between calls of the function.
I have tried that, it unfortunately does not work (I must have something
wrong in the syntax). I have simplified the test case as much as I could:
CREATE TABLE ecriture(libelle TEXT NOT NULL);
CREATE OR REPLACE FUNCTION f_insertion_lot(TEXT, TEXT, TEXT) RETURNS INT4 AS 'BEGIN EXECUTE ''INSERT INTO
ecriture(libelle)'' || '' SELECT '' || quote_ident($2 || ''.libelle'')
||'' FROM '' || quote_ident($2) || '' ORDER BY '' || quote_ident($2 ||
''.id''); RETURN 0; -- faking END;' LANGUAGE 'plpgsql';
CREATE TEMPORARY TABLE insert_temp(id SERIAL NOT NULL, libelle TEXT NOT
NULL);
INSERT INTO insert_temp(libelle) VALUES ('Test1');
INSERT INTO insert_temp(libelle) VALUES ('Test2');
SELECT f_insertion_lot('ignore', 'insert_temp', 'ignore');
Thank you for your valuable input.
My thanks also to Christopher Kings-Lynne <chriskl@familyhealth.com.au>
You can see the actual (now mostly working) code at: http://www.linux-gull.ch/projets/compta/
especially:
http://www.linux-gull.ch/projets/compta/compta.tar.gz
Marc SCHAEFER <alphanet-postgresql-sql@alphanet.ch> writes:
> compta=> DELETE FROM ecriture WHERE id = 1;
> DELETE 0
> The funny thing is the DELETE not saying an error, but not deleting (which
> is good, but I would like an error).
Then make your trigger raise an error. Returning NULL out of the
trigger means "silently suppress this operation". There's not much
point in having the system report an error; it has no idea what the
error condition is, while the trigger presumably knows why it's unhappy
and so can give a useful error message.
> How can I defer the trigger call til the end of the transaction ?
AFAIK we don't currently have end-of-transaction triggers, only
end-of-statement triggers.
> EXECUTE ''INSERT INTO ecriture(libelle)''
> || '' SELECT ''
> || quote_ident($2 || ''.libelle'')
> || '' FROM ''
> || quote_ident($2)
> || '' ORDER BY ''
> || quote_ident($2 || ''.id'');
I think you want quote_ident($2) || ''.libelle'' and so forth.
What you'll get from that is "tablename.libelle", what you want
is "tablename".libelle, no?
regards, tom lane
On Mon, 27 May 2002, Marc SCHAEFER wrote:
> > compta=> DELETE FROM ecriture WHERE id = 1;
> > DELETE 0
>
> > The funny thing is the DELETE not saying an error, but not deleting (which
> > is good, but I would like an error).
>
> Then make your trigger raise an error. Returning NULL out of the
It does:
CREATE TRIGGER t_ecriture_balance_insert AFTER INSERT OR UPDATE OR DELETE ON ecriture FOR EACH ROW EXECUTE
PROCEDUREf_ecriture_balance_check ();
CREATE FUNCTION f_ecriture_balance_check () RETURNS opaque AS 'DECLARE amount NUMERIC(10, 2); BEGIN
amount:= 0; SELECT SUM(montant_signe) FROM ecriture WHERE (lot = NEW.lot) INTO
amount;
IF (amount != 0) THEN RAISE EXCEPTION ''Sum of ecritures in lot % is not zero but %'',
NEW.lot, amount; END IF;
RETURN new; END;' LANGUAGE 'plpgsql';
which works, at least in some cases:
compta=> UPDATE ecriture SET montant_signe = -249 WHERE id = 1;
ERROR: Sum of ecritures in lot 1 is not zero but 1.00
compta=> UPDATE ecriture SET montant_signe = -250 WHERE id = 1;
UPDATE 1
compta=> DELETE FROM ecriture WHERE id = 1;
DELETE 0
now, removing an ecriture should make the balance unbalanced, and as it's
run AFTER should detect the problem, no ?
I tried to modify it slightly so to use OLD instead of NEW:
CREATE TRIGGER t_ecriture_balance_delete AFTER DELETE ON ecriture FOR EACH ROW EXECUTE PROCEDURE
f_ecriture_balance_check_delete();
however it wasn't better.
PS: the second problem is solved thanks to your help, including my initial problem (question 3) where two transaction
ina session would fail.
PS/2: I am now using 7.2.1.
schaefer@alphanet.ch writes:
> The funny thing is the DELETE not saying an error, but not deleting (which
> is good, but I would like an error).
>>
>> Then make your trigger raise an error. Returning NULL out of the
> It does:
Hm. The "NEW.lot" part will not work in an AFTER DELETE trigger (I'm a
bit surprised that it doesn't raise an error --- I guess it is inserting
a NULL instead). You want OLD.lot for the DELETE case.
I don't know why you are getting the DELETE 0 result, but it's not
because of this trigger. AFTER triggers can't suppress individual
row actions, because the action is already done --- the most they
can do is raise an error to abort the whole transaction. Perhaps
you still have a BEFORE trigger in there somewhere?
regards, tom lane