PostgreSQL – Vade-mecum SQL – 6/8 – Programmation

Le principe

Les fonctions permettent d’enregistrer une procédure (une requête en gros) puis de l’exécuter à loisir. Les procédures autorisent l’utilisation de certaines syntaxes spécifiques ainsi que d’autres langages plus puissants que le simple SQL. Nous verront ici le PL/pgSQL, un langage dérivé du SQL dédié au fonctions.

Vous savez déjà utiliser les fonctions, nous les avons vu durant les précédentes page. Elles s’utilise en lieu et place d’une colonne ou d’une table selon le type de donnée renvoyée.

Les requêtes

La théorie

CREATE FUNCTION mon_schema.ma_fonction()
	-- On déclare le nom de la fonction

RETURNS type AS
	-- On indique le type de données qui sera renvoyé :
	-- 	integer
	-- 	text
	-- 	date
	-- 	...
	-- 	void : permet d'indiquer que la fonction ne renvoie rien

	'Définition de la fonction'
		-- La fonction est définie entre quotes

LANGUAGE sql
	-- On clos la fonction en indiquant le langage utilisé dans la définition
;

La pratique

Le SQL

Fonction qui effectue un calcule et renvoie la valeur « 2 ».

CREATE FUNCTION mon_schema.ecrire2()
RETURNS integer AS
	'SELECT 1 + 1'
LANGUAGE sql
;

Fonction qui ajoute « 2 » à une valeur qui lui ait envoyé.

CREATE FUNCTION mon_schema.ajout2(IN mon_argument integer)
 	-- On déclare un argument :
 	-- 	Méthode 
 	-- 	 	IN : La valeur sera passée à la fonction (optionnel, défaut)
 	-- 	 	OUT : La valeur sera renvoyée par la fonction (équivalent du "RETURNS type AS")
 	-- 	mon_argument : le nom de l'argument (optionnel)
 	-- 	integer : le type de valeur qui sera utilisé

RETURNS integer AS
'SELECT $1 + 1' 	
	-- L'argument est utilisé avec le code "$ordre_d_apparition" : ici "$1"

LANGUAGE sql
;

Il est possible de renvoyer plus d’une valeur avec une fonction :

• Soit sous la forme de plusieurs colonnes
• Soit sous la forme de plusieurs lignes
• Soit sous les deux forme en même temps

CREATE FUNCTION mon_schema.deux_valeurs()
RETURNS table (valeur_1 integer, valeur_2 text) AS
	-- La fonction renvoie un tableau composé de deux colonnes.
	'
	SELECT
		22, 
		''Voici un texte''::text
	'
	-- On fait ici un SELECT classique avec deux colonnes (une pour chaque valeur retournée)
LANGUAGE sql
;

Voici une variante

CREATE FUNCTION mon_schema.deux_valeurs(OUT valeur_1 integer, OUT valeur_2 text)
	-- On déclare les valeurs renvoyées par la fonction comme argument avec le préfixe OUT
AS
	-- Pas besoin de RETURN car on a déjà indiqué ce qui ressortira

	'
	SELECT
		22, 
		''Voici un texte''::text
	'
LANGUAGE sql
;

Pour utiliser ces fonctions, deux méthodes :

SELECT deux_valeurs();

--> Résultat
    deux_valeurs
---------------------
(22,"Voici un texte")


SELECT * FROM mon_schema.deux_valeurs();

--> Résultat
 valeur_1 |    valeur_2      
----------|---------------
    22    | Voici un texte

Le PL/pgSQL

Renvoyons du texte

CREATE FUNCTION mon_schema.mon_texte()
RETURNS text AS

$CORPS$
	-- Plutôt que d'utiliser l'apostrophe on utilise un délimiteur ce qui va nous simplifier la tâche (voir "concepts clés" plus bas) et éviter les échappements
	BEGIN
		-- On démarre le bloc de code
		RETURN 'Hello world';
		-- On indique la valeur qui est retournée avec "RETURN".
	END;
	-- On fini le bloc de code
$CORPS$
	-- On ferme la délimitation de la fonction
LANGUAGE PLPGSQL
	-- Ici aussi on indique le langage : PLPGSQL
;

Ajoutons quelques variables.

CREATE FUNCTION mon_schema.jaime_la_variable()
RETURNS integer AS

