Forums PostgreSQL.fr

Geo-x

Membre

Mélange variable / colonne fonction

Bonjour @ tous.

Voilà, j'essaie de créer une fonction qui me permettra de chercher un caractère spécifique dans toutes les tables d'une base de données dans laquelle l'utilisateur lance sa fonction.

Pour ce faire, j'ai en entrée une variable qui correspond au texte recherché

txtsearch varchar

J'ai d'autres variables qui viennent se rajouter :

var_liste_table record;          (boucle sur les noms de table)
var_liste_column record;        (boucle sur les noms de champ)
var_searchtxt_ok varchar [];   (variable qui me récupère la liste des table - colonne qui contiennet le caractère recherché)
var_requete varchar;              (Récupère la requête à exécuter)

Je fais donc mes boucles sur les tables :

FOR var_liste_table IN SELECT tablename FROM pg_tables  LOOP

Puis sur les colonnes :
   

FOR var_liste_column IN SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=var_liste_table.tablename LOOP

Et je lance ma requête, et c'est là ou je bloque (forcément c'est la partie la moins facile). Je crée ma requête :

var_requete=
'SELECT array_cat(coalesce(var_searchtxt_ok,''{}''),(SELECT array_agg('''||var_liste_table.tablename::varchar||' - '||var_liste_column.COLUMN_NAME::varchar||''') FROM '::varchar||var_liste_table.tablename ||' 
WHERE '||var_liste_column.COLUMN_NAME::varchar||' like '''::varchar||txtsearch||'%'' OR '::varchar||var_liste_column.COLUMN_NAME::varchar||' LIKE ''%'||txtsearch||'%'' OR 
'||var_liste_column.COLUMN_NAME::varchar||' LIKE ''%'||txtsearch||''')::varchar[]) INTO var_searchtxt_ok;'::varchar;

et je l'exécute :

EXECUTE requete;

Et j'ai ce message qui s'affiche :

ERROR:  column "var_searchtxt_ok" does not exist

J'ai déjà créé des fonctions utilisant cette requête mais sans avoir besoin de passer par une variable qui contient la requête et u Exécute. Le problème, c'est que si je lance directement ma requête postgres me dit :

ERROR:  column "var_liste_table.tablename" does not exist

Je suis donc bloqué à cette étape là, sans vraiment comprendre le raisonnement de postgres sur des variables pourtant déclarés qu'il semble oublier...

Je vous remercie de votre aide.

Geo-x

rjuju

Administrateur

Re : Mélange variable / colonne fonction

Bonjour,

Votre besoin n'est pas très clair, mais si vous cherchez à obtenir le nom de tous les objets dont le nom du schéma, de la table ou de la colonne contient une chaîne spécifique, vous pouvez faire beaucoup plus simple :

CREATE OR REPLACE FUNCTION search(pattern text)
RETURNS SETOF text AS $$
BEGIN
RETURN QUERY SELECT quote_ident(n.nspname) || '.' || quote_ident(c.relname) || '.' || quote_ident(a.attname)
FROM pg_namespace n
JOIN pg_class c ON c.relnamespace = n.oid
JOIN pg_attribute a ON a.attrelid = c.oid
WHERE a.attnum > 0
AND (
    n.nspname LIKE '%' || pattern || '%'
    OR
    c.relname LIKE '%' || pattern || '%'
    OR
    a.attname LIKE '%' || pattern || '%'
);
END;
$$ LANGUAGE plpgsql;

---

Julien.
https://rjuju.github.io/

Geo-x

Membre

Re : Mélange variable / colonne fonction

Bonjour Julien et merci de votre réponse.

Cependant, même si votre fonction permet d'optimiser un certain nombre de procsessus, elle ne répond pas exactement à ce que je souhaite faire.

En effet, si je crée une boucle c'est pour pouvoir rechercher une chaîne de caractère spécifique dans chaque colonne de chaque table de ma base de données.

Geo-x

Dernière modification par Geo-x (24/02/2014 10:19:11)

rjuju

Administrateur

Re : Mélange variable / colonne fonction

C'est ce que fait cette procédure stockée, sauf qu'elle recherche en plus dans les noms de schéma et de table. Il suffit de remplacer la clause WHERE par :

WHERE a.attnum > 0
AND a.attname LIKE '%' || pattern || '%'

et cela ne recherchera plus que dans les noms de colonne.

---

Julien.
https://rjuju.github.io/

Geo-x

Membre

Re : Mélange variable / colonne fonction

:-D Désolé mais en me relisant, je vois que deux lectures sont possibles, je vais donc schématisé pour être plus précis ;-)

En fait j'ai une table TABLE1 avec des colonnes COL1,COL2 qui ocntiennent des données COL1.'értech', COL1.'rtech' /  COL2.'ameil', COL2.'Proyard'
Ce que je souhaite faire c'est recenser le nom de latable et de la colonne (ce que fais très bien votre fonction) qui ont des données qui contiennent certains caractères.
Dans cet exemple si je cherche 'é' il me faut en résultat TABLE1 .COL1

Merci de votre aide.

Geo-x

Geo-x

Membre

Re : Mélange variable / colonne fonction

Je viens de finir de mettrre au poin ma reuqête qui permet d'effectuer une recherche de texte dans une base de donnée et vue que j'ai trouvé de l'aide ici, il me parait normal de vous fournir cette fonction :

CREATE OR REPLACE FUNCTION research_txt(txtsearch text)
RETURNS SETOF text AS
$BODY$

DECLARE

	var_liste_table_colonne record;
	requete text;
	
