From: | Marc SCHAEFER <schaefer(at)alphanet(dot)ch> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Various PostgreSQL questions |
Date: | 2002-05-15 10:00:17 |
Message-ID: | Pine.LNX.3.96.1020515110044.1367A-100000@defian.alphanet.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)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 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:
From | Date | Subject | |
---|---|---|---|
Next Message | Stephane DEWITTE | 2002-05-15 12:08:37 | Help on indexes |
Previous Message | Adam Witney | 2002-05-15 09:23:21 | Re: some questions |