SQL-UDTFs (Benutzerdefinierte Tabellenfunktionen)

Eine UDTF gibt einen Satz von Zeilen anstelle eines einzelnen, skalaren Werts zurück und kann in der FROM-Klausel einer Abfrage aufgerufen werden. Snowflake unterstützt sowohl SQL- als auch JavaScript-UDTFs. Unter diesem Thema werden SQL-UDTFs behandelt.

Informationen zu JavaScript-UDTFs finden Sie unter JavaScript-UDTFs (Benutzerdefinierte Tabellenfunktionen).

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.

  • 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.

  • Obwohl die Trennzeichen um den SQL-Ausdruck normalerweise aus einfachen Anführungszeichen bestehen, können Sie alternative Trennzeichen verwenden, z. B. eine Zeichenfolge aus zwei oder mehr Dollarzeichen („$$“). Das schließende Trennzeichen muss mit dem öffnenden Trennzeichen übereinstimmen. Eine Zeichenfolge aus Dollarzeichen ist praktisch, wenn der SQL-Ausdruck einfache Anführungszeichen enthält. Ein Beispiel mit doppelten Dollarzeichen befindet sich im Abschnitt „Beispiele“ unten.

  • Eine SQL-UDTF wird normalerweise in der FROM-Klausel einer Abfrage mit dem Schlüsselwort TABLE aufgerufen. Die in der UDTF definierten Spalten können überall dort erscheinen, wo eine normale Tabellenspalte verwendet werden kann.

Beispiele für SQL-UDTFs

Grundlegende Beispiele

Dies ist ein stark vereinfachtes Beispiel für eine UDTF mit einer hartcodierten Ausgabe:

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        |
+---------------+------+---------------+