$CORPS$
	DECLARE
	-- On déclare nos variables en indiquant leur type et en leur affectant une valeur si nécessaire
		variable_1 integer;
		variable_2 integer := 14;
		-- En SQL on peut utiliser un simple "=" pour affecter une valeur, en PL/pgSQL c'est ":="
		-- Le type peut être omis lorsqu'on affecte une valeur car il en sera déduit

	BEGIN
		variable_1 := variable_2 * 4;
		RETURN variable_1;
	END;
$CORPS$
LANGUAGE PLPGSQL
;

Les variables avec un tableau.

CREATE FUNCTION mon_schema.retour_table(mon_argument integer)
RETURNS TABLE (colonne_1 text, colonne_2 integer) AS
	-- On va renvoyer un tableau dont on a défini les deux colonnes
	-- On aurait pu utiliser le préfixe OUT dans la ligne précédente
$CORPS$
	DECLARE
		variable_1 integer;
		variable_2 text := 'Hello world';
		variable_3 integer := 14;

	BEGIN
		variable_1 := variable_3 * mon_argument;
		-- Avec le PL/pgSQL, on peut utiliser directement le nom de l'argument plutôt que le code "$ordre_d_apparition"
		RETURN QUERY SELECT variable_2, variable_1;
		-- On fait un "RETURN QUERY" car on renvoi le résultat d'une requête et non juste une valeur
		RETURN;
		-- Ici, le RETURN final pourrait être omis car la fonction a déja renvoyée une valeur (mais on peut imaginer plusieurs RETURN QUERY avant un RETURN final).
	END
$CORPS$
LANGUAGE PLPGSQL
;

Les structures conditionnelles

if

CREATE FUNCTION mon_schema.test_conditionnel(nombre integer)
RETURNS varchar(15) AS

$CORPS$
	BEGIN
		IF nombre < 0
		-- Si..., le résultat de cette requête doit être booléen 
			THEN RETURN 'Négatif';
		ELSIF nombre = 0
		-- Sinon si... (optionnel)
			THEN RETURN 'Nul';
		ELSE
		-- Sinon... (optionnel, s'il n'est pas indiqué alors on renvoi NULL dans ce cas)
			RETURN 'Positif';
		END IF ;
	END
$CORPS$
LANGUAGE PLPGSQL
;

CASE WHEN

CREATE FUNCTION mon_schema.test_conditionnelv2(nombre integer)
RETURNS varchar(15) AS

$CORPS$
	DECLARE
		variable_1 varchar(15);

	BEGIN
		CASE
			WHEN nombre < 0
				THEN variable_1 := 'Négatif';
				-- Lorsque que "nombre" est inférieur à 0 alors on affiche "négatif"
			WHEN nombre = 0
				THEN variable_1 := 'Nul';
 				-- Le deuxième cas est optionnel (on peut en ajoutant autant que nécessaire)
			ELSE
				variable_1 := 'Positif';
				-- Sinon on affiche "Positif"
		END CASE;

		RETURN variable_1;
		-- Ici on ne renvoi la valeur qu'après le CASE mais la même structuration que le IF précédent (avec des RETURN dans le CASE WHEN) fonctionne également.
	END
$CORPS$
LANGUAGE PLPGSQL
;

Une variante du CASE WHEN

CREATE FUNCTION mon_schema.test_conditionnelv3(nombre integer)
RETURNS varchar(15) AS

$CORPS$
	DECLARE
		x integer;

	BEGIN
	
		x := nombre/2;

		CASE x
		-- On indique la valeur à tester pour éviter de la répéter dans les prédicats
			WHEN -5
				THEN RETURN 'Moins 5';
				-- Lorsque que x = -5 alors on affiche "Moins 5"
			WHEN 0
				THEN RETURN 'Zéro';
			ELSE
				RETURN 'Autre';
				-- Sinon on affiche "Autre"
		END CASE;
	END
$CORPS$
LANGUAGE PLPGSQL
;

La boucle

Cas standard

CREATE FUNCTION mon_schema.la_boucle(nombre integer)
RETURNS SETOF integer AS
	-- On va renvoyer plusieurs valeurs integer : un set d'integer sous la forme de plusieurs lignes

