Forums PostgreSQL.fr

Geo-x

Membre

Requête de champs contenus dans une liste de tables

Bonjour @ tous,

Vous êtes prêts pour un nouveau défi que je n'ai (une fois de plus) pas su relever ?

TOP

Le but est de requêter sur toutes les tables d'une BDD, afin de connaître le type de géométrie définit pour chacune d'entre elle.

Si je visualise le résultat que je veux ça serait quelque chose du genre :

Nom_table        géométrie
table 1              POLYGON
table 2              MULTILIPOYGON
table 3              LINE

Donc pour ce faire j'ai fait :

1- Je cherche toutes les tables sur lesquelles je veux requêter :

SELECT DISTINCT table_name FROM INFORMATION_SCHEMA.COLUMNS WHERE udt_name='geometry' AND column_name='wkb_geometry'

2- Et c'est là ou je bloque, je veux pour chacune de ces tables les différents type de géométrie :

SELECT DISTINCT foo.table_name,geometrytype (foo.wkb_geometry)
FROM (SELECT DISTINCT table_name FROM INFORMATION_SCHEMA.COLUMNS WHERE udt_name='geometry' AND column_name='wkb_geometry')as foo 
GROUP BY table_name;

Donc en fait l'idée serait de faire un FROM toutes les tables IN ma liste de tables, c'est possible ça??

Merci d'avance!

gleu

Administrateur

Re : Requête de champs contenus dans une liste de tables

Non, ce n'est pas possible. Vous allez devoir passer par une variable qui récupérera le nom de la table et par un EXECUTE, le tout évidemment dans une procédure stockée.

---

Guillaume.

Geo-x

Membre

Re : Requête de champs contenus dans une liste de tables

Bonjour,

merci pour la précision.

J'avoue ne pas être un as de la fonction sous postgres SQL voici ce que j'ai pondu :

CREATE OR REPLACE FUNCTION test("$1" varchar,"$2" varchar)
  RETURNS varchar AS
$BODY$

DECLARE

tables record;
resultat record;

BEGIN


	FOR tables IN 'SELECT DISTINCT table_name FROM INFORMATION_SCHEMA.COLUMNS WHERE udt_name='geometry' AND column_name='wkb_geometry''LOOP 
		EXECUTE 'SELECT DISTINCT table_name,geometrytype(wkb_geometry)as geometrytype FROM 'tables.table_name' GROUP BY table_name' 
INTO resultat;
	END LOOP;

	$1=resultat.table_name;
	$2=resultat.geometrytype;
	
return $1,$2;
END;

mais il y a une logique que je ne maitrise pas au niveau des résultats de la fonction. On ne peut pas retourner plusieurs résultats d'une fonction, et en même temps, on ne peut pas retourner de résultat de type record. Donc comment peut-on faire pour retourner à une table donnée, un type de géométrie??

gleu

Administrateur

Re : Requête de champs contenus dans une liste de tables

On ne peut pas retourner plusieurs résultats d'une fonction

Je ne vois pas ce que vous voulez dire par ça.

on ne peut pas retourner de résultat de type record

Si, bien sûr.

Si j'essaie de comprendre ce que vous voulez faire avec votre pseudo code, ça donnerait ceci en mieux coder :

CREATE OR REPLACE FUNCTION test(OUT p_table text, OUT p_geometrytype text) RETURNS SETOF record
LANGUAGE plpgsql
AS
$BODY$
DECLARE
  tables record;
BEGIN
  FOR tables IN SELECT DISTINCT table_name FROM information_schema.columns WHERE udt_name='geometry' AND column_name='wkb_geometry'
  LOOP
    p_table := tables.table_name;
    EXECUTE 'SELECT geometrytype(wkb_geometry) FROM ' || quote_ident(tables.table_name)
      INTO p_geometrytype;
    RETURN NEXT;
  END LOOP;
END
$BODY$;

Un petit

SELECT * FROM test();

devrait donner quelque chose.

---

Guillaume.

Geo-x

Membre

Re : Requête de champs contenus dans une liste de tables

Ben alors là...c'est exactement ça!
Merci beaucoup.

Par

On ne peut pas retourner plusieurs résultats d'une fonction

C'est le fait que je voulais retourner de ma fonction deux colonnes distinctes avec deux données différentes. Je pensais que les fonctions ne permettaient de retourner un seul résultat, comme par exemple une fonction sur les caractères retourne le mot en entrée avec les espaces supprimés (je ne sais pas si je suis très clair là...)

Par rapport au "pseudo code" (j'ai bien aimé votre définition) que vous avez transformé en "code" digne de ce nom : j'aurais quelques question pour être sûr de bien comprendre :

1-

(OUT p_table text, OUT p_geometrytype text)

Je n'avais encore jamais vu ce OUT, généralement il y a simplement écrit le type de champ. Donc si je comprend bien, quand en entrée de fonction j'ai besoin d'un champ de type integer et un champ de type text et qu'en retour j'aurais un résultat en varchar vais mettre :

FUNCTION test (integer, text)
DECLARE $1 varchar;
(...)
RETURN $1

2- je ne connaissais pas la fonction quote_ident merci pour l'info

3- Si je comprend bien on met le RETURN NEXT lorsqu'il y a une série d'enregistrement c'est bien cela?

4- Dans quel cas met on RETURNS et dans quel cas met on RETURN ? c'est selon qu'il y ait un ou plusieurs retour j'imagine?

Merci pour ces compléments d’informations qui me permettront d'approfondir mes connaissances.

gleu

Administrateur

Re : Requête de champs contenus dans une liste de tables

1. Non. Il manque la clause RETURNS, et $1 n'est pas un bon nom de variable.
3. Oui, c'est bien cela.
4. RETURNS indique le type de donnée renvoyée, il est indiqué en dehors du code source de la fonction. RETURN permet de renvoyer une valeur, il est dans le code source de la fonction et peut être utilisé une ou plusieurs fois (s'il est utilisé plusieurs fois, la déclaration du type de données en retour doit contenir la clause SETOF).

---

Guillaume.

Geo-x

Membre

Re : Requête de champs contenus dans une liste de tables

Merci beaucoup pour ces précisions Gleu ;-)