Fonctions SQL et Vues

Fonctions SQL. Vues

Équipe BD

LPSM Université Paris-Cité

2024-10-18

Requêtes paramétrées : pourquoi ?

SQL is much like chess—a few hours to learn, a lifetime to master

Pourquoi ?

Lorsqu’une collection de requêtes de même forme est utilisée sur une base de données, il est pertinent de préparer ces requêtes

Les objectifs de cette préparation sont divers:

  • éviter de coder de façon répétitive

  • permettre une planification en amont des requêtes

  • éviter de recalculer des jointures coûteuses

  • …

Comment ?

Les SGBD relationnels offrent une gamme d’outils pour combler ces besoins

  • Les PREPARED STATEMENT

  • Les fonctions

  • Les vues

Fonctions SQL (version PostGreSQL): définition

Dans SQL et dans PostgreSQL en particulier , il est possible de définir des fonctions ou procédures persistantes (STORED PROCEDURE)

Ces fonctions sont des objets permanents d’un schéma (comme les tables, vues, utilisateurs, etc)

Elles permettent de regrouper un traitement complexe en un seul programme que l’on peut appeler à tout moment si on en a le droit

Syntaxe générale des fonctions

CREATE OR REPLACE FUNCTION 
  func_name(arg1 arg1_datatype DEFAULT arg1_default)

RETURNS some type | set of some type | TABLE (..) AS
$$

BODY of function

$$
LANGUAGE language_of_function

langage_of_function peut prendre différentes valeurs :

  • SQL (Trusted)
  • PL/pgSQL (Trusted)
  • C
  • PL/Python (Untrusted)
  • …
SELECT lanname 
FROM pg_language;
lanname |
--------+
internal|
c       |
sql     |
plpgsql |

Signature de la fonction

CREATE OR REPLACE FUNCTION 
  <func_name>(<arg1> <arg1_datatype> [DEFAULT <arg1_default>])

RETURNS <some type> | SETOF <some type> | TABLE (..)

La signature d’une fonction est formée par:

  • le nom de la fonction <func_name>
  • la liste des arguments (nom <arg1> et type attendu )
  • le type du résultat retourné par la fonction

Signature de la fonction (suite)

  • <arg1_datatype>:

    • type prédéfini ou non, voir CREATE TYPE ...
    • type de colonne d’une table : country.countrycode%TYPE
    • type des tuples d’une table : country%ROWTYPE…

  • <arg1_default>: litéral ou expression

  • Type du résultat:

    • type prédéfini ou non
    • SETOF ... (table)
    • TABLE (...)

Les arguments peuvent avoir des valeurs par défaut. Cela permet à l’appelant d’ommettre ces arguments.

Les arguments optionnels doivent être déclarés après les arguments non-optionnels dans la signature de la fonction.

Types définis par l’usager

CREATE TYPE  type_utilisateur AS (
  <identifiant> <type défini>
  [, <identifiant> <type défini>]*
);

Usage pour une création de table

CREATE TABLE <nom de table> OF 
  type_utilisateur
  (CONSTRAINT <nom de contrainte> PRIMARY KEY  (<nom de colonne>));

Pratique pour créer des tables de même schéma

Note

quand on crée une table, PostgreSQL crée automatiquement un type associé (bis)

Exemple

  • Définition d’un type
CREATE TYPE basic_user AS (
  user_name varchar(50), 
  pwd varchar(10)
);
  • Usage du type défini par usager
CREATE TABLE super_users OF basic_user 
  (CONSTRAINT pk_su PRIMARY KEY  (user_name));

Annotations

Les définitions de fonctions comportent parfois des qualifications supplémentaires

  • VOLATILITY: IMMUTABLE, STABLE, VOLATILE (default)

  • SECURITY : SECURITY DEFINER, SECURITY INVOKER

Les fonctions écrites en langage SQL

SQL est d’abord un langage permettant d’émettre des requêtes, il peut aussi être utilisé pour écrire des fonctions