$CORPS$
	BEGIN
		<< ma_boucle >>
		-- On définit le nom de la boucle (optionnel). 
		LOOP			
		-- On démarre la boucle

			IF nombre > 10
				THEN EXIT ;
				-- On met un condition pour sortir de la boucle (EXIT)
			ELSIF nombre < -10
				THEN EXIT ;
			END IF;

			RETURN NEXT nombre;
			-- On renvoi la valeur et on continu la boucle (NEXT)

			nombre = nombre * 2;
			-- On fait un calcul avant de repartir pour un tour de boucle

		END LOOP ma_boucle ;
		-- Fin de la boucle nommée "ma_boucle" (on aurait pu omettre le label car il n'y a qu'une seule boucle à stopper)

		RETURN;	
		-- On renvoi le tout
	END
$CORPS$
LANGUAGE PLPGSQL
;

Variante du cas standard

CREATE FUNCTION mon_schema.la_bouclev2(nombre integer)
RETURNS SETOF integer AS

$CORPS$
	BEGIN
		LOOP
			EXIT WHEN nombre > 10 OR nombre < -10;
	 		-- On définit plus simplement quand sortir

			RETURN NEXT nombre;

			nombre = nombre * 2;

		END LOOP;
		RETURN;
	END
$CORPS$
LANGUAGE PLPGSQL
;

Autre variante du cas standard

CREATE FUNCTION mon_schema.la_bouclev3(nombre integer)
RETURNS SETOF integer AS

$CORPS$
	BEGIN
		LOOP
			EXIT WHEN nombre > 10 OR nombre < -10;

			nombre = nombre + 2;

			CONTINUE WHEN nombre < 0;
			-- CONTINUE permet de redémarrer un cycle de boucle sans faire ce qui est indiqué après.

			RETURN NEXT nombre;
			-- Ici on affichera que les valeurs supérieures à 0
		END LOOP;
		RETURN;
	END
$CORPS$
LANGUAGE PLPGSQL
;

Le « tant que »

CREATE FUNCTION mon_schema.le_tant_que(nombre integer)
RETURNS SETOF integer AS

$CORPS$
	BEGIN
		WHILE nombre > -10 AND nombre < 10
		-- Tant que "nombre" est entre -10 et 10 alors on fait la boucle
		LOOP
			nombre = nombre + 2;
			RETURN NEXT nombre;
			-- On peut ici aussi utiliser des EXIT et des CONTINUE
		END LOOP;
		RETURN;
	END
$CORPS$
LANGUAGE PLPGSQL
;

Le « jusqu’à »

CREATE FUNCTION mon_schema.le_jusqua(nombre integer)
RETURNS SETOF integer AS

