Обсуждение: Various PostgreSQL questions
Version: 7.1release-3.potato.1 (Debian package of 7.1release-3)
Question 1: Apparently using NUMERIC(10, 2) in a type definition (DECLARE) of a stored procedure in PL/pgSQL doesn't
work;FLOAT works instead. Is this normal ?
Question 2: How can I implement a constraint which would always ensure the SUM(money_amount) WHERE type = 1 in a
specifiedtable is always zero ?
I tried that:
CREATE FUNCTION f_ecriture_balance_check () RETURNS opaque AS 'DECLARE amount FLOAT;
BEGIN amount := 0; SELECT SUM(montant_signe) FROM ecriture
WHERE(lot = NEW.lot) INTO amount;
IF (CAST(amount AS NUMERIC(10,2)) != - NEW.montant_signe) THEN RAISE EXCEPTION ''Sum of
ecrituresin lot is not zero''; END IF;
RETURN new; END;' LANGUAGE 'plpgsql';
-- TODO -- - STATEMENT instead of ROW when supported by PostgreSQL CREATE TRIGGER
t_ecriture_balance_insert BEFORE INSERT ON ecriture FOR EACH ROW EXECUTE PROCEDURE
f_ecriture_balance_check();
Of course this is only for INSERT (UPDATE and DELETE are modified). Should I use SET CONSTRAINTS ALL DEFERRED within
atransaction ? This function always fails: I assume it gets called for each of the row and not for the final state at
theend of the transaction.
Question 3: The following works, once. The second time it doesn't work (in the same session/backend, see below for
theerror).
-- Only inherited. CREATE TABLE insert_temporary_table_base (id SERIAL NOT NULL, numero_compte
INT4NOT NULL, libelle TEXT NOT NULL, date_valeur DATE NOT NULL, montant_signe NUMERIC(10, 2)
NOTNULL, UNIQUE(id), PRIMARY KEY(id));
CREATE TABLE ecriture(date_valeur DATE NOT NULL, montant_signe NUMERIC(10, 2) NOT NULL,
utilisateur TEXT NOT NULL, exercice TEXT NOT NULL);
CREATE FUNCTION f_insertion_lot(TEXT, TEXT, TEXT) RETURNS INT4 AS 'DECLARE amount FLOAT;
BEGIN INSERT INTO ecriture(utilisateur, exercice,
date_valeur, montant_signe) SELECT $1, $3,
date_valeur, montant_signe FROM insert_temp ORDER
BYinsert_temp.id;
RETURN 0; -- faking END;' LANGUAGE 'plpgsql';
BEGIN WORK; CREATE TEMPORARY TABLE insert_temp ()
INHERITS(insert_temporary_table_base);
INSERT INTO insert_temp (numero_compte, libelle,
date_valeur, montant_signe) VALUES (1000,
'PaiementMarc pour cours SQL', '2002-04-26', -245);
INSERT INTO insert_temp (numero_compte, libelle,
date_valeur, montant_signe) VALUES (3000,
'Marc,cours SQL', '2002-04-26', 200);
INSERT INTO insert_temp (numero_compte, libelle,
date_valeur, montant_signe) VALUES (3010,
'Marc,frais déplacement', '2002-04-26', 50);
SELECT f_insertion_lot('schaefer', 'insert_temp',
'Exercice 2002');
DROP TABLE insert_temp; COMMIT WORK;
BEGIN WORK; CREATE TEMPORARY TABLE insert_temp ()
INHERITS(insert_temporary_table_base);
INSERT INTO insert_temp (numero_compte, libelle,
date_valeur, montant_signe) VALUES (1000,
'PaiementMarc pour cours SQL', '2002-04-26', -245);
INSERT INTO insert_temp (numero_compte, libelle,
date_valeur, montant_signe) VALUES (3000,
'Marc,cours SQL', '2002-04-26', 200);
INSERT INTO insert_temp (numero_compte, libelle,
date_valeur, montant_signe) VALUES (3010,
'Marc,frais déplacement', '2002-04-26', 50);
SELECT f_insertion_lot('schaefer', 'insert_temp',
'Exercice 2002');
DROP TABLE insert_temp; COMMIT WORK;
The error is (at the second COMMIT): CREATE INSERT 633792 1 INSERT 633793 1 INSERT 633794 1
psql:test.sql:104:ERROR: Relation 633722 does not exist psql:test.sql:106: NOTICE: current transaction is
aborted,queries ignored until end of transaction block *ABORT STATE*
Question 4: Is it possible to parametrize the table of a SELECT ?
SELECT * FROM $1 # for example.
Thank you for any hint or pointers!
> Version: 7.1release-3.potato.1 (Debian package of 7.1release-3) You should upgrade to 7.2.1 :) > Question 1: > Apparently using NUMERIC(10, 2) in a type definition (DECLARE) of > a stored procedure in PL/pgSQL doesn't work; FLOAT works instead. > Is this normal ? Don't know the answer to that one. If it doesn't, it's a bug I guess... > 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 ? The best way is CREATE ASSERTION (which Postgres doesn't have yet :) ) > I tried that: > > CREATE FUNCTION f_ecriture_balance_check () > RETURNS opaque > AS 'DECLARE amount FLOAT; > BEGIN > amount := 0; > SELECT SUM(montant_signe) > FROM ecriture > WHERE (lot = NEW.lot) > INTO amount; > > IF (CAST(amount AS NUMERIC(10,2)) != - > NEW.montant_signe) THEN > RAISE EXCEPTION ''Sum of ecritures in lot is > not zero''; > END IF; > > RETURN new; > END;' > LANGUAGE 'plpgsql'; > > -- TODO > -- - STATEMENT instead of ROW when supported by PostgreSQL > CREATE TRIGGER t_ecriture_balance_insert > BEFORE INSERT > ON ecriture > FOR EACH ROW > EXECUTE PROCEDURE f_ecriture_balance_check (); > > Of course this is only for INSERT (UPDATE and DELETE are modified). > Should I use SET CONSTRAINTS ALL DEFERRED within a transaction ? > This function always fails: I assume it gets called for each of the > row and not for the final state at the end of the transaction. Maybe - give it a try! It sounds like it should work... > Question 3: > The following works, once. The second time it doesn't work (in the > same session/backend, see below for the error). Unfortunately there are known problems with temporary relations in stored procedures. I believe there's talk of fixing it, but apparently it's a tricky problem. I can't remember what the workaround was... > Question 4: > Is it possible to parametrize the table of a SELECT ? > > SELECT * FROM $1 # for example. Nope. But there are patches flying around for 7.3/7.4 that implement such a thing. It's not certain whether they'll be in the release any time soon though... Chris
Marc SCHAEFER <schaefer@alphanet.ch> writes:
> Version: 7.1release-3.potato.1 (Debian package of 7.1release-3)
> Question 1:
> 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.
> 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.
> 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.
regards, tom lane
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