Dans PostgreSQL, l’utilisation d’un morceau de SQL existant est facile :

  • prenez vos instructions SQL existantes (éventuellement plusieurs)

  • ajoutez un en-tête et une conclusion fonctionnels

et … vous avez terminé

Les fonctions écrites en langage SQL (suite)

Mais cette facilité a un prix

Vous ne pouvez pas :

  • utiliser des structures de contrôle (boucles, alternatives) ou des définitions de variables locales

  • exécuter des instructions SQL dynamiques que vous assemblez à la volée en utilisant les arguments passés dans la fonction

Mais, tout de même

  • le planificateur de requêtes peut examiner une fonction SQL et en optimiser l’exécution

Les fonstions SQL exécutent une suite arbitraire d’expressions (requêtes) SQL, elles renvoient le résultat de la dernièrere requête.

In the simple (non-set) case, the first row of the last query’s result will be returned. Bear in mind that “the first row” of a multirow result is not well-defined unless you use ORDER BY. If the last query happens to return no rows at all, the null value will be returned.

Corps de la fonction

La syntaxe du corps de la fonction dépend du language_of_function

Dans le cas où language_of_function est SQL

$$

BODY of function

$$

Références aux argument

On peut faire références aux arguments en les nommant dans le corps de la fonction

Si les arguments ne sont pas nommés, vous faites référence aux arguments par leur position dans la signature : $1, $2, $3, etc

Si vous nommez les arguments, vous pouvez utiliser la notation

big_elephant(name => 'Wooly', ear_size => 1.2)

lors des appels à la fonction

Exemple

CREATE OR REPLACE FUNCTION 
  write_to_log(param_user_name varchar,
               param_description text)
RETURNS integer AS
$$
INSERT INTO logs
   (user_name, description) 
   VALUES($1, $2)
RETURNING log_id;
$$
LANGUAGE 'sql' VOLATILE;

Invocation

SELECT write_to_log('alex', 
    'Logged in at 11:59 AM.') AS new_id;

Les fonctions SQL peuvent retourner des ensembles

Trois manières de faire:

  • ANSI SQL standard: RETURNS TABLE

  • spécifier des paramètres OUT et retourner SETOF RECORD

  • types de données composés SETOF ...

Exemple de fonction retournant un type simple

Dans le schéma world, on veut écrire une fonction qui prend en argument une région et renvoie la population maximale parmi les capitales de la région

CREATE OR REPLACE 
  FUNCTION username.taille_max_capitale_region(p_region text)
  RETURNS INTEGER 
  LANGUAGE SQL AS
$$
SELECT MAX(population_city) AS max_pop
FROM world.country c 
  JOIN world.city cc ON (c.capital=cc.id)
WHERE c.region=p_region AND
 cc.population_city IS NOT NULL;
$$ ;

Fonctions qui retournent un type composé

Dans le schéma world, on veut écrire une fonction qui prend en argument une région et renvoie le nom et la population de la capitale la plus peuplée de cette région

On peut utiliser le qualifiant OUT pour désigner des paramètres de sortie.

CREATE OR REPLACE 
  FUNCTION username.capitale(
    p_region text, 
    OUT o_name_capital TEXT, 
    OUT o_population_capital INTEGER)
  RETURNS RECORD  
  LANGUAGE SQL AS
$$
WITH r AS (
  SELECT cc.*, RANK() OVER (PARTITION BY c.region ORDER BY cc.population_city DESC) AS rnk
  FROM world.country c JOIN 
    world.city cc ON (c.capital=cc.id)
  WHERE c.region=p_region
)

SELECT r.name_city,  r.population_city
FROM r 
WHERE r.rnk = 1 ;
$$ ;

Fonctions qui retournent un type composé défini par les lignes d’une table

Dans le schéma world, on veut écrire une fonction qui prend en argument une région et renvoie la description de la capitale la plus peuplée de cette région