$CORPS$
	BEGIN
		FOR i IN 1..10 BY 1
		-- Pour "i" entre 1 et 10 on fait la boucle en incrémentant de "1" (par défaut c'est 1 donc on est pas obligé de le mettre)
		LOOP
			nombre = nombre + i;
			RETURN NEXT nombre;
			-- On peut ici aussi utiliser des EXIT et des CONTINUE
		END LOOP;
		RETURN;
	END
$CORPS$
LANGUAGE PLPGSQL
;

Le « jusqu’à » v2

CREATE FUNCTION mon_schema.le_jusquav2(nombre integer)
RETURNS TABLE (colonne_1 integer, colonne_2 integer) AS
 	-- RETURNS TABLE inclut un SETOF ce qui permet d'avoir plusieurs lignes

$CORPS$
	BEGIN
		FOR i IN 1..10 BY 2
		-- On incrémente de 2 à chaque tour
		LOOP
			nombre = nombre + i;
			RETURN QUERY SELECT nombre, i as "Pas";
	  		-- On retourne ici une requête pour avoir le "nombre" et le pas : "i" donc deux valeurs
		END LOOP;
		RETURN;
	END
$CORPS$
LANGUAGE PLPGSQL
;

Le « jusqu’à » v3

CREATE FUNCTION mon_schema.le_jusquav3()
RETURNS SETOF text AS

$CORPS$
	BEGIN
		FOR i IN SELECT * FROM ma_table
		-- On va boucler sur la totalité des lignes de la table "ma_table".
		-- "i" va prendre successivement la valeur de chaque ligne.
		LOOP
			RETURN NEXT i.colonne_a || i.colonne_b;
 			-- On peut appeler les colonnes de "ma_table" qui nous intéressent.
		END LOOP;
		RETURN;
	END
$CORPS$
LANGUAGE PLPGSQL
;

Le « jusqu’à » v4

-- Copie des initiales dans une table client pour les mettre dans une colonne "initiale"
CREATE FUNCTION mon_schema.le_jusquav4()
RETURNS text AS

$CORPS$
	DECLARE
		mon_array text array;
		-- On déclare une liste
		i text;

	BEGIN
		mon_array = array(
			SELECT
				nom_client::text 
			FROM
				mon_schema.tables_client
		);
		-- On récupère les valeurs d'une table dans une liste (array)

		FOREACH i IN ARRAY mon_array
		-- On passe chaque éléments de la liste dans la boucle pour les utiliser
		LOOP
			EXECUTE $a$
			-- Pour chaque tour de boucle, on exécute la requête SQL située entre quotes ($a$)
				INSERT INTO mon_schema.tables_client(
					initiale_client
					)
				VALUES (
					$a$ || quote_literal(left(i, 1)) || $a$
					-- Les quotes permettent d'utiliser la variable i en la concaténant à la requête
					-- On ne récupère que la première lettre de la valeur de la variable
				)
			$a$ ;
			-- Ainsi, on insère l'initiale de chaque client dans la colonne initiale_client
		END LOOP;
		RETURN 'Le traitement est terminé';
		-- A la fin, on indique simplement que le traitement est terminé sans forcément renvoyer une valeur traitée.
	END;
$CORPS$
LANGUAGE PLPGSQL
;

Les erreurs : messages d’information

Génération des messages d’information

Lorsqu’il y a un problème, PostgreSQL vous renvoi une erreur. Il s’agit d’un message d’information.

Il est possible de générer ses propres messages d’information. Voici la syntaxe :

RAISE niveau 
	action_a_remplacer			-- Facultatif
	USING mon_detail = 'Mon message' 	-- Facultatif
;

Avec niveau :

• DEBUG : l’erreur est stockée dans le log de PostgreSQL.
• LOG : l’erreur est stockée dans le log de PostgreSQL.
• INFO : l’erreur est stockée dans le log de PostgreSQL.
• NOTICE : l’erreur est stockée dans le log de PostgreSQL et un avertissement est envoyé au client.
• WARNING : l’erreur est stockée dans le log de PostgreSQL et un avertissement est envoyé au client.
• EXCEPTION : Le code est stoppé et un avertissement est envoyé au client. Valeur par défaut.

Avec action_a_remplacer, une erreur PostgreSQL à remplacer :

• Un nom d’erreur PostgreSQL : invalid_parameter_value
• Un code d’erreur PostgreSQL : SQLSTATE '22023'

Avec mon_detail :

• MESSAGE : un message à renvoyer.
• DETAIL : le détail du message (s’il n’est pas assez clair).
• HINT : un indice sur la résolution de l’erreur.
• ERRCODE : code d’erreur.
• COLUMN : objet de l’erreur (le cas échéant).
• CONSTRAINT : objet de l’erreur (le cas échéant).
• DATATYPE : objet de l’erreur (le cas échéant).
• TABLE : objet de l’erreur (le cas échéant).
• SCHEMA : objet de l’erreur (le cas échéant).

Voici un exemple qui permet de remonter des informations dans votre fonction sans pour autant la stopper :

-- Retourner une information
RAISE NOTICE USING MESSAGE 'Voici la valeur de votre variable : ' || une_variable_de_la_fonction;

Attraper les messages d’information

Il est possible de traiter les messages d’information générés par votre code.

Le fonctionnement est identique à un CASE..WHEN.
Le bloc doit se placer dans un bloc BEGIN..END et juste avant le mot clé END.

EXCEPTION 
	WHEN mon_erreur
	-- Nom ou code d'erreur 
		THEN ...
		-- Code à exécuter si l'erreur se produit.
;

Avec mon_erreur (au choix) :

• Nom de l’erreur, par exemple : invalid_parameter_value
• Code de l’erreur, par exemple : SQLSTATE '22023'
• Le code d’une classe pour attraper toutes les erreurs de celle-ci, par exemple : SQLSTATE '22000'
• OTHERS : n’importe quel code erreur

Vous trouverez ici la liste des erreurs avec leurs codes.

Le code à exécuter lorsque l’erreur se produit est libre et peut être :

• Une requête d’INSERT pour écrire cette information dans une table.
• Rien : on passe l’erreur sous silence.
• Un RETURN pour renvoyer une valeur et terminer la fonction.
• Un RAISE pour renvoyer un message d’erreur.
• Un mélange des éléments précédents.

Notez qu’après le THEN, les variables suivantes permettent d’obtenir des information sur l’erreur qui a été attrapée :

• SQLSTATE : code de l’erreur
• SQLERRM : message de l’erreur

Voici un exemple de message personnalisé

EXCEPTION
	WHEN OTHERS 
		THEN RAISE EXCEPTION USING MESSAGE = CHR(10) || 'Code erreur : ' || SQLSTATE || CHR(10) || 'Message : ' || SQLERRM ;

Un autre moyen d’obtenir des information est de déclarer une variable que vous utiliserez dans le message de retour et à laquelle vous allez assigner une valeur avec la commande GET STACKED DIAGNOSTICS. En effet, cette commande permet de remonter les éléments suivants :

• RETURNED_SQLSTATE : code d’erreur
• MESSAGE_TEXT : le texte du message principal
• PG_EXCEPTION_DETAIL : le texte du message détaillé (si disponible)
• PG_EXCEPTION_HINT : le texte du message d’astuce (si disponible)
• PG_EXCEPTION_CONTEXT : ligne(s) de texte décrivant la pile d’appel au moment de l’exception
• SCHEMA_NAME : le nom du schéma
• TABLE_NAME : le nom de la table
• COLUMN_NAME : le nom de la colonne en relation
• CONSTRAINT_NAME : le nom de la contrainte en relation
• PG_DATATYPE_NAME : le nom du type de données

Voici comment l’utiliser :

DO
$corps$
DECLARE 
	-- Info erreur
	v_erreur_code text;
	v_erreur_msg text;
	v_erreur_msg_detail text;
	v_erreur_astuce text;
	v_erreur_context text;
	v_erreur_schema text;
	v_erreur_table text;
	v_erreur_colonne text;
	v_erreur_contrainte text;
BEGIN
	--Code de la fonction générant une erreur à propos d'une contrainte (par exemple, application d'une contrainte CHECK sur une colonne dont les valeurs ne répondent pas à la contrainte).

	EXCEPTION
		WHEN '23000' THEN 
			GET STACKED DIAGNOSTICS v_erreur_code = RETURNED_SQLSTATE;
			GET STACKED DIAGNOSTICS v_erreur_msg = MESSAGE_TEXT;
			GET STACKED DIAGNOSTICS v_erreur_msg_detail = PG_EXCEPTION_DETAIL;
			GET STACKED DIAGNOSTICS v_erreur_astuce = PG_EXCEPTION_HINT;
			GET STACKED DIAGNOSTICS v_erreur_context = PG_EXCEPTION_CONTEXT;
			GET STACKED DIAGNOSTICS v_erreur_schema = SCHEMA_NAME;
			GET STACKED DIAGNOSTICS v_erreur_table = TABLE_NAME;
			GET STACKED DIAGNOSTICS v_erreur_colonne = COLUMN_NAME;
			GET STACKED DIAGNOSTICS v_erreur_contrainte = CONSTRAINT_NAME;

			RAISE EXCEPTION USING MESSAGE = chr(10) ||
				'Une erreur s''est produite lors de l''application de la contrainte suivante : ' || v_erreur_contrainte || chr(10) ||
				'Vérifiez la table : ' || quote_ident(v_erreur_schema) || '.' || quote_ident(v_erreur_table) || CHR(10) ||
				'Erreur d''origine : ' || v_erreur_code || ' - ' || v_erreur_msg || CHR(10) ||
				'Contexte : ' || v_erreur_context
			;