BEGIN 
	
	FOR var_liste_table_colonne IN 
		SELECT quote_ident(c.relname) AS table,quote_ident(a.attname) AS colonne
		FROM pg_namespace n 
		LEFT JOIN pg_class c ON c.relnamespace = n.oid
		LEFT JOIN pg_attribute a ON a.attrelid = c.oid
		WHERE a.attnum > 0 AND quote_ident(n.nspname)='public'
		ORDER BY quote_ident(c.relname),quote_ident(a.attname)
		
		LOOP

			requete=  'SELECT DISTINCT '''||replace(quote_ident(var_liste_table_colonne.table||' - '||quote_ident(var_liste_table_colonne.colonne)),'"','')||'''::text FROM '||quote_ident(replace(var_liste_table_colonne.table,'"',''))||' WHERE '||quote_ident(var_liste_table_colonne.colonne)||'::text like '''||txtsearch||'%'' OR '||quote_ident(var_liste_table_colonne.colonne)||'::text LIKE ''%'||txtsearch||'%'' OR '
				||quote_ident(var_liste_table_colonne.colonne)||'::text LIKE ''%'||txtsearch||''';';
				
			raise notice 'requête : %',  requete;
			
			RETURN QUERY 
			EXECUTE requete;
			
		END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION research_txt(text)
  OWNER TO postgres;

N'hésitez pas si vous voyez un moyen d'optimiser la chose, je suis preneur ;-)

Bonne journée.

Geo-x

gleu

Administrateur

Re : Mélange variable / colonne fonction

Je ne vois pas l'intérêt du quote_ident dans le WHERE et dans le ORDER BY. Je vois plutôt le côté très négatif pour les performances, vu que cela empêche l'utilisation des index pour le WHERE et l'ORDER BY.

---

Guillaume.

Geo-x

Membre

Re : Mélange variable / colonne fonction

Bonjour Gleu.

En effet merci pour ces précisions. J'en ai profité pour mettre également en place un petit compteur avec le pourcentage d'avancement sous forme de RAISE NOTICE.

CREATE OR REPLACE FUNCTION research_txt(txtsearch text)
  RETURNS SETOF text AS
$BODY$

DECLARE

	var_liste_table_colonne record;
	var_requete text;
	var_pourcentage numeric(10,2);
	var_nb_ligne numeric(10,2);
	var_compteur numeric(10,2)=0.00;
	
BEGIN 

	SELECT count(quote_ident(c.relname)||quote_ident(a.attname)) INTO var_nb_ligne
		FROM pg_namespace n 
		LEFT JOIN pg_class c ON c.relnamespace = n.oid
		LEFT JOIN pg_attribute a ON a.attrelid = c.oid
		WHERE a.attnum > 0 AND quote_ident(n.nspname)='public' AND c.relam=0 AND relkind<>'S' AND relkind<>'c' AND quote_ident(c.relname)<>'spatial_ref_sys' AND quote_ident(c.relname)<>'geography_columns' AND quote_ident(c.relname)<>'geometry_columns' 
		AND quote_ident(c.relname)<>'raster_columns' AND quote_ident(c.relname)<>'raster_overviews' AND quote_ident(a.attname)<>'ogc_fid' AND quote_ident(a.attname)<>'wkb_geometry';
	
	FOR var_liste_table_colonne IN 
		SELECT quote_ident(c.relname) AS table,quote_ident(a.attname) AS colonne
		FROM pg_namespace n 
		LEFT JOIN pg_class c ON c.relnamespace = n.oid
		LEFT JOIN pg_attribute a ON a.attrelid = c.oid
		WHERE a.attnum > 0 AND quote_ident(n.nspname)='public' AND c.relam=0 AND relkind<>'S' AND relkind<>'c' AND quote_ident(c.relname)<>'spatial_ref_sys' AND quote_ident(c.relname)<>'geography_columns' AND quote_ident(c.relname)<>'geometry_columns' 
		AND quote_ident(c.relname)<>'raster_columns' AND quote_ident(c.relname)<>'raster_overviews' AND quote_ident(a.attname)<>'ogc_fid' AND quote_ident(a.attname)<>'wkb_geometry'
		ORDER BY quote_ident(c.relname),quote_ident(a.attname)
		
		LOOP

			-- Calcul du pourcentage d'avancement de la requêtes
			var_compteur=var_compteur+1;
			var_pourcentage=(var_compteur*100)/var_nb_ligne;

			-- Sélection de toutes les lignes contenant le caracère définit
			var_requete=  'SELECT DISTINCT '''||replace(quote_ident(var_liste_table_colonne.table)||' - '||quote_ident(var_liste_table_colonne.colonne),'"','')||'''::text FROM '||quote_ident(replace(var_liste_table_colonne.table,'"',''))
				||' WHERE '||var_liste_table_colonne.colonne||'::text like '''||txtsearch||'%'' OR '||var_liste_table_colonne.colonne||'::text LIKE ''%'||txtsearch||'%'' OR '
				||var_liste_table_colonne.colonne||'::text LIKE ''%'||txtsearch||''';';

			raise notice 'Pourcentage : %',  var_pourcentage||' %';
			raise notice 'Requête : %',  var_requete;
			
			RETURN QUERY 
			EXECUTE var_requete;
			
		END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION research_txt(text)
  OWNER TO postgres;

rjuju

Administrateur

Re : Mélange variable / colonne fonction

Il manque également un c.relkind = 'r' afin de ne traiter que les tables je dirais.

---

Julien.
https://rjuju.github.io/