Various PostgreSQL questions - Mailing list 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
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!