SQL-UDFs

Unter diesem Thema werden Konzepte und Nutzungsdetails behandelt, die für SQL-UDFs (benutzerdefinierte Funktionen) spezifisch sind.

Eine Einführung in UDFs und Informationen zu allen Arten von UDFs finden Sie unter Übersicht zu UDFs.

Unter diesem Thema:

Allgemeine Nutzung

Eine SQL-UDF wertet einen beliebigen SQL-Ausdruck aus und gibt die Ergebnisse des Ausdrucks zurück.

Die Funktionsdefinition kann ein SQL-Ausdruck sein, der entweder einen skalaren (d. h. einen einzelnen) Wert oder, wenn als Tabellenfunktion definiert, einen Satz von Zeilen zurückgibt. Hier ist ein einfaches Beispiel einer skalaren UDF, die die Fläche eines Kreises berechnet:

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

Ausgabe:

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

Der Ausdruck kann ein Abfrageausdruck sein (ein SELECT-Ausdruck). Beispiel:

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

Wenn Sie einen Abfrageausdruck in einer SQL-UDF verwenden, müssen Sie kein Semikolon in den UDF-Funktionsrumpf einschließen, um den Abfrageausdruck zu beenden.

Sie können nur einen Abfrageausdruck einschließen. Der Ausdruck kann UNION [ALL] enthalten.

Bemerkung

Obwohl der Funktionsrumpf einer UDF eine vollständige SELECT-Anweisung enthalten kann, darf er keine DDL-Anweisung oder DML-Anweisung außer SELECT enthalten.

Beispiele

Grundlegendes Beispiel für skalare SQL-UDF

Dieses Beispiel gibt eine hart codierte Approximation der mathematischen Konstante Pi zurück.

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

Ausgabe:

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

Häufige SQL-Beispiele

In diesem Beispiel wird ein Abfrageausdruck verwendet (d. h. eine SELECT-Anweisung):

Erstellen Sie die zu verwendende Tabelle und Daten:

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)
   ;

Erstellen Sie die UDF:

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

Rufen Sie die UDF in einer Abfrage auf:

SELECT profit();

Ausgabe:

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

In diesem Beispiel wird die UDF in einer WITH-Klausel verwendet:

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
  ;

Ausgabe:

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

In diesem Beispiel wird eine komplexere Abfrage verwendet, die eine JOIN-Operation enthält:

Erstellen Sie die zu verwendende Tabelle und Daten:

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');

Erstellen Sie die 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
  $$
  ;

Rufen Sie die UDF in einer Abfrage auf:

SELECT store_profit();

Ausgabe:

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

Weitere Beispiele finden Sie unter dem Thema CREATE FUNCTION.

Verwenden von UDFs in verschiedenen Klauseln

Ein skalare UDF kann an jeder Stelle verwendet werden, an der ein Skalarausdruck verwendet werden kann. Beispiel:

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

select MyFunc(column1) from table1;

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

Verwenden von SQL-Variablen in einer UDF

In diesem Beispiel wird gezeigt, wie Sie eine SQL-Variable festlegen und diese Variable in einer UDF verwenden:

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
$$
;

Problembehandlung

Fehlermeldung: Unsupported subquery type

Ursache

Wenn eine UDF einen Abfrageausdruck enthält, kann die UDF als Unterabfrage fungieren. Wenn einer Unterabfrage ein Spaltenname übergeben wird, kann die Unterabfrage als korrelierte Unterabfrage fungieren. Wenn eine korrelierte Unterabfrage gegen die Snowflake-Regeln für korrelierte Unterabfragen verstößt, erhält der Benutzer die Fehlermeldung Unsupported subquery type. Das folgende Beispiel zeigt eine ungültige korrelierte Unterabfrage und wie sich eine UDF ähnlich wie eine ungültige korrelierte Unterabfrage verhalten kann.

Erstellen Sie ein Tabellenpaar, und laden Sie Daten in diese Tabellen:

CREATE TABLE stores (store_ID INTEGER, city VARCHAR);
CREATE TABLE employees (employee_ID INTEGER, store_ID INTEGER, sales NUMERIC(10,2), 
    sales_date DATE);
INSERT INTO stores (store_ID, city) VALUES 
    (1, 'Winnipeg'),
    (2, 'Toronto');
INSERT INTO employees (employee_ID, store_ID, sales, sales_date) VALUES 
    (1001, 1, 9000.00, '2020-01-27'),
    (1002, 1, 2000.00, '2020-01-27'),
    (2001, 2, 6000.00, '2020-01-27'),
    (2002, 2, 4000.00, '2020-01-27'),
    (2002, 2, 5000.00, '2020-01-28')
    ;

Die folgende SQL-Anweisung enthält eine korrelierte Unterabfrage, die nicht den Snowflake-Regeln entspricht. Dieser Code verursacht einen Unsupported subquery type-Fehler:

SELECT employee_ID,
       store_ID,
       (SELECT city FROM stores WHERE stores.store_ID = employees.store_ID)
    FROM employees;

Mit dem folgenden Code wird eine Unterabfrage-ähnliche UDF erstellt und dann so aufgerufen, dass eine korrelierte Unterabfrage ähnlich der oben gezeigten erstellt wird:

CREATE FUNCTION subquery_like_udf(X INT)
    RETURNS VARCHAR
    LANGUAGE SQL
    AS
    $$
        SELECT city FROM stores WHERE stores.store_ID = X
    $$;
SELECT employee_ID, subquery_like_udf_2(employees.store_ID)
    FROM employees;
Lösung 1

Wenn die UDF einen Abfrageausdruck enthält, rufen Sie die UDF nur auf eine Weise auf, die den Regeln für Unterabfragen entspricht.

In der folgenden Anweisung wird die UDF beispielsweise mit einer Konstante und nicht mit einem Spaltennamen aufgerufen, sodass die UDF nicht wie eine korrelierte Unterabfrage fungiert:

SELECT subquery_like_udf(1);
+----------------------+
| SUBQUERY_LIKE_UDF(1) |
|----------------------|
| Winnipeg             |
+----------------------+
Lösung 2

In einigen Fällen können Sie die UDF neu schreiben, um dasselbe Ziel auf andere Weise zu erreichen. Eine korrelierte Unterabfrage ist zulässig, wenn die Unterabfrage statisch so festgelegt werden kann, dass sie eine Zeile zurückgibt. Die folgende UDF verwendet eine Aggregatfunktion und gibt daher nur eine Zeile zurück:

CREATE FUNCTION subquery_like_udf_2(X INT)
    RETURNS VARCHAR
    LANGUAGE SQL
    AS
    $$
        SELECT ANY_VALUE(city) FROM stores WHERE stores.store_ID = X
    $$;
SELECT employee_ID, subquery_like_udf_2(employees.store_ID)
    FROM employees;
+-------------+-----------------------------------------+
| EMPLOYEE_ID | SUBQUERY_LIKE_UDF_2(EMPLOYEES.STORE_ID) |
|-------------+-----------------------------------------|
|        1001 | Winnipeg                                |
|        1002 | Winnipeg                                |
|        2001 | Toronto                                 |
|        2002 | Toronto                                 |
|        2002 | Toronto                                 |
+-------------+-----------------------------------------+