Re: 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

Marc SCHAEFER <alphanet-postgresql-sql(at)alphanet(dot)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