Comment créer une fonction définie par l'utilisateur postgresql

«Lorsqu'il nécessite l'exécution de nombreuses instructions SQL plusieurs fois, il est préférable d'écrire une fonction ou une procédure postgresql définie par l'utilisateur avec ces instructions SQL. Il supprime le code en double et réutilise le code en appelant la fonction à partir de différentes applications. La façon de créer et d'utiliser différents types de fonctions définies par l'utilisateur de PostgreSQL a été affichée dans ce tutoriel."

Syntaxe

La syntaxe de la fonction postgresql est donnée ci-dessous.

Créer ou remplacer function function_name (argument_list)
Return return_type comme $ variable_name $
DÉCLARER
déclaration;
[…]
COMMENCER

[…]
Return variable_name | valeur
FIN;
Langue PLPGSQL;

• Créer un mot clé est utilisé pour créer une nouvelle fonction définie par l'utilisateur et remplacer le mot clé est utilisé pour modifier la fonction existante.
• Le nom de la fonction est défini par le nom de la fonction, et la fonction peut prendre un ou plusieurs arguments. Trois types d'arguments peuvent être utilisés dans la fonction. Ceux-ci sont DANS, DEHORS, et Interruption. L'argument par défaut de la fonction est DANS argument. Il est utilisé pour passer la valeur dans la fonction, et il ne peut pas être modifié à l'intérieur de la fonction. Le DEHORS L'argument est utilisé pour renvoyer la valeur de la fonction. Le Interruption L'argument peut être utilisé pour transmettre la valeur à la fonction et retourner la valeur de la fonction. Il peut être réaffecté à l'intérieur de la fonction comme la sortie argument.
• Le type de données de la variable qui sera renvoyé par la fonction est mentionné après le mot-clé Retour.
• Le corps de la fonction commencera par le mot-clé Declare, où les variables nécessaires seront déclarées. Ensuite, les instructions SQL requises seront définies à l'intérieur des blocs de début et de fin.
• Le nom du langage procédural est défini après le mot-clé du langage dans lequel la fonction sera exécutée.

Conditions préalables

UN. Installez le postgresql

Vous devez installer la dernière version des packages PostgreSQL sur le système d'exploitation Linux avant d'exécuter les instructions SQL affichées dans ce tutoriel. Exécutez les commandes suivantes pour installer et démarrer le postgresql:

$ sudo apt-get -y installer postgresql postgresql-contrib
$ sudo systemctl start postgresql.service

Exécutez la commande suivante pour vous connecter à PostgreSQL avec le privilège racine.

$ sudo -u postgres psql

B. Vous pouvez créer une base de données nommée «TestDB» En exécutant l'instruction SQL suivante.

# Créer une base de données testdb;

Si vous souhaitez créer une table dans le testdb base de données, alors vous devez taper «\ C» Pour modifier la base de données. Mais dans ce didacticiel, je n'ai pas modifié la base de données et la table, et toutes les fonctions ont été créées dans la base de données par défaut nommée postgres.

C. Créez une table nommée des produits avec quatre champs et insérez quatre enregistrements dans le tableau pour tester les fonctions définies par l'utilisateur qui seront créées plus tard dans ce tutoriel.

# Créer des produits de table (
Clé primaire en série ID,
Nom Varchar (30),
Type Varchar (20),
prix de l'argent);

D. Insérez quatre enregistrements dans la table.

# Insérer dans les produits (nom, type, prix)
Valeurs («Samsung A40», «Mobile», 300),
(«A4», «souris», 20),
(«Sony 42» »,« TV », 1000),
(«Samsung 3tb», «disque dur», 600);

Différents exemples de fonctions définies par l'utilisateur de PostgreSQL

Les utilisations des fonctions définies par l'utilisateur avec argument et sans argument dans PostgreSQL ont été montrées ci-dessous en utilisant plusieurs exemples.

Exemple-1: fonction définie par l'utilisateur sans argument et valeur de retour

Exécutez l'instruction SQL suivante pour créer une fonction nommée function1 () sans aucun argument, et la fonction ne renverra rien. La fonction mettra à jour l'enregistrement du tableau des produits où la valeur de l'ID est 3 après l'exécution.

# Créer ou remplacer la fonction fonction1 ()
Revient vide comme
$$
COMMENCER
--Mettez à jour la valeur de prix du troisième enregistrement
Mettre à jour les produits Set Price = 1500 où id = 3;
FIN;
$$
Langue «plpgsql»;

La sortie suivante apparaîtra si la fonction est créée avec succès dans le postgres base de données.

Exécutez l'instruction SQL suivante pour lire tous les enregistrements du des produits table avant l'exécution function1 ().

# Select * dans les produits;

La sortie suivante montre les enregistrements insérés du des produits tableau.

Exécutez l'instruction SQL suivante pour exécuter la fonction.

# Select function1 ();

La sortie suivante montre qu'une ligne est affectée en appelant la fonction function1 (). La valeur du champ de prix du tableau sera mise à jour où la valeur de l'identification est 3.

Exécutez l'instruction SQL suivante pour lire tous les enregistrements du des produits table après l'exécution function1 ().

# Select * dans les produits;

La sortie suivante montre que la valeur de prix du tableau des produits a été mise à jour où la valeur d'ID est de 3, et il est maintenant de 1500.

Exemple-2: fonction définie par l'utilisateur avec l'argument

L'argument par défaut de la fonction est en argument. Exécutez l'instruction SQL suivante pour créer une fonction nommée function2 () avec deux arguments, et la fonction ne retournera rien. La fonction inséra un nouvel enregistrement au des produits table avec les valeurs d'argument.

# Créer ou remplacer la fonction fonction2 (nom varchar, type varchar, prix int)
Revient vide comme
$$
COMMENCER
--Insérer un nouveau record
Insérer dans les produits (nom, type, prix)
Valeurs (nom, type, prix);
FIN;
$$
Langue «plpgsql»;

La sortie suivante apparaîtra si la fonction est créée avec succès dans le postgres base de données.

Exécutez l'instruction SQL suivante pour exécuter la fonction trois dans les valeurs d'argument. Ce sont «iPhone», «mobile» et 2000.

# Select function2 ('iPhone', 'mobile', 2000);

La sortie suivante montre qu'une ligne est affectée en appelant la fonction function2 (), Et un nouveau record sera inséré dans la table.

Exécutez l'instruction SQL suivante pour lire tous les enregistrements du des produits table après l'exécution function2 ().

# Select * dans les produits;

La sortie suivante montre qu'un nouvel enregistrement a été inséré dans le des produits tableau.

Exemple-3: fonction définie par l'utilisateur avec argument OUT

L'argument Out est utilisé pour renvoyer la valeur de la fonction. Exécutez l'instruction SQL suivante pour créer une fonction nommée function3 () avec un argument de type argent, et la fonction renverra les données du type d'argent. La fonction renverra la valeur attribuée de l'argument OUT.

# Créer ou remplacer la fonction fonction3 (prix de l'argent)
Retourne de l'argent comme
$$
COMMENCER
--Attribuer une valeur à l'argument
Prix: = 1000;
FIN;
$$
Langue «plpgsql»;

La sortie suivante apparaîtra si la fonction est créée avec succès dans le postgres base de données.

Exécutez l'instruction SQL suivante pour supprimer les enregistrements de la table de produits en fonction de la valeur renvoyée par function3 ().

# Supprimer des produits où le prix <= function3();

La sortie suivante montre que trois lignes sont affectées en appelant la fonction function3 (). La valeur renvoyée de la fonction est de 1000, ce qui correspond aux trois enregistrements du des produits table où le identifiant les valeurs sont 1, 2, et 4.

Exécutez l'instruction SQL suivante pour lire tous les enregistrements du des produits table après l'exécution function3 (). La sortie montre que deux enregistrements existent dans le tableau où le prix Les valeurs sont supérieures à 1000.

# Select * dans les produits;

La sortie suivante montre que 3 enregistrements ont été supprimés du des produits tableau.

Exemple-4: fonction définie par l'utilisateur avec argument InOut

L'argument InOut est utilisé dans la fonction pour transmettre des valeurs dans la fonction et les valeurs de retour de la fonction. Exécutez l'instruction SQL suivante pour créer une fonction nommée calculer() avec un argument inout de type de flotteur et un argument de type entier. La fonction renvoie les données du type de flotteur. La fonction sera exécutée avec le montant des ventes, qui sera une valeur flottante, et la remise, qui sera un entier. Le montant des ventes après la remise sera calculé en fonction des valeurs d'argument dans. Ensuite, le montant des ventes calculé sera renvoyé de la fonction en utilisant l'argument OUT.

# Créer ou remplacer la fonction Calculer (
Sales_amount inout float, remise int)
Retourne flotter comme
$$
COMMENCER
--Calculez le montant de la réduction
Sales_amount: = Sales_amount - (Sales_amount * Discount / 100);
FIN;
$$
Langue «plpgsql»;

La sortie suivante apparaîtra si la fonction est créée avec succès dans le postgres base de données.

Exécutez l'instruction suivante pour exécuter la fonction avec 10000 comme montant de vente et 5 comme montant de remise

#Select Calculer (10000, 5) comme remise_amount;

Le 5% de 10000 est de 500 et 10000-500 = 9500. La production suivante montre le montant des ventes après une remise de 5%.

Exemple-5: utilisateur-Dfine Fonction avec le Énoncé conditionnel

Exécutez l'instruction SQL suivante pour créer une fonction nommée search_product () avec un argument, et la fonction renvoie les données du type varchar. La manière de déclarer la variable de fonction et de l'utilisation de l'instruction conditionnelle a été affichée dans cette fonction. La variable de fonction nommée Product_name a été défini avec le Nom type du des produits tableau. La valeur de l'argument, tp, sera adapté à la valeur du taper champ du des produits tableau. Si aucune correspondance n'est trouvée dans le tableau, une erreur sera augmentée avec le message, «le type de produit n'existe pas». Si une correspondance est trouvée, alors la valeur du Nom Le champ de ce type de produit sera stocké dans la variable de fonction et la variable sera renvoyée de la fonction.

# Créer ou remplacer la fonction Search_Product (TP Varchar)
Renvoie Varchar comme
$$
DÉCLARER
Product_name Products.Nom% type;
COMMENCER
-- Rechercher le nom du produit en fonction du type
Sélectionnez le nom dans Product_name
Des produits
Où les produits.Type = tp;
Si cela n'est pas trouvé, alors
Le type de produit augmente n'existe pas.';;
FIN SI;
Return product_name;
FIN;
$$
Langue «plpgsql»;

La sortie suivante apparaîtra si la fonction est créée avec succès dans le postgres base de données.

Exécutez l'instruction SQL suivante pour lire tous les enregistrements du tableau des produits.

# Select * dans les produits;

Exécutez l'instruction SQL suivante pour exécuter la fonction search_product () avec le type de produit "Mobile".

# Sélectionner search_product ('mobile');

Exécutez l'instruction SQL suivante pour exécuter la fonction search_product () avec le type de produit "CA".

# Sélectionnez search_product ('AC');

La sortie suivante montre que le des produits Le tableau contient deux enregistrements de valeurs de type, «la télé" et "Mobile". La sortie de la première exécution de la fonction a renvoyé «iPhon» car le type de produit «mobile» existe dans le tableau. La sortie de la deuxième exécution de la fonction a renvoyé un message d'erreur car le type de produit «AC» n'existe pas dans le tableau.

Exemple-6: fonction définie par l'utilisateur avec une fonction agrégée

Toute fonction agrégée prise en charge par SQL peut être utilisée dans la fonction PostgreSQL. Exécutez l'instruction SQL suivante pour créer une fonction nommée count_product () sans aucun argument, et la fonction renverra les données de type entier. La fonction d'agrégat Count () a été utilisée dans la fonction utilisée pour compter le nombre de lignes en fonction de la condition spécifique. Ici, une variable de fonction nommée total_product a été déclarée dans la fonction pour stocker la valeur de retour de la fonction Count (). La fonction Count () a été utilisée dans la requête sélectionnée pour compter les enregistrements où le nom du produit commence par le mot «Sony».

# Créer ou remplacer la fonction count_product ()
Retourne int comme
$$
DÉCLARER
total_product int;
COMMENCER
--Compter le nombre total du produit particulier
Sélectionnez le nombre (*) dans Total_Product à partir des produits
Où le nom comme «Sony%»;
Return total_product;
FIN;
$$
Langue PLPGSQL;

La sortie suivante apparaîtra si la fonction est créée avec succès dans le postgres base de données.

Exécutez la commande suivante pour exécuter la fonction, count_product ().

# Select count_product ();

La sortie suivante montre que la fonction count () renvoie la valeur 1 car il n'y a qu'un seul enregistrement qui correspond à la valeur, "Sony".

Conclusion

Le but de l'utilisation de fonctions postgresql et de différentes façons d'utiliser des fonctions postgresql avec des arguments in, out et inout a été décrit dans ce tutoriel en créant plusieurs fonctions postgresql. L'argument in est utilisé pour prendre de la valeur dans la fonction. L'argument Out est utilisé pour renvoyer la valeur de la fonction. L'argument InOut est utilisé pour intégrer la valeur d'entrée dans la fonction et renvoyer la sortie de la fonction. La fonction peut être créée sans aucun argument également. J'espère que l'utilisateur pourra créer et utiliser correctement la fonction PostgreSQL après avoir lu ce tutoriel.