Les fonctions
SQL propose des fonctions dont on trouvera une description par exemple ici
Examinons en quelques unes...
Fonctions de transtypage
cast (expr as type) : fonction standard SQL qui permet de convertir un type en un autre.
Exemple :
Si x_commune est un champ de type INTEGER dans la table commune
SELECT x_commune FROM commune LIMIT 1
renvoie 478935
(noter l'utilisation de la clause LIMIT qui permet d'indiquer le nombre maximum d'enregistrements en retour.
Il est également possible d'utiliser la clause OFFSET pour décaler le nombre de lignes à obtenir
Exemple :
SELECT * FROM commune LIMIT 10 OFFSET 5
(pour renvoyer les enregistrements de 6 à 15)
SELECT cast(x_commune as real) FROM commune LIMIT 1
renvoie 478935.0
SELECT cast(x_commune as text) FROM commune LIMIT 1
renvoie '478935' c'est-à-dire une chaîne de caractère, puisqu'elle s'exprime entre guillemets simples (« '' »).
PotsgreSQL propose une notation compacte sous la forme expr::type.
Exemple :
Par exemple :
SELECT x_commune :: real FROM commune
Une opération de transtypage est parfois nécessaire pour obtenir le résultat souhaité. Prenons l'exemple de calcul d'un indicateur (ratio de deux entiers) :
Exemple :
SELECT (population/superficie) AS densite FROM commune LIMIT 10;
renvoie :
Ce résultat est inattendu !
Il est dû au fait que PostGIS, le résultat de la division de deux entiers est un entier.
Pour obtenir un résultat satisfaisant il faut au minimum convertir le numérateur ou le dénominateur en décimal :
SELECT cast(population as numeric)/superficie AS densite FROM commune LIMIT 10;
On remarquera à nouveau l'utilisation de LIMIT qui permet d'indiquer le nombre maximum d'enregistrements retournés... c'est une clause très utile pour la mise au point de requêtes sur des grosses tables ou pour récupérer juste le premier enregistrement après un tri.
Le résultat devient bien celui attendu :
Fonctions de chaînes de caractères
LENGTH : renvoie la longueur d'une chaîne
Par exemple :
SELECT length(nom_comm) FROM commune
CHR : renvoie le caractère correspondant au code ASCII (exemple CHR(184) renvoi ©)
|| : concatène deux chaînes (on obtient ce symbole en tapant ALTGr 6)
Par exemple :
SELECT nom_comm|| ' '||insee_comm FROM commune LIMIT 1;
renvoie 'SAINT-JEAN-DE-LA-MOTTE 72291'
SUBSTR : extraction d'une sous-chaîne de caractères substr(chaîne, position , longueur) ; le premier caractère a la position 1 et non pas 0.
Par exemple :
SELECT * FROM troncon_hydrographique WHERE substr(ID_BDCARTO, 1, 3) = '239';
sélectionne tout les tronçons dont l'identifiant commence par '239'
UPPER : convertit en majuscule
LOWER : convertit en minuscule
Par exemple :
SELECT lower(nom_comm) FROM commune;
renvoie les noms de communes en minuscules.
Fonctions mathématiques et numériques
SQL dispose des fonctions mathématiques classiques... notons en particulier :
POW : pour élever à une puissance quelconque ex : POW(champ, 2) pour élever au carré.
SQRT : pour obtenir la racine carrée.
ROUND : qui permet d'arrondir un résultat
Par exemple :
SELECT round(cast(population AS numeric)/superficie,2) AS densite FROM commune;
renvoie :
Attention :
sous PostGIS on écrira plus facilement la requête ci-dessous pour simplifier les emboîtements :
SELECT (round (population/superficie) :: numeric, 2) AS densite FROM commune;
le :: étant une forme compacte sous PostGIS pour réaliser le cast. Le format numérique (numeric) étant obligatoire pour la fonction round sous PostGIS.
Complément : Sites de références pour les fonctions SQL PostGIS
Les fonctions de PostgreSQL (pour la version 12 par exemple) sont décrites ici.
Nous vous conseillons vivement de parcourir une première fois ce site et d'y revenir régulièrement par la suite...