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.

Weitere Informationen zu skalaren SQL-UDFs finden Sie unter Skalare SQL-UDFs.

Weitere Informationen zu JavaScript-UDTFs finden Sie unter Tabellarische JavaScript-UDFs (UDTFs).

Unter diesem Thema:

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.

Argumente:

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.

Ausgabespaltenname:

Der Name der Ausgabespalte. Es muss mindestens eine Ausgabespalte vorhanden sein.

Ausgabespaltentyp:

Der Datentyp der Ausgabespalte.

SQL-Ausdruck:

Dies muss ein gültiger SQL-Ausdruck oder eine gültige Anweisung sein, die eine Tabelle zurückgibt (d. h. null oder mehrere Zeilen, 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 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 der SQL-Ausdruck 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.

  • Eine SQL-UDTF wird normalerweise in der FROM-Klausel einer Abfrage mit dem Schlüsselwort TABLE aufgerufen. (Weitere Informationen zum Schlüsselwort TABLE finden Sie unter Tabellenfunktionen). Die in der UDTF definierten Spalten können überall dort erscheinen, 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.

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'),
    ('SP', 'SPAIN');

create or replace table user_addresses (user_ID integer, country_code char(2));
insert into user_addresses (user_id, country_code) values 
    (100, 'SP'),
    (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 * from table(favorite_colors(2017));
+--------+
| COLOR  |
|--------|
| red    |
| orange |
+--------+

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;
+------+--------+
| YEAR | COLOR  |
|------+--------|
| 2017 | red    |
| 2017 | orange |
| 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;
+---------------+------+---------------+
| FAVORITE_2017 | YEAR | FASHION_COLOR |
|---------------+------+---------------|
| red           | 2017 | red           |
| orange        | 2019 | orange        |
+---------------+------+---------------+