Re: Various PostgreSQL questions

On Mon, 13 May 2002, Tom Lane wrote:

> >    Apparently using NUMERIC(10, 2) in a type definition (DECLARE) of
> >    a stored procedure in PL/pgSQL doesn't work; FLOAT works instead.
> 
> I believe this is fixed in 7.2.

yes, I confirm this is fixed, notably in:
  schaefer@shakotay:/tmp$ dpkg -s postgresql | grep Version  Version: 7.2.1-2

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

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