Hello
Écrire une fonction hello() qui renvoie la chaîne de
caractère « Hello World! » en SQL.
CREATE OR REPLACE FUNCTION hello()AS $BODY$SELECT 'hello world !' ::text ;
Écrire une fonction hello_pl() qui renvoie la chaîne de
caractère « Hello World! » en PL/pgSQL.
CREATE OR REPLACE FUNCTION hello_pl()AS $BODY$BEGIN RETURN 'hello world !' ;END
Comparer les coûts des deux plans d’exécutions de ces requêtes.
Expliquer ces coûts.
Requêtage :
EXPLAIN SELECT hello_pl();Par défaut, si on ne précise pas le coût (COST) d’une
fonction, cette dernière a un coût par défaut de 100. Ce coût est à
multiplier par la valeur du paramètre cpu_operator_cost,
par défaut à 0,0025. Le coût total d’appel de la fonction
hello_pl est donc par défaut de :
100*cpu_operator_cost + cpu_tuple_cost
Ce n’est pas valable pour la fonction en SQL pur, qui est ici
intégrée à la requête.
Division
Écrire en PL/pgSQL une fonction de division appelée
division. Elle acceptera en entrée deux arguments de type
entier et renverra un nombre réel (numeric).
Attention, sous PostgreSQL, la division de deux entiers est par
défaut entière : il faut donc transtyper.
CREATE OR REPLACE FUNCTION division (arg1 integer , arg2 integer )numeric AS $BODY$BEGIN RETURN arg1::numeric / arg2::numeric ;END
Écrire cette même fonction en SQL.
CREATE OR REPLACE FUNCTION division_sql (a integer , b integer )numeric AS $$SELECT a::numeric / b::numeric ;
Comment corriger le problème de la division par zéro ? Écrire cette
nouvelle fonction dans les deux langages. (Conseil : dans ce genre de
calcul impossible, il est possible d’utiliser la constante
NaN (Not A Number ) ).
Le problème se présente ainsi :
Pour la version en PL :
CREATE OR REPLACE FUNCTION division(arg1 integer , arg2 integer )numeric AS $BODY$BEGIN IF arg2 = 0 THEN RETURN 'NaN' ;ELSE RETURN arg1::numeric / arg2::numeric ;END IF ;END $BODY$Pour la version en SQL :
CREATE OR REPLACE FUNCTION division_sql(a integer , b integer )numeric AS $$SELECT CASE $2 WHEN 0 THEN 'NaN' ELSE $1 ::numeric / $2 ::numeric END ;SELECT sur des
tables dans les fonctions
Ce TP utilise les tables de la base
employes_services . Le script de création se télécharge
et s’installe ainsi dans une nouvelle base
employes :
curl -kL https://dali.bo/tp_employes_services -o employes_services.sqlcreatedb employespsql employes < employes_services.sqlLes quelques tables occupent environ 80 Mo sur le disque.
Créer une fonction qui ramène le nombre d’employés embauchés une
année donnée (à partir du champ
employes.date_embauche).
CREATE OR REPLACE FUNCTION nb_embauches (v_annee integer )integer AS $BODY$DECLARE integer ;BEGIN SELECT count (* )INTO nbFROM employesWHERE extract (year from date_embauche) = v_annee ;RETURN nb;END Test :
SELECT nb_embauches (2006 );
Utiliser la fonction generate_series() pour lister le
nombre d’embauches pour chaque année entre 2000 et 2010.
SELECT n, nb_embauches (n)FROM generate_series (2000 ,2010 ) nORDER BY n;
Créer une fonction qui fait la même chose avec deux années en
paramètres une boucle FOR … LOOP,
RETURNS TABLE et RETURN NEXT.
CREATE OR REPLACE FUNCTION nb_embauches (v_anneedeb int , v_anneefin int )TABLE (annee int , nombre_embauches int )AS $BODY$BEGIN FOR i in v_anneedeb.. v_anneefinLOOP SELECT i, nb_embauches (i)INTO annee, nombre_embauches ;RETURN NEXT ;END LOOP ;RETURN ;END Le nom de la fonction a été choisi identique à la précédente, mais
avec des paramètres différents. Cela ne gêne pas le requêtage :
SELECT * FROM nb_embauches (2006 ,2010 );Multiplication
Écrire une fonction de multiplication dont les arguments sont des
chiffres en toute lettre, inférieurs ou égaux à « neuf ». Par exemple,
multiplication ('deux','trois') doit renvoyer 6.
CREATE OR REPLACE FUNCTION multiplication (arg1 text, arg2 text)integer AS $BODY$DECLARE integer ;integer ;BEGIN IF arg1 = 'zéro' THEN := 0 ;= 'un' THEN := 1 ;= 'deux' THEN := 2 ;= 'trois' THEN := 3 ;= 'quatre' THEN := 4 ;= 'cinq' THEN := 5 ;= 'six' THEN := 6 ;= 'sept' THEN := 7 ;= 'huit' THEN := 8 ;= 'neuf' THEN := 9 ;END IF ;IF arg2 = 'zéro' THEN := 0 ;= 'un' THEN := 1 ;= 'deux' THEN := 2 ;= 'trois' THEN := 3 ;= 'quatre' THEN := 4 ;= 'cinq' THEN := 5 ;= 'six' THEN := 6 ;= 'sept' THEN := 7 ;= 'huit' THEN := 8 ;= 'neuf' THEN := 9 ;END IF ;RETURN a1* a2;END Test :
SELECT multiplication('deux' , 'trois' );SELECT multiplication('deux' , 'quatre' );
Si ce n’est déjà fait, faire en sorte que multiplication
appelle une autre fonction pour faire la conversion de texte en chiffre,
et n’effectue que le calcul.
CREATE OR REPLACE FUNCTION texte_vers_entier(arg text)integer AS $BODY$DECLARE integer ;BEGIN IF arg = 'zéro' THEN := 0 ;= 'un' THEN := 1 ;= 'deux' THEN := 2 ;= 'trois' THEN := 3 ;= 'quatre' THEN := 4 ;= 'cinq' THEN := 5 ;= 'six' THEN := 6 ;= 'sept' THEN := 7 ;= 'huit' THEN := 8 ;= 'neuf' THEN := 9 ;END IF ;RETURN ret;END CREATE OR REPLACE FUNCTION multiplication(arg1 text, arg2 text)integer AS $BODY$DECLARE integer ;integer ;BEGIN := texte_vers_entier(arg1);:= texte_vers_entier(arg2);RETURN a1* a2;END
Essayer de multiplier « deux » par 4. Qu’obtient-on et pourquoi ?
SELECT multiplication('deux' , 4 ::text );Par défaut, les variables internes à la fonction valent NULL. Rien
n’est prévu pour affecter le second argument, on obtient donc NULL en
résultat.
Corriger la fonction pour tomber en erreur si un argument est
numérique (utiliser RAISE EXCEPTION <message>).
CREATE OR REPLACE FUNCTION texte_vers_entier(arg text)integer AS $BODY$DECLARE integer ;BEGIN IF arg = 'zéro' THEN := 0 ;= 'un' THEN := 1 ;= 'deux' THEN := 2 ;= 'trois' THEN := 3 ;= 'quatre' THEN := 4 ;= 'cinq' THEN := 5 ;= 'six' THEN := 6 ;= 'sept' THEN := 7 ;= 'huit' THEN := 8 ;= 'neuf' THEN := 9 ;ELSE EXCEPTION 'argument "%" invalide' , arg;:= NULL ;END IF ;RETURN ret;END SELECT multiplication('deux' , 4 ::text );Salutations
Écrire une fonction en PL/pgSQL qui prend en argument le nom de
l’utilisateur, puis lui dit « Bonjour » ou « Bonsoir » suivant l’heure
de la journée. Utiliser la fonction to_char().
CREATE OR REPLACE FUNCTION salutation(utilisateur text)AS $BODY$DECLARE integer ;BEGIN := to_char (now(), 'HH24' );IF heure > 12 THEN := 'Bonsoir' ;ELSE := 'Bonjour' ;END IF ;RETURN libelle|| ' ' || utilisateur|| ' !' ;END Test :
SELECT salutation ('Guillaume' );
Écrire la même fonction avec un paramètre OUT.
CREATE OR REPLACE FUNCTION salutation(IN utilisateur text, OUT message text)AS $BODY$DECLARE integer ;BEGIN := to_char (now(), 'HH24' );IF heure > 12 THEN := 'Bonsoir' ;ELSE := 'Bonjour' ;END IF ;:= libelle|| ' ' || utilisateur|| ' !' ;END Elle s’utilise de la même manière :
SELECT salutation ('Guillaume' );
Pour calculer l’heure courante, utiliser plutôt la fonction
extract.
CREATE OR REPLACE FUNCTION salutation(IN utilisateur text, OUT message text)AS $BODY$DECLARE integer ;BEGIN SELECT INTO heure extract (hour from now())::int ;IF heure > 12 THEN := 'Bonsoir' ;ELSE := 'Bonjour' ;END IF ;:= libelle|| ' ' || utilisateur|| ' !' ;END
Réécrire la fonction en SQL.
Le CASE … WHEN remplace aisément un
IF … THEN :
CREATE OR REPLACE FUNCTION salutation_sql(nom text)AS $$SELECT CASE extract (hour from now()) > 12 WHEN 't' THEN 'Bonsoir ' || nomELSE 'Bonjour ' || nomEND ::text ;Inversion de chaîne
Écrire une fonction inverser qui inverse une chaîne
(pour « toto » en entrée, afficher « otot » en sortie), à l’aide d’une
boucle WHILE et des fonctions char_length et
substring.
CREATE OR REPLACE FUNCTION inverser(str_in varchar )varchar AS $$DECLARE varchar ; -- à renvoyer integer ;BEGIN -- Initialisation de str_out, sinon sa valeur reste à NULL := '' ;-- Position initialisée ç la longueur de la chaîne := char_length(str_in);-- La chaîne est traitée ç l'envers -- Boucle: Inverse l'ordre des caractères d'une chaîne de caractères WHILE position > 0 LOOP -- la chaîne donnée en argument est parcourue -- à l'envers, -- et les caractères sont extraits individuellement := str_out || substring(str_in, position, 1 );:= position - 1 ;END LOOP ;RETURN str_out;END ;SELECT inverser (' toto ' ) ;Jours fériés
La fonction suivante calcule la date de Pâques d’une année :
CREATE OR REPLACE FUNCTION paques (annee integer )date AS $$DECLARE integer ;integer ;date ;BEGIN := (19 * (annee % 19 ) + 24 ) % 30 ;:= (2 * (annee % 4 ) + 4 * (annee % 7 ) + 6 * a + 5 ) % 7 ;SELECT (annee::text || '-03-31' )::date + (a+ b- 9 ) INTO r ;RETURN r ;END ;
Afficher les dates de Pâques de 2018 à 2025.
SELECT paques (n) FROM generate_series (2018 , 2025 ) n ;
Écrire une fonction qui calcule la date de l’Ascension, soit le jeudi
de la sixième semaine après Pâques. Pour simplifier, on peut aussi
considérer que l’Ascension se déroule 39 jours après Pâques.
Version complexe :
CREATE OR REPLACE FUNCTION ascension(annee integer )date AS $$DECLARE date ;BEGIN SELECT paques(annee)::date + 40 INTO r;SELECT r + (4 - extract (dow from r))::integer INTO r;RETURN r;END ;Version simple :
CREATE OR REPLACE FUNCTION ascension(annee integer )date AS $$SELECT (paques (annee) + INTERVAL '39 days' )::date ;Test :
SELECT paques (n), ascension(n) FROM generate_series (2018 , 2025 ) n ;
Pour écrire une fonction qui renvoie tous les jours fériés d’une
année (libellé et date), en France métropolitaine :
Prévoir un paramètre supplémentaire pour l’Alsace-Moselle, où le
Vendredi saint (précédant le dimanche de Pâques) et le 26 décembre sont
aussi fériés (ou toute autre variation régionale).
Cette fonction doit renvoyer plusieurs lignes : utiliser
RETURN NEXT.
Plusieurs variantes sont possibles : avec SETOF record,
avec des paramètres OUT, ou avec
RETURNS TABLE (libelle, jour).
Enfin, il est possible d’utiliser RETURN QUERY.
Version avec SETOF record :
CREATE OR REPLACE FUNCTION vacances (integer ,boolean DEFAULT false record AS $$DECLARE integer ;record ;BEGIN SELECT 'Jour de l '' an' ::text , (annee::text || '-01-01' )::date INTO r;RETURN NEXT r;SELECT 'Pâques' ::text , paques(annee)::date + 1 INTO r;RETURN NEXT r;SELECT 'Ascension' ::text , ascension(annee)::date INTO r;RETURN NEXT r;SELECT 'Fête du travail' ::text , (annee::text || '-05-01' )::date INTO r;RETURN NEXT r;SELECT 'Victoire 1945' ::text , (annee::text || '-05-08' )::date INTO r;RETURN NEXT r;SELECT 'Fête nationale' ::text , (annee::text || '-07-14' )::date INTO r;RETURN NEXT r;SELECT 'Assomption' ::text , (annee::text || '-08-15' )::date INTO r;RETURN NEXT r;SELECT 'La toussaint' ::text , (annee::text || '-11-01' )::date INTO r;RETURN NEXT r;SELECT 'Armistice 1918' ::text , (annee::text || '-11-11' )::date INTO r;RETURN NEXT r;SELECT 'Noël' ::text , (annee::text || '-12-25' )::date INTO r;RETURN NEXT r;IF alsace_moselle THEN SELECT 'Vendredi saint' ::text , paques(annee)::date - 2 INTO r;RETURN NEXT r;SELECT 'Lendemain de Noël' ::text , (annee::text || '-12-26' )::date INTO r;RETURN NEXT r;END IF ;RETURN ;END ;Le requêtage implique de nommer les colonnes :
SELECT * FROM vacances(2020 , true ) AS (libelle text, jour date )ORDER BY jour ;Version avec paramètres OUT :
Une autre forme d’écriture possible consiste à indiquer les deux
colonnes de retour comme des paramètres OUT :
CREATE OR REPLACE FUNCTION vacances(integer ,boolean DEFAULT false ,OUT libelle text,OUT jour date )record AS $function$DECLARE integer ;record ;BEGIN SELECT 'Jour de l '' an' ::text , (annee::text || '-01-01' )::date INTO libelle, jour;RETURN NEXT ;SELECT 'Pâques' ::text , paques(annee)::date + 1 INTO libelle, jour;RETURN NEXT ;SELECT 'Ascension' ::text , ascension(annee)::date INTO libelle, jour;RETURN NEXT ;SELECT 'Fête du travail' ::text , (annee::text || '-05-01' )::date INTO libelle, jour;RETURN NEXT ;SELECT 'Victoire 1945' ::text , (annee::text || '-05-08' )::date INTO libelle, jour;RETURN NEXT ;SELECT 'Fête nationale' ::text , (annee::text || '-07-14' )::date INTO libelle, jour;RETURN NEXT ;SELECT 'Assomption' ::text , (annee::text || '-08-15' )::date INTO libelle, jour;RETURN NEXT ;SELECT 'La toussaint' ::text , (annee::text || '-11-01' )::date INTO libelle, jour;RETURN NEXT ;SELECT 'Armistice 1918' ::text , (annee::text || '-11-11' )::date INTO libelle, jour;RETURN NEXT ;SELECT 'Noël' ::text , (annee::text || '-12-25' )::date INTO libelle, jour;RETURN NEXT ;IF alsace_moselle THEN SELECT 'Vendredi saint' ::text , paques(annee)::date - 2 INTO libelle, jour;RETURN NEXT ;SELECT 'Lendemain de Noël' ::text , (annee::text || '-12-26' )::date INTO libelle, jour;RETURN NEXT ;END IF ;RETURN ;END ;La fonction s’utilise alors de façon simple :
SELECT * FROM vacances(2020 )ORDER BY jour ;Version avec RETURNS TABLE :
Seule la déclaration en début diffère de la version avec les
paramètres OUT :
CREATE OR REPLACE FUNCTION vacances(integer ,alsace_moselle boolean DEFAULT false )TABLE (libelle text, jour date )AS $function$L’utilisation est aussi simple que la version précédente.
Version avec RETURN QUERY :
C’est peut-être la version la plus compacte :
CREATE OR REPLACE FUNCTION vacances(annee integer ,alsace_moselle boolean DEFAULT false )TABLE (libelle text, jour date )AS $function$BEGIN RETURN QUERY SELECT 'Jour de l '' an' ::text , (annee::text || '-01-01' )::date ;RETURN QUERY SELECT 'Pâques' ::text , paques(annee)::date + 1 ;RETURN QUERY SELECT 'Ascension' ::text , ascension(annee)::date ;RETURN QUERY SELECT 'Fête du travail' ::text , (annee::text || '-05-01' )::date ;RETURN QUERY SELECT 'Victoire 1945' ::text , (annee::text || '-05-08' )::date ;RETURN QUERY SELECT 'Fête nationale' ::text , (annee::text || '-07-14' )::date ;RETURN QUERY SELECT 'Assomption' ::text , (annee::text || '-08-15' )::date ;RETURN QUERY SELECT 'La toussaint' ::text , (annee::text || '-11-01' )::date ;RETURN QUERY SELECT 'Armistice 1918' ::text , (annee::text || '-11-11' )::date ;RETURN QUERY SELECT 'Noël' ::text , (annee::text || '-12-25' )::date ;IF alsace_moselle THEN RETURN QUERY SELECT 'Vendredi saint' ::text , paques(annee)::date - 2 ;RETURN QUERY SELECT 'Lendemain de Noël' ::text , (annee::text || '-12-26' )::date ;END IF ;RETURN ;END ;Index fonctionnel
Ce TP utilise la base magasin . La base
magasin (dump de 96 Mo, pour 667 Mo sur le disque au
final) peut être téléchargée et restaurée comme suit dans une nouvelle
base magasin :
createdb magasincurl -kL https://dali.bo/tp_magasin -o /tmp/magasin.dumppg_restore -d magasin /tmp/magasin.dump# le message sur public préexistant est normal rm -- /tmp/magasin.dumpToutes les données sont dans deux schémas nommés
magasin et facturation .
Écrire une requête permettant de renvoyer l’ensemble des produits
(table magasin.produits) dont le volume ne dépasse pas 1
litre (les unités de longueur sont en mm, 1 litre = 1 000 000 mm³).
Concernant le volume des produits, la requête est assez simple :
SELECT * FROM produits WHERE longueur * hauteur * largeur < 1000000 ;
Quel index permet d’optimiser cette requête ? (Utiliser une fonction
est possible, mais pas obligatoire.)
L’option la plus simple est de créer l’index de cette façon, sans
avoir besoin d’une fonction :
CREATE INDEX ON produits((longueur * hauteur * largeur));En général, il est plus propre de créer une fonction. On peut passer
la ligne entière en paramètre pour éviter de fournir 3 paramètres. Il
faut que cette fonction soit IMMUTABLE pour être
indexable :
CREATE OR REPLACE function volume (p produits)numeric AS $$SELECT p.longueur * p.hauteur * p.largeur;PARALLEL SAFE(Elle est même PARALLEL SAFE pour la même raison qu’elle
est IMMUTABLE : elle dépend uniquement des données de la
table.)
On peut ensuite indexer le résultat de cette fonction :
CREATE INDEX ON produits (volume(produits)) ;Il est ensuite possible d’écrire la requête de plusieurs manières, la
fonction étant ici écrite en SQL et non en PL/pgSQL ou autre langage
procédural :
SELECT * FROM produits WHERE longueur * hauteur * largeur < 1000000 ;SELECT * FROM produits WHERE volume(produits) < 1000000 ;En effet, l’optimiseur est capable de « regarder » à l’intérieur de
la fonction SQL pour déterminer que les clauses sont les mêmes, ce qui
n’est pas vrai pour les autres langages.
En revanche, la requête suivante, où la multiplication est faite dans
un ordre différent, n’utilise pas l’index :
SELECT * FROM produits WHERE largeur * longueur * hauteur < 1000000 ;et c’est notamment pour cette raison qu’il est plus propre d’utiliser
la fonction.
De part l’origine « relationnel-objet » de PostgreSQL, on peut même
écrire la requête de la manière suivante :
SELECT * FROM produits WHERE produits.volume < 1000000 ;