END
$corps$

Ce qui devrait vous remonter ceci :

Une erreur s'est produite lors de l'application de la contrainte suivante : cana_materiau_check_test
Vérifiez la table : mon_schema.ma_table
Erreur d'origine : 23514 - la contrainte de vérification « ma_contrainte_xxx » est rompue par une ligne
Contexte : instruction SQL « ALTER TABLE mon_schema.ma_table ADD CONSTRAINT ma_contrainte_xxx CHECK (ma_colonne LIKE 'TYPE_DE_VALEUR_%') »

Trigger et fonction trigger

Une fonction trigger est une fonction classique qui va être déclenchée lors d’une modification de la donnée. Pour déclencher cette fonction spécifique, il faut un déclencheur : « trigger » en anglais.

Trigger

Voici les modifications pendant lesquelles le trigger peut se déclencher :

• INSERT
• UPDATE
• DELETE
• TRUNCATE

Pour chaque modification, il est possible de choisir le moment du déclenchement :

• BEFORE : avant la modification
• AFTER : après la modification
• INSTEAD OF : à la place de la modification (on ne fait pas la modification)

Il est également possible de choisir le mode de déclenchement :

• FOR EACH ROW : pour chaque ligne modifiée
• FOR EACH STATEMENT : pour chaque requête (une requête pouvant modifier plusieurs lignes)