On se contente de nommer la table pour indiquer que le résultat doit avoir même structure qu’une ligne de la table

  CREATE OR REPLACE FUNCTION username.capitale(p_region text)
  RETURNS world.city  
  LANGUAGE SQL AS
  $$
  WITH r AS(
    SELECT cc.*, RANK() OVER (PARTITION BY c.region ORDER BY cc.population_city DESC) AS rnk
    FROM world.country c JOIN world.city cc ON (c.capital=cc.id)
    WHERE c.region=p_region)
  SELECT r.id, r.name_city, r.countrycode, r.district, r.population_city
  FROM r 
  WHERE r.rnk = 1 ;
  $$ ;

Fonctions qui retournent une table

Dans le schéma world, on veut écrire une fonction qui prend en argument une région et renvoie la table des capitales de la région

Table de schéma explicite

On se contente d’abord de renvoyer le nom de la capitale.

On explicite le schéma de la table résultat

CREATE OR REPLACE FUNCTION username.capitales_region(p_region text)
RETURNS TABLE (name_capital text) 
LANGUAGE SQL AS
$$
SELECT cc.name_city 
FROM world.country c JOIN world.city cc ON (c.capital=cc.id)
WHERE c.region=p_region ;
$$ ;

Table de même schéma qu’une autre table

https://www.postgresql.org/docs/15/xfunc-sql.html

On veut maintenant récupérer une table de même schéma que city.

La solution est très simple.

On profite de ce qu’à chaque table correspond un type de même nom et on utilise le mot-clé SETOF.

CREATE OR REPLACE FUNCTION username.capitales_region_large(p_region text)
RETURNS SETOF world.city  
LANGUAGE SQL AS
$$
SELECT cc.* 
FROM world.country c JOIN world.city cc ON (c.capital=cc.id)
WHERE c.region=p_region ;
$$ ;

Autres langages

  • PL/pgSQL

  • C

  • PL/Python

  • PL/R

  • …

Fonctions SQL: usages

Fonction reprise depuis pagila

CREATE OR REPLACE FUNCTION user.inventory_held_by_customer(
    p_inventory_id integer,
    OUT customer_id int2) AS 
$$
  SELECT customer_id 
  FROM pagila.rental
  WHERE return_date IS NULL AND inventory_id = p_inventory_id ;
$$ LANGUAGE 'sql' ;

Fonctions de pagila

CREATE OR REPLACE FUNCTION uname.film_in_stock(
  p_film_id integer, 
  p_store_id integer, 
  OUT p_film_count integer)
 RETURNS SETOF integer
AS $$
     SELECT inventory_id
     FROM pagila.inventory
     WHERE film_id = $1
     AND store_id = $2
     AND pagila.inventory_in_stock(inventory_id);
$$ LANGUAGE sql ;

Invocation

SELECT * 
FROM pagila.film_in_stock(12, 1) ;
p_film_count
60
61
62

Vues : pourquoi ?

Presque tous les SGBD relationnels proposent des vues comme niveau d’abstraction des tables. Dans une vue, vous pouvez interroger plusieurs tables et présenter des colonnes dérivées supplémentaires basées sur des calculs complexes.

Les vues sont généralement en lecture seule, mais PostgreSQL vous permet de mettre à jour les données sous-jacentes en mettant à jour la vue, à condition que la vue provienne d’une seule table.

Pour mettre à jour des données à partir de vues qui joignent plusieurs tables, vous devez créer une gachette contre la vue.

Les vues matérialisées mettent en cache les données afin d’accélérer les requêtes les plus courantes, au détriment des données les plus récentes.

Motivation

Astuce

Si vous vous retrouvez à écrire tous les jours la même requête, créer une vue !

Une vue n’est rien d’autre qu’une requête stockée de façon persistante

Vues sur une seule table

CREATE OR REPLACE VIEW co_asia AS
SELECT * 
FROM world.country 
WHERE continent = 'Asia' ;

