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 PROCEDURE f_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(at)familyhealth(dot)com(dot)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