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