Interroger une vue

Une vue s’interroge comme une table :

SELECT region, sum(population_country) AS pop 
FROM co_asia 
GROUP BY region 
ORDER BY pop DESC ;
region                   |pop       |
-------------------------+----------+
Eastern Asia             |1507328000|
Southern and Central Asia|1490776000|
Southeast Asia           | 518541000|
Middle East              | 188380700|

Vues et manipulation de données

Si

  • la vue (VIEW) provient d’une table unique
  • la vue contient la clé primaire de la table d’origine

Alors

on peut modifier la table au travers de la vue. On peut

  • insérer
  • supprimer
  • mettre à jour

Exemple

  • La vue co_asia provient d’une unique table : world.country
  • La vue co_asia contient la clé primaire countrycode de la table world.country

Cette instruction :

UPDATE co_asia
  SET continent='Antarctica';

… est légale !

–

Mais, après exécution :

  • la vue co_asia est vide

  • les pays d’Asie de country sont délocalisés en Antarctique

Pour éviter ca : WITH CHECK OPTION

CREATE OR REPLACE VIEW boucheron.co_asia AS
  SELECT * 
  FROM world.country 
  WHERE continent = 'Asia' 

WITH CHECK OPTION ;

Ajouter cette modification lors de la création de la vue et PostgreSQL s’opposera à une tentative d’insertion hors de la vue ou à une tentative de mise à jour qui placerait des tuples hors de la vue

CREATE OR REPLACE VIEW pagila.actor_info
AS SELECT a.actor_id,
    a.first_name,
    a.last_name,
    pagila.group_concat(DISTINCT (c.name::text || ': '::text) || 
        (( SELECT pagila.group_concat(f.title::text) AS group_concat
           FROM pagila.film f
             JOIN pagila.film_category fc_1 ON f.film_id = fc_1.film_id
             JOIN pagila.film_actor fa_1 ON f.film_id = fa_1.film_id
          WHERE fc_1.category_id = c.category_id AND fa_1.actor_id = a.actor_id
          GROUP BY fa_1.actor_id))
        ) AS film_info
   FROM pagila.actor a
     LEFT JOIN pagila.film_actor fa ON a.actor_id = fa.actor_id
     LEFT JOIN pagila.film_category fc ON fa.film_id = fc.film_id
     LEFT JOIN pagila.category c ON fc.category_id = c.category_id
  GROUP BY a.actor_id, a.first_name, a.last_name;

Vues multi-tables : exemple de vue sur world

CREATE OR REPLACE VIEW country_info AS

  SELECT
    cn.countrycode, 
    cn.name_country, 
    cn.region,
    string_agg(DISTINCT ci.name_city::text , ', '::text) AS cities

  FROM world.country cn LEFT JOIN 
       world.city ci 
       ON (cn.countrycode = ci.countrycode)

  GROUP BY cn.countrycode, cn.name_country, cn.region;

Stackoverflow

Insertion/Mise à jour/Suppression sur les vues multi-tables

Problème : quelles tables doivent être modifiées ?

DELETE 
FROM boucheron.country_info
WHERE countrycode = 'FRA' ;

  • supprimer les lignes de country ?

  • supprimer les lignes de city ?

Database abstraction

Vues matérialisées

Les vue matérialisées cachent les données récupérées

Cela se produit

  • lors de la création de la vue et

  • lorsque on exécute REFRESH MATERIALIZED VIEW ...

Lorsque vous marquez une vue comme matérialisée, elle n’interrogera à nouveau les données que lorsque vous lancerez la commande REFRESH.

L’avantage est que vous ne gaspillez pas de ressources en exécutant des requêtes complexes à plusieurs reprises ;

lL’inconvénient est que vous risquez de ne pas avoir les données les plus à jour lorsque vous utilisez la vue.

References

Fin

Fonctions SQL et Vues