bases de donn´ees avanc´ees pl/pgsql - irif.frzielonka/enseignement/bdavances/2014/plsql/... ·...
TRANSCRIPT
Bases de donnees avancees
PL/pgSQL
Wies law Zielonka
19 mars 2015
Table des matieres
1 Bloc de code 2
2 Declaration de et initialisation de variables 2
3 Noms de variables. Les types %TYPE et %ROWTYPE 3
4 Noms de parametres 4
5 IF et CASE 4
5.1 IF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45.2 CASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
6 Plusieurs fonctions avec le meme nom 6
7 Boucles 6
7.1 Boucles LOOP et sortie d’une boucle avec EXIT . . . . . . . . . . . . . . . . . . . 67.2 Boucles WHILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77.3 Boucles FOR, CONTINUE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
8 SELECT ... INTO et la variable FOUND 9
9 Boucle FOR sur les resultats de SELECT. 10
10 Parametres OUT 10
11 Fonctions avec les types polymorphes 11
12 Fonctions avec des types composes 11
13 Les fonction retournant des tables 12
13.1 RETURNS SETOF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1213.2 RETURNS TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1213.3 Ajouter des elements dans la table de resultats : RETURN QUERY et RETURN
NEXT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
14 Executer le code SQL a l’interieur d’une fonction 14
15 EXECUTE pour executer les requetes dynamiques. 15
16 Requete dynamiques dans une boucle FOR. 16
1
17 Utilisation de curseur. 18
17.1 Curseur lies a une requete . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1817.2 FETCH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1817.3 MOVE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1817.4 OPEN/CLOSE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1917.5 Retourner le curseur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1917.6 Boucler sur le resultats d’un curseur . . . . . . . . . . . . . . . . . . . . . . . . . . 2017.7 Curseur non lies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
18 Afficher des messages, lancer une exception 21
Resume
Ces notes ne sont pas corrigees (cela concerne surtout la qualite du francais, le code aete verifie). Mais peut-etre vous les trouverez quand meme utile pour preparer l’examen ouprojet. Ne pas distribuer.
Les avantages de l’utilisation PL/pgSQL : vitesse - code compile une fois reside sur le serveur.
1 Bloc de code
Le code est Structure en bloc de code :
[ <<label>> ]
DECLARE
statements
BEGIN
statements
END
En particulier le corps d’une fonction est un bloc de code.
2 Declaration de et initialisation de variables
name [ CONSTANT ] type [ NOT NULL ] [ {DEFAULT | := } value]
– CONSTANT variable non modifiable (read only),– NOT NULL par defaut les variables initialisees avec la valeur NULL,Une fonction qui calcule la somme de ses deux arguement :
----------------- add.sql ---------------------------------------------
CREATE FUNCTION add(integer, integer) RETURNS INTEGER AS $$
DECLARE
q INTEGER := 0;
BEGIN
q := $1+$2;
RETURN q;
END
$$ LANGUAGE plpgsql;
-----------------------------------------------------------------------
Si on suppose qu’une table tab possede des colonnes a, b, en utilisant cette fonction on pourrafaire la requete
SELECT sum(a,b) from tab;
qui est equivalent select a+b from tab;
2
3 Noms de variables. Les types %TYPE et %ROWTYPE
Pour plus de souplesse on peut declarer de variables du meme type que le type d’un attributou un type d’une table :
nom_table.nom_colonne%TYPE
nom_de_table%ROWTYPE
On rappelle que || est l’operateur de concatenation en SQL.La constructionexpression::type
permet de faire un ≪ cast ≫ et changer le type d’une expression.
----------------------- concat.sql ------------------------------------------------
CREATE OR REPLACE FUNCTION concat(ingredients)
RETURNS TEXT AS $$
DECLARE
a ingredients.name%TYPE;
b ingredients.unit%TYPE;
c ALIAS FOR $1; -- creation d’un nom alias pour le parametre $1
x text;
BEGIN
a := c.name;
b := c.unit;
x := (a ||’ ’)::CHAR(30) ;
x := x||b;
RETURN x ;
END
$$ LANGUAGE ’plpgsql’;
-- Maintenant o peut faire
-- SELECT concat(ingredients) FROM ingredients;
-- ou
-- SELECT concat(ingredients.*) FROM ingredients;
-- Par contre SELECT concat(ingredients); ne marche pas
---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION concat(a ingredients.name%TYPE, b ingredients.unit%TYPE)
RETURNS TEXT AS $$
DECLARE
x text;
BEGIN
x := a::CHAR(30);
x := x||b;
RETURN x ;
END
$$ LANGUAGE ’plpgsql’;
--select concat(name,units) from ingredients;
--------------------------------------------------------------------------
--------------------------- changer_prix --------------------------------
CREATE OR REPLACE FUNCTION changer_prix(f items.price%TYPE, combien REAL )
RETURNS items.price%TYPE AS ’
DECLARE
toto items%ROWTYPE;
prix items.price%TYPE DEFAULT 0;
BEGIN
prix := f * combien;
RETURN prix;
END
’
LANGUAGE ’plpgsql’;
---------------------------------------------------------------------------
3
select changer_prix(price,1.7),price
FROM items
WHERE price IS NOT NULL;
4 Noms de parametres
Les parametres d’une fonction sont nommes $1, $2,... etc. On peut donner les noms avecALIAS et les renommer avec RENAME.
nom ALIAS FOR old_name
RENAME old_name TO new_name;
Utile si les variable NEW et OLD dans les triggers. A noter que c’est une declaration alors amettre dans DECLARE.
---------------------- addnumbers ----------------------------------
CREATE OR REPLACE FUNCTION addnumbers(INTEGER, INTEGER)
RETURNS INTEGER AS $$
DECLARE
Number_1 ALIAS FOR $1;
Number_2 ALIAS FOR $2;
RENAME $1 TO Orig_Number;
BEGIN
RETURN Orig_Number + Number_2;
END;
$$ LANGUAGE plpgsql;
---------------------------------------------------------------
Les parametres peuvent etre utilises uniquement pour passer des donnees, jamais pour passerles noms de tables.
Par exempleINSERT INTO mytable VALUES ($1);
est correct maisINSERT INTO $1 VALUES (42);
ne l’est pas.
5 IF et CASE
5.1 IF
IF condition THEN
instructions
[ ELSIF condition THEN
instructions ]
[ ELSIF condition THEN
instructions ]
[ ELSE
instructions ]
END IF;
On peut faire de fonctions recursives, la fonction suivante calcule le factorielmy factoriel(n) =1× 2× 3× . . .× n.
Noter aussi l’utilisation d’un bloc interne avec son propre DECLARE ... BEGIN...END.
-------------------------- factorial.sql ------------------------------------
CREATE OR REPLACE FUNCTION my_factorial(value INTEGER) RETURNS INTEGER AS $$
DECLARE
arg INTEGER;
BEGIN
arg := value;
IF arg IS NULL OR arg < 0 THEN
4
RAISE NOTICE ’Invalid Number’; -- pour faire un affichage sur la console
RETURN NULL;
ELSE
IF arg = 1 THEN
RETURN 1;
ELSE
DECLARE
next_value INTEGER;
BEGIN
next_value := my_factorial(arg - 1) * arg;
RETURN next_value;
END;
END IF;
END IF;
END;
$$ LANGUAGE ’plpgsql’;
------------------------------------------------------------------------------
5.2 CASE
CASE search-expression
WHEN expression [, expression [...]] THEN
instrructions
WHEN expression [, expression [...]] THEN
instructions
...
[ ELSE
instructions ]
END CASE;
search-expression est evaluee et comparee avec les expressions dans WHEN. Sisearch-epression = expression alors les instructions suivant WHEN correspondant sont executees.
CASE x
WHEN 1, 2 THEN
msg:=msg+1;
ELSE
msg:=msg-1;
END CASE;
Une autre forme de CASE :
CASE
WHEN bool-expression THEN
instrructions
WHEN bool-expression THEN
instructions
...
[ ELSE
instructions ]
END CASE;
CASE
WHEN w<0 THEN
msg:=’negatif’
ELSE
msg:=’positif’;
END CASE;
5
Et troisieme forme de CASE
CASE
WHEN bool-expression THEN
expression
WHEN bool-expression THEN
expression
...
[ ELSE
expression ]
END CASE;
L’exemple precedent peut etre reecrit comme :
msg := CASE
WHEN w<0 THEN
’negatif’
ELSE
’positif’;
END CASE;
6 Plusieurs fonctions avec le meme nom
Comme Java PL/pgSQL supporte plusieurs fonctions avec le meme nom si les signaturesdifferentes. Pour supprimer une fonction definie par utilisateur on utilise :DROP FUNCTION nom-fonction(parametres);L’exemple suivant definit trois fonctions qui portentle meme nom.
-------------------------- compute_date.sql ----------------------------
CREATE OR REPLACE FUNCTION compute_due_date(DATE) RETURNS DATE AS $$
DECLARE ------ declaration de variables
due_date DATE;
rental_period INTERVAL := ’7 days’;
BEGIN
due_date := $1 + rental_period;
RETURN due_date;
END;
$$ LANGUAGE ’plpgsql’;
------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION compute_due_date(DATE, INTERVAL) RETURNS DATE AS $$
BEGIN
-- c’est un commentaire dans une fonction
RETURN( $1 + $2 );
END;
$$ LANGUAGE ’plpgsql’;
-- SELECT compute_due_date(current_date,INTERVAL ’1 MONTH’);
------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION compute_due_date(dans INTERVAL) RETURNS DATE AS $$
BEGIN
RETURN current_date + dans;
END
$$ LANGUAGE ’plpgsql’;
-- select compute_due_date(interval ’3 months’);
-----------------------------------------------------------------------
7 Boucles
7.1 Boucles LOOP et sortie d’une boucle avec EXIT
Boucle infinie :
6
LOOP
insctructions
END LOOP;
On termine cette boucle par RETURN ou EXIT. La forme generale de EXIT :
EXIT [ etiquette ] [ WHEN expression-boolenne ] ;
EXIT deplace le controle apres la boucle. On peut etiqueter les boucles par <<etiquette>> ce quipermet de terminer les boucles imbriquees par EXIT <<etiquette>>; Donc EXIT fait la memechose que break en C.
LOOP
x:=x+1;
IF x>10 THEN
EXIT;
END IF;
END LOOP;
LOOP
x:=x+1;
EXIT WHEN x>10;
END LOOP;
<<toto>>
LOOP
x:=x+1;
LOOP
y:=y-1;
EXIT toto WHEN y<x;
END LOOP;
END LOOP;
7.2 Boucles WHILE
WHILE condition LOOP
instructions
END LOOP;
7.3 Boucles FOR, CONTINUE
[ <<etiquatte>> ]
FOR iterator IN [ REVERSE ] expression1 .. expression2 [ BY expr ] LOOP
instructions
END LOOP ;
La variable iterator est automatiquement declaree comme une variable de type INTEGER (pas besoin dela declarer nous meme). Dans la version avec une etiquette iterator peut-etre prefixee par l’etiquette.
Quelques exemples :
FOR I IN 1..10 LOOP
--I Prendra les valeurs 1,2,...,10
END LOOP;
FOR I IN REVERSE 10..9 LOOP
--I Prendra les valeurs 10,9,8,7,...,1
7
END LOOP;
FOR I IN REVERSE 10..1 BY 2 LOOP
--I prendra les valeurs 10,8,6,4,2
END LOOP;
<<toto>>
FOR i IN 1..n LOOP
k:=k * toto.i;
END LOOP;
Dans l’exemple suivant la deuxieme boucle for n’affiche rien et la premiere affiche une suite de messagesi = 10, i = 9 etc. :
create or replace function add() returns void as $$
declare
i int;
c varchar;
begin
for i in reverse 10..1 loop
c := to_char(i,’999’);
raise notice ’i=%’, i;
end loop;
for i in 10..1 loop
raise notice ’i=%’,i;
end loop;
end
$$ language plpgsql;
Noter l’ordre de bornes inferieure et superieure dans REVERSE, il est l’inverse a celui de PL/SQLd’ORACLE.
create or replace function add(int) returns void as $$
declare
i int;
c varchar;
begin
for i in reverse $1..1 loop
c := to_char(i,’999’);
raise notice ’i=%’, i;
end loop;
--for i in 10..1 loop
-- raise notice ’i=%’,i;
--end loop;
end
$$ language plpgsql;
CONTINUE a l’interieur de la boucle aura le meme effet qu’en langage C, on retourne au debut de laboucle. La forme generale :
CONTINUE [ etiquette ] [ WHEN expression-booleenne ];
----------------------------------- new_factorial.sql --------------------------
CREATE OR REPLACE FUNCTION new_factorial(i INTEGER) RETURNS INTEGER AS $$
DECLARE
k INTEGER := 1;
BEGIN
-- IF
IF i <= 0 THEN
RETURN 0;
END IF;
8
-- Boucle for indexee par variable INTEGER.
-- La variable qui indexe la boucle (L) est de type INTEGER.
-- Inutile de la declarer (meme il ne faut pas la declarer).
-- S’il y a deja une variable L declaree dans le bloc externe
-- cette anciene variable sera invisible a l’interieur de la boucle.
FOR L IN 1..i LOOP
k := k*l;
END LOOP;
RETURN k;
END
$$ LANGUAGE ’plpgsql’;
----------------------------------------------------------------
8 SELECT ... INTO et la variable FOUND
On peut recuperer le resultat d’une requete SELECT grace au SELECT ... INTO ... :
SELECT liste-select INTO destination FROM ...;
La requete SELECT ne doit pas retourner plus qu’une ligne ou une valeur. destination apres INTOest une variable ou une liste de variables dans lesquelles postgres mettra le resultat de SELECT.
Avec FOUND on pourra verifier si la requete a effectivement retourne une ligne ou non.
--------------- into.sql --------------------------------------
CREATE OR REPLACE FUNCTION intot(ingredients.ingredientid%TYPE)
RETURNS ingredients.unit%TYPE AS $$
DECLARE
a ingredients%ROWTYPE; --Declaration d’une variable du type:
--ligne de la table ingredients
BEGIN
-- Maintenant on peut faire SELECT dans a:
SELECT * INTO a
FROM ingredients
WHERE $1 = ingredients.ingredientid ;
IF FOUND THEN --FOUND est TRUE si le select
--a trouve des lignes
RETURN a.unit;
ELSE
RETURN ’unknown’ ;
END IF;
END;
$$ LANGUAGE ’plpgsql’;
---------------------------------------------------------------
Destination peut-etre une liste de variables :
---------------------- intob.sql --------------------------------
CREATE OR REPLACE FUNCTION intot(ingredients.ingredientid%TYPE) RETURNS TEXT AS $$
DECLARE
a ingredients.unit%TYPE;
b ingredients.name%TYPE;
BEGIN
SELECT ingredients.unit, ingredients.name
INTO a,b
FROM ingredients
WHERE $1 = ingredients.ingredientid ;
IF FOUND THEN
RETURN a||’ ’||b ;
ELSE
RETURN ’unknown’ ;
9
END IF;
END;
$$ LANGUAGE ’plpgsql’;
-- select intot(’CHESE’);
9 Boucle FOR sur les resultats de SELECT.
Une boucle for pour le parcours des resultats d’une requete SELECT. La variable iterator doit etresoit de type RECORD soit %ROWTYPE
[<<label>>]
FOR iterator IN
requete-SELECT
LOOP
instructions
END LOOP;
------------------------------- forin.sql -------------------------------------------
CREATE OR REPLACE FUNCTION forin(a ingredients.unit%TYPE) RETURNS DECIMAL(5,3) AS $$
DECLARE
toto RECORD; -- noter une variable de type RECORD
prix ingredients.unitprice%TYPE;
s REAL DEFAULT 0; -- initialisation avec DEFAULT
i INTEGER := 0 ; -- initialisation avec :=
BEGIN
FOR toto IN
SELECT name, unitprice FROM ingredients WHERE unit = a
LOOP
s := s + toto.unitprice;
i := i + 1 ;
END LOOP;
IF i = 0 THEN
RETURN 0;
END IF;
RETURN (s/i) :: DECIMAL(5,3) ; --noter un cast
END;
$$ LANGUAGE ’plpgsql’;
-- SELECT forin(’slice’);
-- retourne le prix moyen par unite de ’slice’ (tranche)
A la place d’une requete SELECT on peut mettre INSERT, UPDATE, DELETE avec une clause RETURNING.
10 Parametres OUT
Certains parametres peuvent etre declares OUT, c’est-a-dire comme de parametres de sortie. La dernierevaleur affectee sur un parametres OUT sert de la valeur de sortie.
-----------------------------------sum_n_product-----------------------
CREATE OR REPLACE FUNCTION
sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN
sum := x + y;
prod := x * y;
END;
$$ LANGUAGE plpgsql;
----------------------------------------------------------------------
10
Si on utilise RETURN dans cette fonction elle devra retourner RECORD.La fonction sum_n_product peut-etre utilisee comme
SELECT sum_n_product(a,b) from tab;
(on suppose que a, b sont deux attributs de la table tab.Si nous voulons acceder a juste un parametre OUT on utilise la syntaxe suivante :
SELECT (sum_n_product(a,b)).sum from tab;
ouselect sum((sum_n_product(a,b))) from tab;
11 Fonctions avec les types polymorphes
Les parametres d’une fonction peuvent etre declares en utilisant les types polymorphes : anyelement,anyarray, anynonarray, anyenum. Nous n’utiliserons que anyelement. Dans la fonction
---------------------------- add_three_values--------------------------
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
result ALIAS FOR $0;
BEGIN
result := v1 + v2 + v3;
RETURN result;
END;
$$ LANGUAGE plpgsql;
------------------------------------------------------------------------
anyelement designe le type quelconque (a condition que l’addition soit supportee par ce type) maistous les trois arguments et la valeur retournee doivent etre du meme type (c’est-a-dire quelconque mais lememe).
Une fonction dont le type de retour est polymorphe possede une variable ( le parametre ) $0, initialiseeNULL, qui permet de stocker la valeur a retourner. Sinon $0 est utilise comme tous les autres $i.
12 Fonctions avec des types composes
------------------------------- create emp ------------------------------------------
CREATE TABLE emp (
name text,
salary numeric,
age integer,
cubicle point
);
--------------------------------------------------------------------------------------
--------------------------------------- double_salary -----------------------------------------
CREATE FUNCTION double_salary(emp) RETURNS numeric AS $$
SELECT $1.salary * 2 AS salary;
$$ LANGUAGE SQL;
-----------------------------------------------------------------------------------------------
Dans la fonction ci-dessus le parametre emp de la fonction signifie que la fonction prend en parametreune ligne (un record) avec les meme types qu’une ligne de la table emp. La fonction est ensuite utiliseecomme
SELECT name, double_salary(emp.*) AS dream
FROM emp
WHERE emp.cubicle ~= point ’(2,1)’;
Si une fonction retourne un type compose alors pour acceder aux champs on utilise une de deuxsyntaxes :
SELECT (new_emp()).name;
SELECT name(new_emp());
11
La notation fonctionnelle peut etre aussi utilisee avec les noms d’attributs d’une table : toto.prixc’est la meme chose que prix(toto) pour une table toto.
Si une fonction retourne un type compose elle peut etre utilisee partout ou on utilise de tables, parexemple dans FROM (ceci est vrai aussi pour les valeurs scalaires mais dans ce cas peu utile).
13 Les fonction retournant des tables
Il y a deux maniere a declarer qu’une fonction retourne une table, soit en utilisant RETURNS SETOF type
soit avec RETURNS TABLE(nom1 type1,...,nomn typen) La table de resultat est construite avec les appelsa RETURN QUERY et/ou RETURN NEXT comme explique le chapitre 13.3.
13.1 RETURNS SETOF
Une fonction qui retourne une table declare la valeur de retour commeRETURNS SETOF sometype
Dans l’exemple suivant on suppose qu’il existe la table nommee ingredients. Donc la fonctioncommander() retourne une table dont chaque ligne a exactement le meme type qu’une ligne de la tableingredients. On suppose que la table ingredients possede l’attribut inventory.
----------------------------------- commander ----------------------------------------
-- le parametre ingredients ci-dessous indique juste que $1 devait etre du meme type
-- que la table ingredients
CREATE OR REPLACE FUNCTION commander() RETURNS SETOF ingredients AS $$
BEGIN
-- le select suivant peut retourner plusieurs lignes.
-- Sans SETOF le select retourne une seule ligne.
RETURN QUERY SELECT * FROM ingredients WHERE inventory = 0 OR INVENTORY IS NULL;
END ;
$$ LANGUAGE plpgsql;
----------------------------------------------------------------------------------------
Dans l’exemple precedent on peut aussi ecrire RETURNS SETOF ingredients%ROWTYPE a la place RETURNSSETOF ingredients.
Si le type de retour de la fonction n’existe pas on peut le creer avec CREATE TYPE. Dans l’exemplesuivant on commence par creer un type doubleint qui represente une ligne avec deux valeurs de typeinteger. Ensuite on definit une fonction retournant une table dont chaque ligne est composee de deuxvaleurs integer.
Dans cet exemple on suppose qu’il existe une table tab avec les attributs a,b,c de type int.
-----------------ajouterunsetof()----------------------------------
create type doubleint as (x integer, y integer);
drop function ajouterunsetof();
create or replace function ajouterunsetof()
returns setof doubleint as $$
declare
r record;
begin
for r in select * from tab where a is not null
loop
return next (r.a+1,r.b+1); --ceci ajoute une ligne dans la table de resultats
end loop;
return;
end $$ language plpgsql;
-----------------------------------------------------------------
13.2 RETURNS TABLE
La fonction suivante retourne une table avec les attributs nom et prix.
--------------------------- modifier_prix--------------------------------
DROP FUNCTION modifier_prix(NUMERIC(10,2)) ;
CREATE OR REPLACE FUNCTION modifier_prix(NUMERIC(10,2))
12
RETURNS TABLE(nom items.name%TYPE, prix items.price%TYPE)
AS $$
BEGIN
RETURN QUERY SELECT name, price * $1 FROM items ;
END ;
$$ LANGUAGE plpgsql;
--------------------------------------------------------------------------------------
13.3 Ajouter des elements dans la table de resultats : RETURNQUERYet RETURN NEXT
Les fonctions qui retournent une table doivent construire la table de resultat avant de terminer.Au debut la table de resultats est vide. On ajoute les tuples dans cette table en utilisant une des
instructions suivantes :
RETURN NEXT expression;
RETURN QUERY requete;
RETURN QUERY EXECUTE string-req [ USING expression [, ...] ];
Ces trois formes de RETURN ne terminent pas l’execution de la fonction mais servent a ajouter destuples dans la table des resultats.
La fonction termine si on execute RETURN sans parametre ou si l’execution arrive a la derniere instruc-tion de la fonction. En terminant la fonction retourne la table construite.
RETURN NEXT expression;
ajoute un tuple de resultat expression dans la table des resultats. Cette forme est utilisee dans les fonctionsavec RETURN SETOF type. Le type de l’expression doit correspondre au type indique dans RETURN SETOF
type, voir la fonction ajouterunsetof() dans le chapitre 13.1.Dans les fonction avec RETURNS TABLE(...) on utilise plutot RETURN NEXT sans expression. Dans la
fonction ajouterun() ci-dessous RETURN NEXT; n’est pas suivi d’une expression. Ce sont les valeurs ac-tuelles de parametres x et y qui sont utilisees pour ajouter la ligne (x,y) dans la table de resultats aumoment uu RETURN NEXT est execute. Donc les d’attributs de la table de resultat sont utilises dans lafonction comme de variables locales.
Dans cet exemple on suppose que la Base de Donnees contient une table tab avec les attributs a,b (etpeut-etre d’autres attributs).
-----------------ajouterun()--------------------------------------
create or replace function ajouterun()
returns table(x integer, y integer) as $$
declare
r record;
begin
for r in select * from tab where a is not null
loop
x:=r.a+1;
y:=r.b+1;
return next; --ceci ajoute (x,y) dans la table de resultats
end loop;
return;
end $$ language plpgsql;
-----------------------------------------------------------------
Ensuite nous pouvons utiliser cette fonction comme dans ici :
select *
from ajouterun() as t, tab
where t.x=tab.a;
RETURN QUERY ajoute les resultats d’une requete dans la table des resultats. La fonction suivante faitexactement la meme chose que la fonction ajouterun() de l’exemple precedent en ajoutant un tuplechaque fois :
-----------------ajouterunbis()--------------------------------------
create or replace function ajouterunbis()
returns table(x integer, y integer) as $$
13
declare
r record;
begin
for r in select * from tab where a is not null
loop
return query values(r.a+1,r.b+1) ; --ceci ajoute un tuple (r.a+1,r.b+1)
-- dans la table de resultats.
end loop;
return;
end $$ language plpgsql;
-----------------------------------------------------------------
Mais on peut faire la meme chose sans boucle :
-----------------ajouteruntiers()--------------------------------------
create or replace function ajouteruntiers()
returns table(x integer, y integer) as $$
begin
return query select a+1,b+1 from tab where a is not null;
return;
end $$ language plpgsql;
-----------------------------------------------------------------
Dans la version RETURN QUERY EXECUTE la requete SELECT se trouve dans une chaıne de caracteresstring-reg. Cette chaıne peut contenir des occurrences de caractere % qui seront remplacees par les valeursdes expressions qui se trouvent apres USING.
C’est RETURN sans parametre qui termine la fonction et retourne la table construite grace au RETURN NEXT
et RETURN QUERY.
14 Executer le code SQL a l’interieur d’une fonction
Nous pouvons mettre le code SQL directement dans les fonctions PL/pgSQL : CREATE TABLE, INSERT,UPDATE, DELETE, etc. sauf SELECT et sauf l’appel a une autre fonction si le resultat de SELECT ou del’appel n’est pas recupere.
Si on veut executer SELECT sans recuperation de resultat on fera
PERFORM requete-select ;
comme dans
PERFORM * FROM ingredients WHERE unitprice < 5;
Noter que PERFORM remplace le mot SELECT.PERFORM peut etre utilise pour evaluer une expression SQL sans recuperation de resultat, par exemple
PERFORM fonction(parametres);
Apres PERFORM on pourra tester la variable FOUND pour voir si la requete a produit un resultat ou sielle a retourne NULL. Si on utilise PERFORM c’est pour les effets de bord de la requete et non pas pourla valeur retournee (la valeur on l’ignore sciemment).
-------------------- ajouter_meal()------------------------------------------------------
CREATE OR REPLACE FUNCTION ajouter_meal( meals.mealid%TYPE, meals.name%TYPE)
RETURNS INTEGER AS $$
DECLARE
id ALIAS FOR $1;
nom ALIAS FOR $2;
a INTEGER;
BEGIN
-- si a la place de l’instruction suivante on ecrit
-- SELECT * FROM meals WHERE mealid=id;
14
-- cela compile OK mais probleme a l’execution.
a := add(5,6);
PERFORM add(7,8);
PERFORM * FROM meals WHERE mealid=id;
IF FOUND THEN
RETURN 0;
END IF;
INSERT INTO meals VALUES(id,nom);
RETURN 1;
END;
$$ LANGUAGE ’plpgsql’;
-----------------------------------------------------------------------------------------------------
15 EXECUTE pour executer les requetes dynamiques.
EXECUTE command-string;
permet d’executer une requete contenue dans une chaıne de caracteres. Cela permet de faire de requetesdynamiques qui ne sont pas precompilees.
Quelques exemples :
------------------- exect.sql ---------------------------------
CREATE OR REPLACE FUNCTION exect(commande VARCHAR) RETURNS INTERVAL AS $$
DECLARE
beg_time TIMESTAMP;
end_time TIMESTAMP;
BEGIN
beg_time := current_timestamp;
EXECUTE commande;
end_time := current_timestamp;
RETURN end_time - beg_time;
END
$$ LANGUAGE ’plpgsql’;
--SELECT exect(’SELECT * FROM ingredients’);
----------------------------------------------------------------------
----------------------------------- modif_prix -----------------------------------------
CREATE OR REPLACE FUNCTION modif_prix(VARCHAR(30), DEC(5,2), DEC(6,2))
RETURNS SETOF ingredients AS $$
DECLARE
qui ALIAS FOR $1;
plus ALIAS FOR $2;
pcent ALIAS FOR $3;
val ingredients.unitprice%TYPE;
tal ingredients.unitprice%TYPE;
r RECORD;
u RECORD;
BEGIN
FOR r IN
EXECUTE ’SELECT * FROM ingredients WHERE ’|| qui
LOOP
val := r.unitprice * ( 100 + pcent )/100 ;
tal := r.unitprice + plus;
IF val > tal THEN
u := ROW( r.ingredientid, r.name, r.unit, val, r.foodgroup, r.inventory, r.vendorid )::ingredients;
RETURN NEXT u;
ELSE
u := ROW( r.ingredientid, r.name, r.unit, tal, r.foodgroup, r.inventory, r.vendorid )::ingredients;
RETURN NEXT u;
END IF;
END LOOP;
RETURN;
15
END; $$
LANGUAGE plpgsql;
-----------------------------------------------------------------------------------------------
---------------------- test_modif_prix----------------
SELECT I.name, I.unitprice, AUG.unitprice
FROM ingredients I JOIN
modif_prix(’foodgroup=’’Milk’’’,
0.05, 5) AS AUG USING(ingredientid) ;
------------------------------------------------------
Forme generale de EXECUTE :
EXECUTE command-string [ INTO [ STRICT ] target ] [ USING expression, ...] ] ;
command-string de type text. target est une variable de type RECORD, ou une variable de type ROWTYPE ouune liste de variables simples. Le resultat de la requete sera stocke dans ces variables. Si la requete retourneplusieurs lignes alors c’est la premiere ligne qui sera stockee dans le(s) variable(s) INTO . Avec STRICT il auraune erreur si la requete retourne plusieurs lignes. command-string peur avoir des parametres, $1, $2, ...
qui seront substitues par les valeurs calculees dans USING. Cela ne concerne que les parametres, $1,... neconcerne pas les noms de tables qui doivent etre injectes comme les chaınes de caracteres.
On peut utiliser les fonction quote_lireal quote_ident quote_nullable.quote_indent(text) retourne une chaıne de caracteres avec des ’ pour l’utilisation comme identifica-
teur.*quote_literal(text) pour que ’ approprie comme string-literal. Elle retourne NULL si l’argument
NULL. Si on veut NULL pour l’argument NULL il faut utiliser quote_nullable(text).
16 Requete dynamiques dans une boucle FOR.
[<<label>>]
FOR iterator IN
EXECUTE string-requete [ USING expression,... ]
LOOP
instructions
END LOOP;
string-requete s’evalue comme une chaıne de caracteres contenant une requete SELECT. Exemples :
---------------------------- somme2.sql ---------------------------------------
CREATE OR REPLACE FUNCTION somme2(req VARCHAR) RETURNS REAL AS $$
DECLARE
toto REAL := 0; -- noter une variable de type RECORD
s REAL DEFAULT 0; -- initialisation par DEFAULT
BEGIN
FOR toto IN
EXECUTE req
LOOP
s := s + toto;
END LOOP;
RETURN s;
END;
$$ LANGUAGE ’plpgsql’;
--SELECT somme2(’SELECT unitprice FROM ingredients’);
--------------------------------------------------------------------------------
---------------------------- somme_square.sql ---------------------------------------
CREATE OR REPLACE FUNCTION somme_square(tables text, col text) RETURNS REAL AS $$
DECLARE
toto REAL := 0; -- noter une variable de type RECORD
s REAL DEFAULT 0; -- initialisation par DEFAULT
16
BEGIN
FOR toto IN
EXECUTE ’SELECT ’ || quote_ident(col) || ’ FROM ’|| quote_ident(tables)
LOOP
s := s + toto*toto;
END LOOP;
RETURN sqrt(s);
END;
$$ LANGUAGE ’plpgsql’;
select somme_square(’ingredients’,’unitprice’);
Encore un exemple qui calcule la deviation. Par definition une deviation d’un ensemble de n valeurs :x1, . . . , xn est egale a 1
n
∑n
i=1(xi − x) ou x = 1
n
∑n
i=1xi est la moyenne. Le parametres de la fonction :
t - le nom de la table, col le nom de la colonne, cond - une condition a mettre dans WHERE. So cond estNULL alors il n’y aura par WHERE dans SELECT :
----------------deviation()------------------------------------------
drop function deviation(varchar,varchar,varchar);
create or replace function deviation(t varchar, col varchar, cond varchar) returns real as $$
declare
s real; sq real;
i integer;
r real; sigma real;
m varchar;
x varchar;
d int := 5;
begin
s:=0; i:=0; sq:=0;
m:= case when cond is null
then ’’
else ’ where ’||cond
end ;
-- case peut etre remplace par IF:
-- IF cond is null then
-- m:=’’;
-- else
-- m:= ’ where ’||cond;
-- end if;
for r in
execute ’select ’||col|| ’ from ’|| t || m
loop
if r is not null then
s=s+r;
sq=sq+r*r;
i=i+1;
end if;
end loop;
sigma:=sq/i-(s/i)*(s/i);
return sigma;
end
$$ language plpgsql;
-------------------------------------------------------------------
Dans cet exemple on pourrait utiliser EXECUTE ... USING :
for r in
execute ’select $1 from ’ || t || m USING col
loop
...
17
end loop;
Noter qu’il est impossible de mettre le nom de la table dans USING, uniquement les donnees.
17 Utilisation de curseur.
Pour travailler avec le curseur : DECLARE, OPEN, FETCH, CLOSE
Une fonction peut retourner un curseus et l’appelant peut l’utiliser pour parcourir la table de resultat.C’est moyen efficace pour retourner un enseble de lignes d’une table.
17.1 Curseur lies a une requete
Declaration dans la partie DECLARE d’un bloc :
nom_cursor CURSOR FOR requete_select ;
nom_cursor [ [NO] SCROLL] CURSOR(parametres_formelles) FOR requete_select ;
declarent des curseur lies. SCROLL indique qu’on pourra faire un parcours en arriere sur le resultat. Pa-rametres formelles est une liste de couples nom type_de_donnee.
Exemples :
DECLARE
curs1 refcursor; --curseur non lie
curs2 CURSOR FOR SELECT * FROM tenk1;
curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
Toutes les trois variables sont de type refcursor mais les deux dernieres sont liees a des requetes. De pluscurs3 possede un parametre key de type INTEGER.
17.2 FETCH
Pour recuperer les valeurs faire dans une boucle :
FETCH [ direction {FROM|IN} ] nom_cursor INTO destinations(s) ;
ou destination(s) une variable RECORD ou une variable %ROWTYPE ou une liste de variables. Ici un exemplequ’on aurait pu faire avec FOR. Noter EXIT WHEN NOT FOUND; qui permet de tester si le parcours est fini.Si rien a lire destination prendra la valeur NULL.
Les directions– NEXT – ligne suivante,– PRIOR – ligne precedente,– FIRST – la premiere ligne,– LAST – la derniere ligne,– ABSOLUTE numero – ligne numero a partir du debut,– RELATIVE numero – linge numero a partir de la position courante du curseur. Dans ce cas l’argument
numero peut-etre negatif.permettent de recuperer une seule ligne (eventuellement NULL).
Les directions– ALL
– FORWARD
– FORWARD nombre
– FORWARD ALL
– BACKWARD
– BACKWARD nombre
– BACKWARD ALL
permettent de recuperer le nombre de lignes indique a partir de la position courante, soit en avant soit enarriere. Ces directions ne font pas partie du standard SQL (ce sont des extension de postgres).
FETCH sans direction est equivalent a FETCH NEXT
17.3 MOVE
MOVE [ direction { FROM | IN } ] cursor;
deplace le curseur mais ne retourne pas de resultat.
18
17.4 OPEN/CLOSE
Pour ouvrir un curseur lie avec les parametres :
OPEN nom_cursor(parametres_actuels);
Pour fermer un curseur non utilise faire :
CLOSE cursor;
------------------------------------- curs.sql ------------------------------------
CREATE OR REPLACE FUNCTION
curs(ingredients.unitprice%TYPE, ingredients.unitprice%TYPE)
RETURNS REAL AS $$
DECLARE
-- declaration d’un cursor
toto CURSOR FOR
SELECT * FROM ingredients WHERE unitprice BETWEEN $1 AND $2;
resultat RECORD;
s REAL DEFAULT 0; -- initialisation par DEFAULT
BEGIN
OPEN toto; -- ouvrir le cursor
LOOP
FETCH toto INTO resultat; -- fetch une ligne dans un record
EXIT WHEN NOT FOUND; -- Plus rien a lire alors quitter la boucle
s := s + resultat.unitprice * resultat.unitprice ;
END LOOP;
CLOSE toto; -- fermer le cursor
RETURN s;
END;
$$ LANGUAGE ’plpgsql’;
--SELECT curs(0.01,0.03);
----------------------------------------------------------------------------------
Les curseur utilisent des ressources de systeme (la memoire pour la table parcourus par le curseur). Si lecurseur n’est plus utilise il est recommander de le fermer.
17.5 Retourner le curseur
Un fonction peut retourner le curseur (la fonction RETURNS refcursor). Il faut d’abord associer un nomavec le curseur avant OPEN. Pour cela on fait une affectation d’une chaıne de caracteres sur la variablecurseur. Cette chaıne de caractere c’est le nom de curseur. (Mais ce nom peut etre genere automatiquement,donc il n’est pas necessaire d’associer un nom au curseur.)
Exemple suivant montre comment l’appelant passe le nom de curseur vers la fonction, c’est le plussimple puisque autrement l’appelant devrait trouver le nom de curseur autrement.
CREATE TABLE test (col text);
INSERT INTO test VALUES (’123’);
CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS $$
BEGIN
OPEN $1 FOR SELECT col FROM test;
RETURN $1;
END;
$$ LANGUAGE plpgsql;
BEGIN;
SELECT reffunc(’funccursor’);
FETCH ALL IN funccursor;
COMMIT;
19
17.6 Boucler sur le resultats d’un curseur
[ <<label>> ]
FOR recordvar IN bound_cursorvar [ ( argument_values ) ] LOOP
statements
END LOOP [ label ];
Cela concerne uniquement le curseurs lies, le curseur ne peut pas etre ouvert a ce moment. La variablerecordvar est automatiquement declaree comme record et existe seulement dans la boucle.
17.7 Curseur non lies
On peut declarer un curseur qui ne sera pas associe a une requete. Le curseur de ce type est declarecomme une variable de type refcursor :
mon_cursor refcursor;
Si nous avons un curseur non lie alors au moment de faire OPEN on specifie la requete SELECT :
OPEN mon_cursor_non_lie [[NO] SCROLL] FOR requete;
pour une requete fixe ou
OPEN mon_cursor [[ NO ] SCROLL ] FOR EXECUTE string-requete;
pour une requete dynamique.Ensuite nous pouvons utiliser le curseur pour parcourir les resultats de la requete.L’exemple precedent reecrit pour utiliser les curseurs non lies :
------------------------------- curspd.sql ---------------------------------------------
CREATE OR REPLACE FUNCTION curspd()
RETURNS VOID AS $$
DECLARE
-- declaration des cursor non lies
vendor refcursor;
ingr refcursor;
resultat RECORD;
val ingredients%ROWTYPE;
I INTEGER;
BEGIN
OPEN vendor FOR SELECT * FROM vendors; -- ouvrir le cursor
LOOP
FETCH vendor INTO resultat; -- fetch une ligne de vendors dans un record
EXIT WHEN NOT FOUND; -- Plus rien a lire alors quitter la boucle
I := 0;
OPEN ingr FOR
SELECT * FROM ingredients
WHERE resultat.vendorid = ingredients.vendorid;
LOOP --boucle interne
FETCH ingr INTO val; --fetch une ligne
EXIT WHEN NOT FOUND;
I := I+1;
END LOOP;
CLOSE ingr; --fermer cursor
IF I > 2 THEN
RAISE NOTICE ’bon vendor % ’, resultat.repfname;
ELSE
RAISE NOTICE ’mauvais vendor %’, resultat.repfname;
20
END IF;
END LOOP; --fin boucle externe
CLOSE vendor; -- fermer le cursor
RETURN ;
END;
$$ LANGUAGE ’plpgsql’;
--------------- utilisation:
--SELECT curspd();
------------------------------------------------------------------------------
L’exemple suivant presente une fonction qui calcule la mediane de valeurs d’une colonne. Le nom dela table et le nom de la colonne passent comme les parametres de la fonction.
-------------------median()------------------------------------
create or replace function median(tab varchar, col varchar)
returns real as $$
declare
c refcursor;
i integer;
r real;
begin
open c SCROLL for
execute ’select ’|| col|| ’ from ’|| tab ||
’ where ’||col||’ is not null’
||’ ORDER BY ’||col;
i:=0;
LOOP
move next from c ;
exit when not found;
i:=i+1;
end LOOP;
FETCH absolute (i+2)/2 FROM c into r;
close c;
return r;
end $$ language plpgsql;
----------------------------------------------------------------
18 Afficher des messages, lancer une exception
Pour afficher des messages on utilise :
RAISE NOTICE ’message’ [,variable ..];
Le message peut contenir les % dont les occurrences sont substituees par les variables de la liste.
------------------------------- cursp.sql ---------------------------------------------
CREATE OR REPLACE FUNCTION cursp()
RETURNS VOID AS $$
DECLARE
-- declaration d’un cursor simple
vendor CURSOR FOR
SELECT * FROM vendors;
-- declaration d’un cursor parametre
ingr CURSOR(ID ingredients.vendorid%TYPE) FOR
SELECT * FROM ingredients WHERE ID = ingredients.vendorid;
resultat RECORD;
21
val ingredients%ROWTYPE;
I INTEGER;
BEGIN
OPEN vendor; -- ouvrir le cursor
LOOP
FETCH vendor INTO resultat; -- fetch une ligne de vendors dans un record
EXIT WHEN NOT FOUND; -- Plus rien a lire alors quitter la boucle
I := 0;
OPEN ingr(resultat.vendorid) ;
LOOP --boucle interne
FETCH ingr INTO val; --fetch une ligne
EXIT WHEN NOT FOUND;
I := I+1;
END LOOP;
CLOSE ingr; --fermer cursor
IF I > 2 THEN
RAISE NOTICE ’bon vendor % ’, resultat.repfname;
ELSE
RAISE NOTICE ’mauvais vendor %’, resultat.repfname;
END IF;
END LOOP; --fin boucle externe
CLOSE vendor; -- fermer le cursor
RETURN ;
END;
$$ LANGUAGE ’plpgsql’;
--------------- utilisation:
--SELECT cursp();
------------------------------------------------------------------------------
Pour lancer une exception on utilise :
RAISE EXCEPTION ’format’ [,expression [, ...]]
[ USING option = expression [,...]] ;
Il y a plusieurs option possibles, en particulier, par exempleUSING ERRCODE=’23505’
indique le code d’erreur. Les codes d’erreur existant sont specifies dans la documentation de postgres.Si il y a une exception pendant l’execution d’une fonction alors PLpgSQL fait automatiquement
ROLLBACK jusqu’au debut du bloc BEGIN.
22