Forums PostgreSQL.fr

Geo-x

Membre

Retour de résultat vide d'une fonction

Bonjour @ tous,

j'ai un souci de fonction dans postgres/postgis. Je m'explique :

J'ai une table1 qui contient des objets surfaciques
J'ai une table 2 qui contient des ponctuels
Entre les deux tables, les champs sont identiques

Ce que je souhaite faire, c'est récupérer les valeurs ponctuels se toruvant dans les surfaciques mais seulement si les valeurs de la table2 sont unique. Par exemple, s'il y a trois ponctuels dans un surfacique, mais que les valeurs de ces trois points das un champ identique sont 'a','a' et 'x', je ne les prends pas en revanche si la valeur est 'e','e','e', alors je mets à jour ce champ dans ma table1.

Pour réaliser ces manipulations, j'ai réalisé la fonction suivante (avec en entrée, l'identifiant unique de ma zone)

CREATE OR REPLACE FUNCTION fonction("$1" integer)
  RETURNS integer AS
$BODY$

DECLARE

	col_champ record;
	col_champ_txt character varying(125);
	var_valeur record;
	var_valeur_txt character varying(125);

FOR col_champ IN SELECT DISTINCT COLUMN_NAME AS "champ" FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='table1'

	LOOP
		
		EXECUTE 'SELECT DISTINCT "'||col_champ.champ||'" FROM table2 
		WHERE contains((SELECT wkb_geometry FROM table1 WHERE ogc_fid='||$1||'),wkb_geometry)'
		INTO var_valeur;

		IF

			count(var_valeur)='1'

		THEN
		
			col_champ_txt=replace(replace(col_champ.champ::varchar,'(',''),')','');
			var_valeur_txt=replace(replace(replace(var_valeur::varchar,'()','NULL'),'(',''''),')','''');
			EXECUTE 'UPDATE table1 SET '||col_champ_txt||'='||var_valeur_txt||' WHERE ogc_fid='||$1;

		END IF;
		
	END LOOP;
	
RETURN 1;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
COST 100;
ALTER FUNCTION fonction(integer) OWNER TO postgres;

Mais ce que je ne comprend pas,  peu importe ce que j'intégre, mon record var_valeur se retrouve toujours vide en sortie.

Est-ce que vous avez une piste pour m'aider à comprendre d'ou peut venir le problème?

Par avance merci.

gleu

Administrateur

Re : Retour de résultat vide d'une fonction

Il y a plusieurs choses qui ne vont pas dans cette procédure stockée.

* count(var_valeur)

Je suppose que vous cherchez à savoir si vous avez bien récupéré une ligne avec le SELECT précédent. En fait, le EXECUTE ne vous renvoie aucune ligne (si le filtre a supprimé toutes les lignes résultats) ou une seule ligne (que le filtre ait conservé une ou plusieurs lignes). Donc, déjà, vous n'en récupérez qu'une sur X, sans être certain que ce soit toujours la même.

* var_valeur::varchar

var_valeur est une ligne. Je ne sais pas ce que vous cherchez obtenir avec ça, mais certainement pas ce que vous obtenez en bout de compte.

* RETURNS integer

La fonction déclare renvoyer un entier et elle ne renvoit... rien.

Commencez par corriger ça

---

Guillaume.

Geo-x

Membre

Re : Retour de résultat vide d'une fonction

Bonsoir Gleu et merci de votre réponse.

Si je reprend l'exemple que j’avais mis au début, voici ce que je souhaiterais faire :

cas 1 : S'il y a plusieurs résultats 'a','a' et 'x' alors je ne fais rien
cas 2 : Si les résultats sont les suivants 'e','e','e', alors, au moment de l'insertion de l'objet je récupère pour ce champ là la valeur 'e' et je l’intègre automatiquement à l'objet.

Donc voici mon raisonnement :

* count(var_valeur) permet de vérifier combien il y a de lignes avec des valeurs différentes. Dans le cas 1 : 2 lignes, je ne fais donc rien et dans le cas 2 : 1 ligne, je passe donc à l'étape suivante. Depuis le post, j'avais en effet corrigé certaines choses, maintenant la requête donne ceci :

var_nb_tuples=coalesce(sum(count),0)::integer FROM (SELECT DISTINCT col_champ.champ,count(DISTINCT code_insee) FROM table2 WHERE contains($2,table2.wkb_geometry) GROUP BY col_champ.champ)foo;

* var_valeur::varchar en fait ce que je souhaite, c'est obtenir cette valeur unique contenu dans le champ col_champ.champ. Pour ce faire j'ai tenté de faire mais le résultat n'est pas très concluant, visiblement il me renvoi toujours la valeur de la variable col_champ.champ...

var_valeur=EXECUTE 'SELECT '||col_champ.champ||') FROM table2 WHERE contains((SELECT wkb_geometry FROM table1 WHERE ogc_fid='||$1||'),wkb_geometry))foo'

* RETURNS integer oui, dans l'idée, la fonction fait directement un update en base et ne renvoi rien de particulier.

Dernière modification par Geo-x (27/08/2013 08:37:05)

gleu

Administrateur

Re : Retour de résultat vide d'une fonction

Pour "var_valeur::varchar", vu que vous ne récupérez qu'une seule colonne, pourquoi passer par un RECORD ? utilisez directement le bon type et il n'y a plus de question.

Pour "RETURNS integer", remplacer integer par void.

---

Guillaume.

Geo-x

Membre

Re : Retour de résultat vide d'une fonction

Bonjour Gleu et merci de votre aide.

J'ai corrigé le return integer en void. Si j'ai bien compris la documentation, il s'agit bien de préciser que l'on ne renvoi rien du tout ?

Concernant le var_valeur, il est déjà en type text mais visiblement, ça ne fonctionne pas quand même. J'ai du mal à récupérer la valeur d'un champ, voici les étapes auxquelles je procède :

1- Je mets mon nom de champ au format record en format texte

var_champ=replace(replace(replace(col_champ.champ::text,'()','NULL'),'(',''''),')','''');

2-J'essaie (désespérément de récupérer la valeur de la table 2 pour un champ donné :

var_valeur= test FROM (SELECT DISTINCT var_champ AS "test",count(DISTINCT code_insee) FROM table2 WHERE contains($2,table2.wkb_geometry) GROUP BY var_champ)foo;

3- Je construis ma requête qui me permettra de tout intégrer (en INSERT INTO) avec la partie colonnes (précision, compteur_stop=nombre de colonnes dans le record):

		
IF 
	var_insert_colonnes='' AND compteur<>compteur_stop
THEN 
	var_insert_colonnes=var_champ||',';
ELSIF
	var_insert_colonnes<>'' AND compteur<>compteur_stop
THEN
	var_insert_colonnes=var_insert_colonnes||var_champ||',';
ELSIF
	var_insert_colonnes<>'' AND compteur=compteur_stop
THEN 
	var_insert_colonnes=var_insert_colonnes||var_champ;
END IF;

et la partie valeurs :
           

IF 
	var_insert_valeurs='' AND compteur<>compteur_stop
THEN 
	var_insert_valeurs=''''||replace(replace(replace(var_valeur::varchar,'()','NULL'),'(',''''),')','''')||''',''';
ELSIF
	var_insert_valeurs<>'' AND compteur<>compteur_stop
THEN 
	var_insert_valeurs=var_insert_valeurs||replace(replace(replace(var_valeur::varchar,'()','NULL'),'(',''''),')','''')||''',''';
ELSIF
	var_insert_valeurs<>'' AND compteur=compteur_stop
THEN 
	var_insert_valeurs=var_insert_valeurs||replace(replace(replace(var_valeur,'()','NULL'),'(',''''),')','''')||'''';
END IF;

4-En sortie de boucle il me met tout ça dans une requête en INSERT INTO qu'il exécute.

Le problème, c'est que pour le moment, var_valeur prend pour valeur le nom de la colonne et non le contenu de l'enregistrement...

Geo-x

Membre

Re : Retour de résultat vide d'une fonction

J'ai trouvé la solution pour var_valeur! je fais une requête au format chaîne de caractères et je l'éxecute par la suite dans une variable :

requete='SELECT DISTINCT '||var_champ||' AS "test",count(DISTINCT code_insee) FROM table2WHERE ST_Contains('''||CAST(NEW.wkb_geometry As text)||''',table2.wkb_geometry) GROUP BY '|| var_champ;

EXECUTE requete INTO var_valeur;

Maintenant ce que je souhaiterais faire, c'est utiliser cette fonction dans un trigger avec une instruction BEFORE INSERT, comme ça ce sera plus facile à utiliser.

Grossomodo, il s'agit de la même chose, sauf que la seule chose que je ne sais pas faire c'est exécuter une instruction avec une variable contenant le nom de la colonne et un NEW.

Dans l'idée j'aimerais faire ça dans une boucle :

 EXECUTE 'NEW.'||var_champ||'='''||var_valeur||'''';

Pour le moment, si je fais une raise notice, j'ai quand même une syntaxe correcte qui donne :

 EXECUTE 'NEW.champ1='Bonjour';

mais j'ai quand même un message d'erreur de la part de postgres :

ERROR:  syntax error at or near "NEW"

gleu

Administrateur

Re : Retour de résultat vide d'une fonction

NEW est une variable, vous ne pouvez pas le coller dans une chaîne en supposant qu'elle sera interprétée. À priori, il vous suffit de faire :

NEW.champ1 := 'Bonjour';

---

Guillaume.

Geo-x

Membre

Re : Retour de résultat vide d'une fonction

Malheureusement, mon nom de champ est traduit par une variable var_champ inclue dans une boucle.

Quand je fais simplement

NEW.var_champ=var_valeur; 

Postgres me répond

record "new" has no field "var_champ"

et n'interpréte la variable en tant que variable.

gleu

Administrateur

Re : Retour de résultat vide d'une fonction

Ah oui, c'est clair. Vous ne pouvez pas faire ça.

PL/pgsql n'est pas un langage très complet. C'est plutôt une surcouche du SQL pour lui ajouter boucles et tests. Vous n'avez clairement pas toutes les possibilités des langages actuels.

---

Guillaume.

cedric

Membre

Re : Retour de résultat vide d'une fonction

Malheureusement, mon nom de champ est traduit par une variable var_champ inclue dans une boucle.

Quand je fais simplement

NEW.var_champ=var_valeur; 

Postgres me répond

record "new" has no field "var_champ"

et n'interpréte la variable en tant que variable.

On peux probablement faire une fonction sefiled() pour gérer ca. Toutefois, il y a peut etre mieux à faire dans la fonction pour ne pas avoir besoin de faire une fonction setfield() !

Quel est le code de la fonction trigger actuelle ?

---

Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation

Geo-x

Membre

Re : Retour de résultat vide d'une fonction

Bonjour Cedric et Gleu et merci de vos réponses.

Mon trigger actuel se déclenche sur un BEFORE INSERT et voici le code :

--$1 id_pk

DECLARE

	col_champ record;
	var_champ text;
	var_valeur text;
	var_insert_colonnes text;
	var_insert_valeurs text;
	var_nb_tuples integer;
	requete text;
	requete2 text;
	requete3 text;

BEGIN

	var_insert_colonnes='';
	var_insert_valeurs='';
		
	FOR col_champ IN SELECT DISTINCT COLUMN_NAME AS "champ" FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='table1' AND COLUMN_NAME<>'ogc_fid' AND COLUMN_NAME<>'id_table1' AND COLUMN_NAME<>'geometry'

	LOOP
		
		var_champ=replace(replace(replace(col_champ.champ::text,'()','NULL'),'(',''''),')','''');

		requete2='SELECT coalesce(sum(count),0)::integer

		FROM (SELECT DISTINCT '|| col_champ.champ||',count(DISTINCT '||col_champ.champ||') FROM table2 

		WHERE contains('''||CAST($2 As text)||''',table2.geometry) AND '||col_champ.champ||' IS NOT NULL AND '||col_champ.champ||'::text<>'''' 


		GROUP BY '||col_champ.champ||')foo';

		raise notice '%',  requete2;

		EXECUTE requete2 INTO var_nb_tuples;

		raise notice '%', var_nb_tuples;

		IF

			var_nb_tuples=1

		THEN
			var_champ=col_champ.champ::text;
			requete='SELECT DISTINCT '||var_champ||' AS "test",count(DISTINCT code_insee) FROM table2 WHERE ST_Contains('''||CAST($2 As text)||''',table2.geometry) GROUP BY '|| var_champ;		

			EXECUTE requete INTO var_valeur;
		
			var_valeur=replace(var_valeur,'''','''''');

			NEW.var_champ=var_valeur;
			
		END IF;
		
	END LOOP;

	RETURN NEW;

END;

Geo-x

Membre

Re : Retour de résultat vide d'une fonction

Étant donné que mon prblème emmène irrésolument vers une impasse, je souhaiterais en savoir un peu plus par rapport à la fonction suivante :

On peux probablement faire une fonction sefiled() pour gérer ca.

Par avance merci à vous.

Geo-x

gleu

Administrateur

Re : Retour de résultat vide d'une fonction

Aucune idée, il n'existe pas de fonction sefiled fournie par PostgreSQL. Je ne sais pas ce dont voulait parler Cédric.

---

Guillaume.