Tabellarische SQL-UDFs (UDTFs)

Snowflake unterstützt SQL-UDFs, die einen Satz von Zeilen zurückgeben, bestehend aus keiner, einer oder mehreren Zeilen, von denen jede eine oder mehrere Spalten aufweist. Solche UDFs heißen Tabellarische UDFs, Tabellen-UDFs oder am häufigsten UDTFs (benutzerdefinierte Tabellenfunktionen).

Der Zugriff auf eine UDTF kann über die FROM-Klausel einer Abfrage erfolgen.

Syntax

CREATE OR REPLACE FUNCTION <name> ( [ <arguments> ] )
  RETURNS TABLE ( <output_col_name> <output_col_type> [, <output_col_name> <output_col_type> ... ] )
  AS '<sql_expression>'

Eine ausführlichere Beschreibung der allgemeinen Syntax für alle UDFs, einschließlich SQL UDTFs, finden Sie unter CREATE FUNCTION.

Argumente

name:

Dies sollte ein gültiger Datenbankobjektname sein, der den unter Anforderungen an Bezeichner beschriebenen Regeln folgt.

arguments:

Dies muss ein Ausdruck sein, beispielsweise ein Spaltenname, ein Literal oder ein Ausdruck, der zu einem einzelnen Wert ausgewertet werden kann. Normalerweise nimmt eine Funktion ein Argument an, das einen Spaltennamen darstellt. Sie können mehrere Werte übergeben, z. B. mehr als einen Spaltennamen oder einen Spaltennamen und einen oder mehrere Literalwerte.

Es ist möglich, einen Konstantenwert oder keinen Wert zu übergeben. In den meisten Fällen ist die Ausgabe jedoch jedes Mal gleich, wenn die Eingabe jedes Mal gleich ist.

RETURNS TABLE(...)

Gibt an, dass die UDF eine Tabelle zurückgeben soll. Geben Sie innerhalb der Klammern Name-Typ-Paare für Spalten (wie unten beschrieben) an, die in die zurückgegebene Tabelle aufgenommen werden sollen.

output_col_name:

Name einer Ausgabespalte, die in die zurückgegebene Tabelle aufgenommen werden soll. Es muss mindestens eine Ausgabespalte vorhanden sein.

output_col_type:

Der Datentyp der Ausgabespalte.

sql_expression:

Ein gültiger SQL-Ausdruck oder eine gültige Anweisung, die eine Tabelle mit null oder mehrere Zeilen zurückgibt, von denen jede eine oder mehrere Spalten hat. Die Ausgaben müssen mit der Anzahl und den Datentypen übereinstimmen, die in der RETURNS-Klausel angegeben sind.

Nutzungshinweise

  • Der Hauptteil (auch „Definition“) einer SQL-UDTF muss ein SELECT-Ausdruck sein.

  • Obwohl die Trennzeichen um den SQL-Ausdruck sql_expression in der Regel einfache Anführungszeichen sind, können Sie doppelte Dollarzeichen $$ als Trennzeichen verwenden. Das schließende Trennzeichen muss mit dem öffnenden Trennzeichen übereinstimmen. Doppelte Dollarzeichen sind praktisch, wenn sql_expression einfache Anführungszeichen enthält. Ein Beispiel mit doppelten Dollarzeichen befindet sich im Abschnitt „Beispiele“ unten.

    Wenn das Trennzeichen ein einfaches Anführungszeichen ist und der Textkörper ein einfaches Anführungszeichen enthält, können Sie das einfache Anführungszeichen im Textkörper das Backslash-Zeichen \ als Escapezeichen verwenden. Ein Beispiel ist im Abschnitt „Beispiele“ unten zu finden.

  • Die in der UDTF definierten Spalten können überall dort verwendet werden, wo eine normale Tabellenspalte verwendet werden kann.

  • Die in der RETURNS-Klausel angegebenen Rückgabetypen bestimmen Namen und Typen der Spalten in den Tabellenergebnissen und müssen mit den Typen der Ausdrücke an den entsprechenden Positionen der SELECT-Anweisung im Funktionsteil übereinstimmen.

  • Beim Aufrufen einer UDTF müssen Sie hinter dem Schlüsselwort TABLE in Klammern den Namen der UDTF und die Argumente angeben. Weitere Informationen dazu finden Sie unter Aufrufen einer SQL-UDTF.

Bemerkung

Tabellarische Funktionen (UDTFs) haben einen Grenzwert von 500 Eingabeargumenten und 500 Ausgabespalten.

Aufrufen einer SQL-UDTF

Wenn Sie eine UDTF in der FROM-Klausel einer Abfrage aufrufen, geben Sie den Namen und die Argumente der UDTF innerhalb der Klammern an, die dem Schlüsselwort TABLE folgen.

Verwenden Sie beim Aufrufen einer UDTF das Schlüsselwort TABLE in etwa wie folgt:

SELECT ...
  FROM TABLE ( udtf_name (udtf_arguments) )

Beispiele für SQL-UDTFs

Grundlegende Beispiele

Dies ist ein stark vereinfachtes Beispiel für eine UDTF mit einer hartcodierten Ausgabe. Dies veranschaulicht auch die Verwendung von $$ als Trennzeichen:

CREATE FUNCTION t()
    RETURNS TABLE(msg VARCHAR)
    AS
    $$
        SELECT 'Hello'
        UNION
        SELECT 'World'
    $$;

SELECT msg 
    FROM TABLE(t())
    ORDER BY msg;
+-------+
| MSG   |
|-------|
| Hello |
| World |
+-------+

