Re: Various PostgreSQL questions

> 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