Voici la syntaxe de création du trigger :

CREATE TRIGGER mon_trigger
 	-- On choisit le moment de lancement du trigger
	BEFORE UPDATE OR INSERT
 	-- On indique quelle table déclenchera le trigger
	ON mon_schema.ma_table_sur_laquelle_on_execute_le_trigger
 	-- On indique le mode de déclenchement
	FOR EACH ROW
 	 	-- On indique la fonction trigger à lancer
		EXECUTE FUNCTION ma_fonction_trigger()
;

Fonction trigger

Du fait du moment particulier pendant lequel la fonction trigger est exécutée, plusieurs variables sont disponibles :

• TG_NAME : nom du trigger lancé.
• TG_WHEN : 'BEFORE' ou 'AFTER' selon le moment du lancement.
• TG_LEVEL : 'ROW' ou 'STATEMENT' selon la définition du trigger.
• TG_OP : 'INSERT', 'UPDATE' ou 'DELETE' opération sur laquelle porte le trigger
• TG_TABLE_NAME : Nom de la table qui a causé le déclenchement.
• TG_TABLE_SCHEMA : Nom du schéma de la table qui a appelé le trigger.
• OLD : Uniquement en UPDATE et en DELETE. Tableau des anciennes valeurs des colonnes de la table déclenchant le trigger. Par exemple OLD.ma_colonne
• NEW : Uniquement en UPDATE et en INSERT. Tableau des nouvelles valeurs des colonnes de la table déclenchant le trigger. Par exemple NEW.ma_colonne

Attention, les valeurs du tableau OLD ne sont jamais modifiables. Les valeurs du tableau NEW sont modifiables mais ne seront appliquées que si elles sont modifiées dans un trigger BEFORE : avant l’INSERT ou l’UPDATE.

Voici la syntaxe d’une fonction trigger :

CREATE OR REPLACE FUNCTION mon_schema.ma_fonction_trigger()
RETURNS TRIGGER AS
	-- On indique qu'on renvoi un trigger

$corps$
	BEGIN
		INSERT into mon_schéma.ma_table_historique 
			(utilisateur, date_maj, ancienne_valeur, nouvelle_valeur)
		VALUES
			(current_user, now(), OLD.nom, NEW.nom);

		RETURN NEW;
		-- On retourne les valeur du tableau NEW pour qu'elles puissent être exploitées
	END
$corps$

LANGUAGE plpgsql
;

Voici un exemple de modification des valeurs du tableau NEW :

CREATE OR REPLACE FUNCTION mon_schéma.ma_fonction_trigger()
RETURNS TRIGGER AS

$corps$
	BEGIN
		IF	NEW.mon_champ_1 IS NULL
			THEN NEW.mon_champ_1 = 'valeur_defaut';
		END IF;

		NEW.mon_champ_2 = 'valeur_fixe'

		RETURN NEW;
	END
$corps$

LANGUAGE plpgsql
;

DO

Le DO permet de lancer des procédures sans avoir à les stocker : sans créer de fonction.

Pour l’utiliser, utilisez la syntaxe suivante :

DO
	$CORPS$
		DECLARE
			variable_1 text;

		BEGIN
			ma procédure XXX
		END

	$CORPS$
;

Quelques fonctions utiles

Voici quelques fonctions qui peuvent être utiles (cette rubrique va se remplir au fur et à mesure des fonctions que j’ajoute sur le site).

Les concepts clés

Les paramètres : IN, OUT et INOUT

Les paramètres permettent d’indiquer comment seront utilisés les arguments :

• IN (par défaut) : l’argument sera utiliser en entrée et pourra ainsi être appelé dans la fonction.
• OUT : l’argument sera renvoyé en sortie, c’est le résultat de la fonction. Lorsque OUT est utilisé, il remplace RETURNS mon_type dans le corps de la fonction.
• INOUT : l’argument sera utilisé en entré et en sortie : on l’appellera dans le corps de la fonction et il sera remplacé par le résultat final.

Return

Le RETURN permet de renvoyer le résultat de la fonction (en général vers l’afficheur). Plusieurs types existent selon le type de retour :

