Forums PostgreSQL.fr

jduv

Membre

Utilisation des null dans une procedure plpgsql

Bonjour à tous,

Voici mon pb sur lequel je bloque depuis quelques temps.

Je veux utiliser un trigger pour déclencher l'insertion dans une base distante en utilisant dblink, j'ai donc écrit la procédure ci dessous qui fonctionne bien sauf dans un cas :
si l'un des champs est null alors rien ne se passe

Je met ici le code de création des tables et de la procédure :

Base 1 :
 

CREATE TABLE t_test1
(
  id_test serial NOT NULL,
  nb1 integer NOT NULL,
  nb2 integer,
  text1 character varying(20),
  CONSTRAINT t_test1_pkey PRIMARY KEY (id_test)
)
WITH (OIDS=TRUE);

CREATE TRIGGER trigger_new_tuple
  AFTER INSERT
  ON t_test1
  FOR EACH ROW
  EXECUTE PROCEDURE gen_new_tuple();

CREATE OR REPLACE FUNCTION gen_new_tuple()
  RETURNS "trigger" AS
$BODY$
BEGIN

		PERFORM dblink_exec('dbname=base_test3','INSERT INTO t_test3(nb1,nb2,texte,id_base_source) VALUES ('|| NEW.nb1 ||','|| NEW.nb2 ||','''|| NEW.text1 ||''',1)');
		RETURN NEW;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

et la base de destination base 3

CREATE TABLE t_test3
(
  id_test serial NOT NULL,
  nb1 integer NOT NULL,
  nb2 integer NOT NULL,
  id_base_source integer NOT NULL,
  texte character varying(20),
  CONSTRAINT t_test3_pkey PRIMARY KEY (id_test)
)
WITH (OIDS=TRUE);

Donc en résumé si je fait :

INSERT INTO t_test1(nb1,nb2,text1) VALUES (10,11,'toto');

Je récupére bien 10,11,toto,1 dans ma base3 table t_test3

mais dès que l'une des valeurs de nb2 ou text1 est null alors rien ne se passe ??

Si quelqu'un a une solution qui saute aux yeux je suis preneur,
J'ai essayé avec des COALESCE(val,NULL) et d'autres mais rien à faire.

Merci d'avance de toute piste.

Marc Cousin

Membre

Re : Utilisation des null dans une procedure plpgsql

La concaténation d'un NULL avec une chaine de caractères retourne NULL.

VALUES ('|| NEW.nb1 ||','|| NEW.nb2 ||','''|| NEW.text1 ||''',1)') retourne donc null dès que nb2 ou text1 vaut null

Avec un coalesce (NEW.nb2,''NULL'') ca devrait passer (et la même chose sur text1).
J'ai peut être oublié un jeu de ' dans la bagarre, mais vous devriez trouver le reste.

---

Marc.

jduv

Membre

Re : Utilisation des null dans une procedure plpgsql

Bonjour,
C'est la première piste que j'ai suivi mais sans résultat pour le moment, si je fait :

VALUES ('|| NEW.nb1 ||','|| COALESCE(NEW.nb2,NULL) ||','''|| COALESCE(NEW.text1,NULL) ||''',1)');

le coalesce renvoi NULL et donc tous vaut null et rien ne passe
et avec un ajout de simple quote autour du NEW.text1 il ajoute la chaine 'NULL' dans le champ text1 et cela ne fonctionne pas pour le champ nb2 puisque c'est un entier attendu.
rq là c'est normal, c'est ce que je lui demande dans le cas présent, or moi il me faut des vrai NULL ?

VALUES ('|| NEW.nb1 ||','|| COALESCE(NEW.nb2,NULL) ||','''|| COALESCE(NEW.text1,'NULL') ||''',1)');

je n'ai pas trouvé la bonne combinaison de quote pour le moment

Merci

Marc Cousin

Membre

Re : Utilisation des null dans une procedure plpgsql

J'y suis je pense arrivé avec la sytaxe suivante :

'INSERT INTO t_test3(nb1,nb2,texte,id_base_source) VALUES ('|| NEW.nb1 ||','|| COALESCE(NEW.nb2::text,'NULL') ||','||COALESCE(''''||NEW.text1||'''','NULL'),1)'

La chaine générée est :

INSERT INTO t_test1(nb1,nb2,text1) VALUES (10,NULL,NULL);
DEBUG:  INSERT INTO t_test3(nb1,nb2,texte,id_base_source) VALUES (10,NULL,NULL,1)
INSERT 16403 1
test=# INSERT INTO t_test1(nb1,nb2,text1) VALUES (10,1,'toto');
DEBUG:  INSERT INTO t_test3(nb1,nb2,texte,id_base_source) VALUES (10,1,'toto',1)
INSERT 16404 1

Ce qui me semble correct (mais horrible comme code )

Je présume qu'on est bien en standard_conforming_strings à ON ?

Au passage, si le code se complique, je pense qu'il serait bon d'avoir une (ou des) fonctions qui font le coalesce à l'extérieur du code (une par type de données)

Dernière modification par Marc Cousin (29/05/2009 18:05:24)

---

Marc.

jduv

Membre

Re : Utilisation des null dans une procedure plpgsql

Bonsoir,
Merci pour les réponses, j'ai eu un pb avec votre code, peut être l'histoire du standard_conforming_strings à ON ??

Mais avec la petite modif ci dessous cela passe bien

PERFORM dblink_exec('dbname=base_test3','INSERT INTO t_test3(nb1,nb2,texte,id_base_source) 
VALUES ('|| NEW.nb1 ||','|| COALESCE(NEW.nb2::text,'NULL') ||','|| COALESCE(NEW.text1::text,'NULL') ||',	1)');

Je vais quand même regarder cette histoire de standard_conforming_strings dès que possible

Merci
@+