PostgreSQL - Fonctions

Comme dans la plupart des bases de données, on peut enchaîner une série de phrases SQL et les traiter comme une unité d'un bloc fonctionnel; les différentes bases de données décrivent cette opération avec différents noms, procédures stockées, modules, macros, etc.

Dans PostgreSQL elles sont appelées fonctions. En plus d'unifier plusieurs instructions SQL, ces fonctions nous donnent également la possibilité d'exécuter des instructions SQL à l'aide de langages procéduraux (PL).

Dans PostgreSQL nous avons plusieurs options pour cela et la possibilité de les étendre.

1. Fonctions PostgreSQL

Anatomie d'une fonction
Quel que soit le langage utilisé pour écrire les fonctions, elles ont une structure, cette structure peut être synthétisée avec :

 CRÉER OU REMPLACER UNE FONCTION nom_fonction (arg1_arg1typededonnées) RETOURNE un_type / setoff un certaintype / TABLE / (…) / AS $$ BODY off fonction $$ LANGUAGE language_of_function

Si nous décrivons ce que nous voyons, c'est assez simple, CRÉER OU REMPLACER UNE FONCTION est la clause de création de fonction, func_name est le nom qu'aura arg1 est le paramètre qu'il recevra et arg1_datatype est le type de données qui est dit paramètre, c'est-à-dire s'il s'agit d'un entier, d'une chaîne, etc. Dans RETOUR On retourne le résultat de notre fonction, $$ est le début du bloc qui va céder la place au corps de la fonction puis se termine pareil avec $$ et enfin LANGUE permet de spécifier le langage dans lequel la fonction est écrite.

C'est une structure bien définie et assez lisible, nous ne devrions donc pas avoir de problèmes pour créer nos fonctions.

Écriture de fonctions avec SQL
Écrivez les fonctions en utilisant SQL C'est assez simple et rapide, il s'agit de prendre nos instructions SQL en gros et d'y ajouter l'en-tête et le pied de page des fonctions et nous avons terminé.

Cependant, comme tout le reste, cela se fait au prix de certains sacrifices, par exemple nous perdons la flexibilité que si nous pouvions travailler avec un autre langage pour créer plus de branches de contrôle d'exécution conditionnel, nous ne pouvons pas avoir plus d'une instruction SQL, bien que cela puisse être résolu en utilisant diverses méthodes.

Le plus grand avantage est que puisque SQL est le planificateur PostgreSQL nous permet de profiter des index et ainsi d'accélérer son exécution, par contre avec d'autres langages la fonction sera toujours une boite noire

Regardons maintenant une fonction écrite avec SQL :

 CRÉER OU REMPLACER LA FONCTION ins_logs (param_user_name varchar, param_description text) RETOURNE l'entier AS $$ INSERT INTO dans les journaux (user_name, description) VALUES ($ 1, $ 2) RETOUR log_id; $$ LANGUE 'sql' VOLATILE;

Nous voyons que nous suivons la structure définie ci-dessus et à la fin dans la section LANGUE nous définissons la clause 'sql' VOLATIL Qu'elle soit propriétaire signifie que la fonction peut renvoyer quelque chose de différent à chaque appel qui lui est fait, même si elle reçoit les mêmes paramètres. Ensuite, pour appeler notre fonction, nous pouvons utiliser:

 SELECT ins_logs ('lhsu', 'ceci est un test') As new_id;

On fait une phrase SÉLECTIONNER, la fonction est active et ce qui retourne est ce que nous allons recevoir et dans ce cas nous verrons une valeur que nous appelons new_id et que la fonction retourne comme log_id.
Nous pouvons même utiliser une fonction pour effectuer une mise à jour d'enregistrement et renvoyer un paramètre void comme dans cet exemple :

 CRÉER OU REMPLACER LA FONCTION upd_logs (log_id integer, param_user_name varchar, param_description text) RETURNS void AS $$ UPDATE logs SET user_name = $ 2, description = $ 3, log_ts = CURRENT_TIMESTAMPWHERE log_id = $ 1; $$ LANGUATILE 'sql'' sql 'VOLATILE'

Étant vide, nous n'avons pas besoin d'un champ récepteur, nous l'exécutons donc comme suit :

 SELECT upd_logs (12, 'robe', 'Changer pour regina');