Pour le renvoi d’une seule ligne (c’est le cas des valeurs uniques ou mais on peut l’avoir sous forme de table) :

• RETURN ma_valeur : la fonction renvoie la valeur puis s’arrête, toute action décrite après ce RETURN est omise.

Pour le renvoi de plusieurs lignes (SETOF…) :

• RETURN NEXT : la fonction stocke la valeur puis continu à s’exécuter.
• RETURN QUERY : la fonction stocke le résultat d’une requête puis continu à s’exécuter.
• RETURN QUERY EXECUTE : Comme RETURN QUERY sauf que la requête peut contenir des paramètres afin d’être dynamique.
• RETURN : ce mot clé est utilisé en toute fin de fonction lorsqu’il faut renvoyer les valeurs stockées par les RETURN NEXT ou RETURN QUERY précédents. Ce mot clé peut être omis et la fonction renverra les valeurs stockées lorsque son exécution sera terminée.

Les délimiteurs

Le code d’une fonction est écrit comme un texte littéral à exécuter. Il est donc entre apostrophe comme n’importe quelle constante de type texte. Ce type de quotation impose l’échappement de chaque caractère spécifique (le doublement de chaque apostrophe et de chaque backslash « \ » utilisé au sein de la fonction par exemple).

Il est cependant possible d’utiliser un autre type de quotation bien plus pratique : la quotation dollar.

Il s’agit simplement de remplacer l’apostrophe initial et l’apostrophe final par une suite de deux sigles dollar $$. Vous n’aurez ainsi pas besoin de doubler certains caractères spécifiques. Là où ça devient intéressant, c’est que si vous pouvez ainsi créer vos propres signes de quotation. Ceux-ci doivent être composé du sigle « $ » suivi d’un tag (suite de caractère alphanumérique de votre choix) puis finir par le sigle « $ ». Voici un exemple $fonction$.

Ce principe est valable pour les fonctions mais également pour n’importe quelle requête SQL (sous PostgreSQL bien entendu).

La volatilité de la fonction

Il est possible d’indiquer la classification volatile de la fonction au sein de celle-ci. C’est un renseignement pour l’optimiseur de requête qui le renseigne sur le fonctionnement de la fonction.

On déclare cet élément juste après le langage de la fonction (à la fin donc) avec l’une des valeurs suivantes en fonction de ce que renverra la fonction :

• IMMUTABLE : cette fonction ne modifie pas la base de donnée et renvoie une valeur indépendante du contenu de la base de données. On l’utilise lorsque la fonction ne lit pas le contenu de la base ou lorsqu’elle lit des données qui n’évolueront jamais.
• STABLE : cette fonction ne modifie pas la base de donnée. En revanche, elle peut lire les données et renvoie une valeur dépendante de celles-ci. Le résultat est toujours le même si l’argument en entrée est le même.
• VOLATILE (par défaut) : cette fonction peut modifier la base de données. Elle peut également renvoyer une valeur différente pour un même argument utilisé deux fois de suite. Cette fonction ne peut donc pas être optimisée.

Table temporaire

Dans une fonction, il est possible d’utiliser des variables contenant plusieurs colonnes (type record).

Il est également possible d’utiliser des boucles pour traiter toutes les lignes issues d’une requête.

Cependant, il n’est pas possible de stocker un ensemble de plusieurs lignes dans une variable pour ensuite les traiter.

Pour cela, il faut utiliser une table temporaire. Pour cela, je vous conseille de créer des tables temporaires puis de les supprimer. L’intérêt des tables temporaire est qu’elles ne sont pas visibles des autres utilisateur de la BDD et qu’elles sont automatiquement supprimées lorsque l’utilisateur met fin à sa session (lorsqu’il se déconnecte).

Voici comment faire :

CREATE TEMPORARY TABLE mon_schema.ma_table (
	colonne_1 text,
colonne_2 text
);

Puis supprimez la simplement avec :

DROP TABLE mon_schema.ma_table;

---

Je vais maintenant vous proposer quelques fonctions et vues qui vont vous aider à gérer votre base de données.

Sommaire général

Voici le sommaire général du cours :

Cet article vous a plu ?

N'hésitez pas à le partager, il interessera surement certains de vos contacts.

Les thèmes suivants contiennent des articles en lien avec celui-ci, allez faire un tour :

BDDPostgreSQLProgrammationSQL tuto