Skalare SQL-UDFs¶

Memoisierbare Funktion erstellen¶

Sie können eine skalare SQL-UDF in der CREATE FUNCTION-Anweisung durch Angabe des Schlüsselworts MEMOIZABLE als memoisierbare Funktion definieren. Memoisierbare Funktionen enthalten keine Argumente. Wenn Sie eine memoisierbare Funktion schreiben:

• Geben Sie BOOLEAN oder andere skalare Datentypen als result_data_type an.

  Bei der Verwendung von ARRAY als result_data_type ist Vorsicht geboten, da die Cache-Größe begrenzt ist.

• Geben Sie keine anderen Datentypen, wie OBJECT und VARIANT an.

• Verweisen Sie in keiner Weise auf eine andere memoisierbare Funktion.

Memoisierbare Funktion aufrufen¶

Eine memoisierbare Funktion kann in einer SELECT-Anweisung aufgerufen oder in eine Richtliniendefinition aufgenommen werden, die dann die memoisierbare Funktion auf der Grundlage der Richtlinienbedingungen aufruft.

Beachten Sie beim Aufrufen einer memoisierbaren Funktion Folgendes:

• Für SQL-UDFs, die den Datentyp ARRAY zurückgeben oder einen nicht skalaren Wert angeben, verwenden Sie die memoisierbare Funktion als Argument in der Funktion ARRAY_CONTAINS.

• Beschränkung der Cache-Größe:

  Jede memoisierbare Funktion hat eine Beschränkung von 10 KB für die aktuelle Snowflake-Sitzung.

  Wenn die memoisierbare Funktion diese Grenze für den Resultset-Cache überschreitet, speichert Snowflake das Ergebnis des Aufrufs der memoisierbaren Funktion nicht. Stattdessen verhält sich UDF wie eine normale skalare UDF, je nachdem, wie die Funktion geschrieben ist.

• Cache-Nutzung:

  Memoisierbare Funktionen haben einen wiederverwendbaren Ergebnis-Cache für verschiedene SQL-Anweisungen, wenn sich die Abfrageumgebung und der Kontext nicht ändern. Im Allgemeinen bedeutet dies, dass der Ergebnis-Cache für verschiedene SQL-Anweisungen gilt, sofern Folgendes gilt:

  • Die Autorisierung der Zugriffssteuerung von Objekten und Spalten, auf die in einer Abfrage verwiesen wird, bleibt unverändert.

  • Die Objekte, auf die in der Abfrage verwiesen wird, werden nicht geändert (z. B. durch DML-Anweisungen).

  In der Spalte CHILD_QUERIES_WAIT_TIME der Account Usage-Ansicht QUERY_HISTORY wird die Zeit (in Millisekunden) erfasst, die bei Aufruf einer memoisierbaren Funktion bis zum Abschluss der zwischengespeicherten Suche benötigt wird.

• Die zwischengespeicherten Ergebnisse von memoisierbaren Funktionen werden in folgenden Fällen nicht wiederverwendet:

  • Die Funktion verweist auf eine Tabelle oder ein anderes Objekt, und es erfolgt eine Aktualisierung der referenzierten Tabelle.

  • Es gibt eine Änderung in der Zugriffssteuerung der Tabelle.

  • Die Funktion ruft eine nicht deterministische Funktion auf.

  • Die Funktion ruft eine externe Funktion oder eine UDF auf, die keine SQL-UDF ist.

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:

SELECT pi_udf();
+-------------+
|    PI_UDF() |
|-------------|
| 3.141592654 |
+-------------+

Häufige SQL-Beispiele¶

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

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

SELECT profit();

SELECT profit();
+----------+
| PROFIT() |
|----------|
|   530.00 |
+----------+

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
  ;

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

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

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

SELECT store_profit();

SELECT store_profit();
+----------------+
| STORE_PROFIT() |
|----------------|
|         550.00 |
+----------------+

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(*)/2 FROM table1);

CREATE OR REPLACE FUNCTION my_filter_function()
RETURNS TABLE (id int)
AS
$$
SELECT id FROM table1 WHERE id > $id_threshold
$$
;