Ici, nous pouvons voir que nous avons supprimé la dernière étape de As new_id de l'appel précédent.
Avec cela, nous terminons ce tutoriel, nous pouvons maintenant faire nos fonctions de base dans SQL, facilitant et simplifiant ainsi de nombreuses activités dont nous pouvons avoir besoin dans un programme ou un système que nous construisons.

2. Fonctions PostgreSQL dans d'autres langages

L'une des caractéristiques les plus attrayantes de PostgreSQL est qu'il ne se limite pas seulement au langage SQLGrâce au chargement de modules, nous pouvons choisir d'incorporer des fonctionnalités avancées, notamment la possibilité d'utiliser différents langages pour créer des fonctions, ce qui nous permet d'obtenir une grande flexibilité en utilisant de meilleures capacités de génération de conditions et les avantages inhérents aux différents langages.

Écriture de fonctions avec PL/pgSQL
Au moment où l'on constate que le standard SQL est insuffisant pour les requêtes que l'on veut effectuer dans une fonction, on peut toujours recourir à l'utilisation de PL/pgSQL; l'une de ses différences et améliorations par rapport à SQL est que les variables locales peuvent être déclarées à l'aide de l'instruction DECLARE, nous pouvons également contrôler le flux et nous devons enfermer le corps de la fonction dans un bloc BEGIN END.

Voyons un exemple de fonction écrite dans ce langage :

 CREATE FUNCTION sel_logs_rt (param_user_name varchar) RETURNS TABLE (log_id int, user_name varchar (50), description text, log_ts timestamptz) AS $$ BEGIN RETURN QUERY SELECT log_id, user_name, description, log_ts FROM logs WHERE user_name = param_user_name FINIR; $$ LANGUE 'plpgsql' STABLE; 

Voyons maintenant comment écrire des fonctions avec Python.

Écrire des fonctions avec Python
Python est un langage de programmation assez propre, qui dispose d'un grand nombre de bibliothèques.
PostgreSQL est le seul moteur de base de données qui vous permet d'utiliser Python pour créer des fonctions.

Afin d'obtenir la possibilité de créer des fonctions avec Python, nous devons d'abord nous assurer que nous avons le langage installé sur notre serveur. Une fois que nous savons que nous l'avons installé, nous devons activer les extensions dans PostgreSQL à l'aide des commandes suivantes :

 CRÉER UNE EXTENSION plpython2u; CRÉER UNE EXTENSION plpython3u; 

Nous devons nous assurer que Python est opérationnel avant d'activer les extensions pour éviter les erreurs.

Fonctions de base avec Python
Une fois que tout est activé pour pouvoir utiliser Python, nous allons commencer à construire notre fonction, il est important de savoir que PostgreSQL peut convertir ses types de données en types de données Python et vice versa. PL / Python est même capable de renvoyer des tableaux et des types composés.

Voyons ci-dessous une fonction qui effectue une recherche de texte dans une ressource en ligne, ce qui ne pourrait pas être fait avec PL / pgSQL, dans l'image suivante nous verrons le code puis nous ferons l'explication correspondante.

• Nous importons les bibliothèques que nous allons utiliser.
• Nous effectuons la recherche sur le Web en concaténant les paramètres d'entrée de l'utilisateur.
• Nous lisons la réponse et l'enregistrons dans un fichier HTML appelé raw_html.
• Nous enregistrons la partie du code HTML qui commence par et se termine avant le début de.
• Nous supprimons les balises HTML et les espaces et sauvegardons à nouveau la variable appelée result.
• Nous renvoyons le résultat final.
• Une autre caractéristique intéressante de l'utilisation de Python est qu'on peut interagir directement avec le système d'exploitation, voyons une fonction qui fait un listing de répertoire, il faut noter que celui-ci doit être créé par un superutilisateur :

 CRÉER OU REMPLACER LA FONCTION list_incoming_files () RETOURNE SETOF text AS $$ import os return os.listdir ('/ entrant') $$ LANGUAGE 'plpython2u' VOLATILE SECURITY DEFINER;

A quoi ça sert ? Nous pouvons nous demander, car imaginez que nous voulons consulter les fichiers dont nous disposons dans un système, l'appel à la fonction serait quelque chose comme ceci :

 SELECT nom de fichier FROM list_incoming_files () As nom de fichier WHERE nom de fichier ILIKE '% .csv'

Avec cela, nous terminons ce tutoriel, nous gérons déjà la création de fonctions dans d'autres langages dans PostgreSQL, ce qui nous donne un champ infini pour répondre à nos exigences.