Dieses Beispiel ähnelt dem vorhergehenden Beispiel, aber es verwendet einfache Anführungszeichen als Trennzeichen und das \ als Escapezeichen für die einfachen Anführungszeichen im Hauptteil der UDTF:

CREATE FUNCTION t()
    RETURNS TABLE(msg VARCHAR)
    AS
    '
        SELECT \'Hello\'
        UNION
        SELECT \'World\'
    ';

SELECT msg
    FROM TABLE(t())
    ORDER BY msg;
+-------+
| MSG   |
|-------|
| Hello |
| World |
+-------+

Dies ist ein weiteres grundlegendes Beispiel für eine UDTF. Dabei wird eine Tabelle abgefragt, und es werden zwei der Spalten aus dieser Tabelle zurückgegeben:

create or replace table orders (
    product_id varchar, 
    quantity_sold numeric(11, 2)
    );

insert into orders (product_id, quantity_sold) values 
    ('compostable bags', 2000),
    ('re-usable cups',  1000);

create or replace function orders_for_product(PROD_ID varchar)
    returns table (Product_ID varchar, Quantity_Sold numeric(11, 2))
    as
    $$
        select product_ID, quantity_sold 
            from orders 
            where product_ID = PROD_ID
    $$
    ;

select product_id, quantity_sold
    from table(orders_for_product('compostable bags'))
    order by product_id;
+------------------+---------------+
| PRODUCT_ID       | QUANTITY_SOLD |
|------------------+---------------|
| compostable bags |       2000.00 |
+------------------+---------------+

Dieselbe Funktionalität kann auch über eine Ansicht implementiert werden kann.

Beispiele mit Verknüpfungen (Joins)

Erstellen Sie eine SQL-UDTF, die Länderinformationen (COUNTRY_CODE und COUNTRY_NAME) für eine angegebene Benutzer-ID zurückgibt:

create or replace table countries (country_code char(2), country_name varchar);
insert into countries (country_code, country_name) values 
    ('FR', 'FRANCE'),
    ('US', 'UNITED STATES'),
    ('ES', 'SPAIN');

create or replace table user_addresses (user_ID integer, country_code char(2));
insert into user_addresses (user_id, country_code) values 
    (100, 'ES'),
    (123, 'FR'),
    (123, 'US');

CREATE OR REPLACE FUNCTION get_countries_for_user ( id number )
  RETURNS TABLE (country_code char, country_name varchar)
  AS 'select distinct c.country_code, c.country_name
      from user_addresses a, countries c
      where a.user_id = id
      and c.country_code = a.country_code';

select *
    from table(get_countries_for_user(123)) cc
    where cc.country_code in ('US','FR','CA')
    order by country_code;
+--------------+---------------+
| COUNTRY_CODE | COUNTRY_NAME  |
|--------------+---------------|
| FR           | FRANCE        |
| US           | UNITED STATES |
+--------------+---------------+

Erstellen Sie eine SQL-UDTF, die die Lieblingsfarbe für ein bestimmtes Jahr zurückgibt:

create or replace table favorite_years as
    select 2016 year
    UNION ALL
    select 2017
    UNION ALL
    select 2018
    UNION ALL
    select 2019;

 create or replace table colors as
    select 2017 year, 'red' color, true favorite
    UNION ALL
    select 2017 year, 'orange' color, true favorite
    UNION ALL
    select 2017 year, 'green' color, false favorite
    UNION ALL
    select 2018 year, 'blue' color, true favorite
    UNION ALL
    select 2018 year, 'violet' color, true favorite
    UNION ALL
    select 2018 year, 'brown' color, false favorite;

create or replace table fashion as
    select 2017 year, 'red' fashion_color
    UNION ALL
    select 2018 year, 'black' fashion_color
    UNION ALL
    select 2019 year, 'orange' fashion_color;

create or replace function favorite_colors(the_year int)
    returns table(color string) as
    'select color from colors where year=the_year and favorite=true';

Rufen Sie die UDTF in einer Abfrage auf:

select color
    from table(favorite_colors(2017))
    order by color;
+--------+
| COLOR  |
|--------|
| orange |
| red    |
+--------+

Verwenden Sie UDTF in einer Verknüpfung (Join) mit einer anderen Tabelle. Beachten Sie, dass die Join-Spalte aus der Tabelle als Argument an die Funktion übergeben wird.

select * 
    from favorite_years y join table(favorite_colors(y.year)) c
    order by year, color;
+------+--------+
| YEAR | COLOR  |
|------+--------|
| 2017 | orange |
| 2017 | red    |
| 2018 | blue   |
| 2018 | violet |
+------+--------+

Verwenden Sie für zusätzliche Prädikate anstelle von ON eine WHERE-Klausel:

select * 
    from fashion f join table(favorite_colors(f.year)) fav
    where fav.color = f.fashion_color ;
+------+---------------+-------+
| YEAR | FASHION_COLOR | COLOR |
|------+---------------+-------|
| 2017 | red           | red   |
+------+---------------+-------+

Verwenden Sie UDTF mit einer Konstante in einem Join-Ausdruck. Beachten Sie, dass für zusätzliche Verknüpfungsbedingungen eine WHERE-Klausel anstelle von ON verwendet werden muss:

select fav.color as favorite_2017, f.*
    from fashion f JOIN table(favorite_colors(2017)) fav
    where fav.color = f.fashion_color
    order by year;
+---------------+------+---------------+
| FAVORITE_2017 | YEAR | FASHION_COLOR |
|---------------+------+---------------|
| red           | 2017 | red           |
| orange        | 2019 | orange        |
+---------------+------+---------------+