pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedVarious PostgreSQL questions
4+ messages / 3 participants
[nested] [flat]
* Various PostgreSQL questions
@ 2002-05-13 16:33 Marc SCHAEFER <schaefer@alphanet.ch>
0 siblings, 2 replies; 4+ messages in thread
From: Marc SCHAEFER @ 2002-05-13 16:33 UTC (permalink / raw)
To: pgsql-sql
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 specified table 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 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.
Question 3:
The following works, once. The second time it doesn't work (in the
same session/backend, see below for the error).
-- Only inherited.
CREATE TABLE insert_temporary_table_base
(id SERIAL NOT NULL,
numero_compte INT4 NOT NULL,
libelle TEXT NOT NULL,
date_valeur DATE NOT NULL,
montant_signe NUMERIC(10, 2) NOT NULL,
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 BY insert_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,
'Paiement Marc 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,
'Paiement Marc 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!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Various PostgreSQL questions
@ 2002-05-14 01:47 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Marc SCHAEFER <schaefer@alphanet.ch>
1 sibling, 0 replies; 4+ messages in thread
From: Christopher Kings-Lynne @ 2002-05-14 01:47 UTC (permalink / raw)
To: Marc SCHAEFER <schaefer@alphanet.ch>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Various PostgreSQL questions
@ 2002-05-14 02:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marc SCHAEFER <schaefer@alphanet.ch>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2002-05-14 02:11 UTC (permalink / raw)
To: Marc SCHAEFER <schaefer@alphanet.ch>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Various PostgreSQL questions
@ 2002-05-15 10:00 Marc SCHAEFER <schaefer@alphanet.ch>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Marc SCHAEFER @ 2002-05-15 10:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 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@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@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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-05-15 10:00 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-13 16:33 Various PostgreSQL questions Marc SCHAEFER <schaefer@alphanet.ch>
2002-05-14 01:47 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-05-14 02:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-05-15 10:00 ` Marc SCHAEFER <schaefer@alphanet.ch>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox