UDFs SQL scalaires

Ce chapitre couvre des concepts et des détails d’utilisation spécifiques aux UDFs SQL (fonctions définies par l’utilisateur).

Pour une introduction aux UDFs, et pour des informations qui s’appliquent à tous les types d’UDFs, voir Aperçu des UDFs.

Dans ce chapitre :

Utilisation générale

Un UDF SQL évalue une expression arbitraire SQL et retourne les résultats de l’expression.

La définition de la fonction peut être une expression SQL qui retourne soit une valeur scalaire (c’est-à-dire unique), soit, si elle est définie comme fonction de table, un ensemble de lignes. Par exemple, voici un exemple de base d’un UDF scalaire qui calcule l’aire d’un cercle :

CREATE FUNCTION area_of_circle(radius FLOAT)
  RETURNS FLOAT
  AS
  $$
    pi() * radius * radius
  $$
  ;
SELECT area_of_circle(1.0);

Sortie :

+---------------------+
| AREA_OF_CIRCLE(1.0) |
|---------------------|
|         3.141592654 |
+---------------------+

L’expression peut être une expression de requête (une expression SELECT). Par exemple :

CREATE FUNCTION profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
    SELECT SUM((retail_price - wholesale_price) * number_sold) FROM purchases
  $$
  ;

Lorsque vous utilisez une expression de requête dans un UDF SQL, vous n’avez pas besoin d’inclure un point-virgule dans le corps de l’UDF pour terminer l’expression de la requête.

Vous ne pouvez inclure qu’une seule expression de requête. L’expression peut inclure UNION [ALL].

Note

Bien que le corps d’un UDF puisse contenir une instruction SELECT complète, il ne peut pas contenir d’instructions DDL ni toute instruction DML autre que SELECT.

Exemples

Exemple(s) UDF scalaire SQL de base

Cet exemple renvoie une approximation codée en dur de la constante mathématique pi.

CREATE FUNCTION pi_udf()
  RETURNS FLOAT
  AS '3.141592654::FLOAT'
  ;
SELECT pi_udf();   

Sortie :

+-------------+
|    PI_UDF() |
|-------------|
| 3.141592654 |
+-------------+

Exemples SQL courants

Cet exemple utilise une expression de requête (c’est-à-dire une instruction SELECT) :

Créez la table et les données à utiliser :

CREATE TABLE purchases (number_sold INTEGER, wholesale_price NUMBER(7,2), retail_price NUMBER(7,2));
INSERT INTO purchases (number_sold, wholesale_price, retail_price) VALUES 
   (3,  10.00,  20.00),
   (5, 100.00, 200.00)
   ;

Créez l’UDF :

CREATE FUNCTION profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
    SELECT SUM((retail_price - wholesale_price) * number_sold) FROM purchases
  $$
  ;

Appelez l’UDF dans une requête :

SELECT profit();

Sortie :

+----------+
| PROFIT() |
|----------|
|   530.00 |
+----------+

Cet exemple utilise l’UDF dans une clause WITH :

CREATE TABLE circles (diameter FLOAT);

INSERT INTO circles (diameter) VALUES
    (2.0),
    (4.0);

CREATE FUNCTION diameter_to_radius(f FLOAT) 
  RETURNS FLOAT
  AS 
  $$ f / 2 $$
  ;
WITH
    radii AS (SELECT diameter_to_radius(diameter) AS radius FROM circles)
  SELECT radius FROM radii
    ORDER BY radius
  ;

Sortie :

+--------+
| RADIUS |
|--------|
|      1 |
|      2 |
+--------+

Cet exemple utilise une requête plus complexe, qui inclut une opération JOIN :

Créez la table et les données à utiliser :

CREATE TABLE orders (product_ID varchar, quantity integer, price numeric(11, 2), buyer_info varchar);
CREATE TABLE inventory (product_ID varchar, quantity integer, price numeric(11, 2), vendor_info varchar);
INSERT INTO inventory (product_ID, quantity, price, vendor_info) VALUES 
  ('X24 Bicycle', 4, 1000.00, 'HelloVelo'),
  ('GreenStar Helmet', 8, 50.00, 'MellowVelo'),
  ('SoundFX', 5, 20.00, 'Annoying FX Corporation');
INSERT INTO orders (product_id, quantity, price, buyer_info) VALUES 
  ('X24 Bicycle', 1, 1500.00, 'Jennifer Juniper'),
  ('GreenStar Helmet', 1, 75.00, 'Donovan Liege'),
  ('GreenStar Helmet', 1, 75.00, 'Montgomery Python');

Créez l’UDF :

CREATE FUNCTION store_profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
  SELECT SUM( (o.price - i.price) * o.quantity) 
    FROM orders AS o, inventory AS i 
    WHERE o.product_id = i.product_id
  $$
  ;

Appelez l’UDF dans une requête :

SELECT store_profit();

Sortie :

+----------------+
| STORE_PROFIT() |
|----------------|
|         550.00 |
+----------------+

La rubrique CREATE FUNCTION contient des exemples supplémentaires.

Utilisation d’UDFs dans différentes clauses

Un UDF scalaire peut être utilisé partout où une expression scalaire peut être utilisée. Par exemple :

-- ----- These examples show a UDF called from different clauses ----- --

select MyFunc(column1) from table1;

select * from table1 where column2 > MyFunc(column1);

Utilisation des variables SQL dans un UDF

Cet exemple montre comment définir une variable SQL et utiliser cette variable dans un UDF :

SET id_threshold = (SELECT COUNT(*) FROM table1) / 2;
CREATE OR REPLACE FUNCTION my_filter_function()
RETURNS TABLE (id int)
AS
$$
SELECT id FROM table1 WHERE id > $id_threshold
$$
;