CREATE FUNCTION

Crea una nuova funzione definita dall'utente (UDF) scalare utilizzando una clausola SQL SELECT o un programma Python.

Per maggiori informazioni ed esempi, consulta Creazione di funzioni definite dall'utente.

Privilegi richiesti

È necessario disporre dell'autorizzazione in uno dei seguenti modi per eseguire CREATE OR REPLACE FUNCTION:

• Per CREATE FUNCTION:

  • L'utente con privilegi avanzati può utilizzare linguaggi attendibili e non attendibili per creare funzioni.

  • Gli utenti con il privilegio CREATE [ OR REPLACE ] FUNCTION possono creare funzioni con linguaggi attendibili.

• Per REPLACE FUNCTION:

  • Superuser

  • Utenti con il privilegio CREATE [ OR REPLACE ] FUNCTION

  • Proprietario della funzione

Sintassi

CREATE [ OR REPLACE ] FUNCTION f_function_name
( { [py_arg_name  py_arg_data_type |
sql_arg_data_type } [ , ... ] ] )
RETURNS data_type
{ VOLATILE | STABLE | IMMUTABLE }
AS $$
  { python_program | SELECT_clause }
$$ LANGUAGE { plpythonu | sql }

Parametri

OR REPLACE

Specifica che se una funzione con stesso nome e tipi di dati degli argomenti di input o firma è già esistente, la funzione esistente viene sostituita. Puoi sostituire una funzione solo con una nuova funzione che definisce un set identico di tipi di dati. Devi essere un utente con privilegi avanzati per sostituire una funzione.

Se definisci una funzione con lo stesso nome di una funzione esistente ma con una firma diversa, viene creata una nuova funzione. In altre parole, il nome della funzione è sottoposto a overload. Per ulteriori informazioni, consulta Overload dei nomi delle funzioni.

f_function_name

Il nome della funzione. Se si specifica un nome schema (come myschema.myfunction), la funzione viene creata utilizzando lo schema specificato. Altrimenti, la funzione viene creata nello schema corrente. Per ulteriori informazioni sui nomi validi, consultare Nomi e identificatori.

Consigliamo di assegnare un prefisso f_ ai nomi di tutte le funzioni definite dall'utente. Amazon Redshift riserva il prefisso f_ esclusivamente per le funzioni definite dall'utente e aggiungendo il prefisso f_ si evita che il nome della funzione entri in conflitto con altri nomi di funzioni SQL predefinite di Amazon Redshift correnti o future. Per ulteriori informazioni, consulta Denominazione delle funzioni definite dall'utente.

Puoi definire più di una funzione con lo stesso nome di funzione se i tipi di dati per gli argomenti di input sono diversi. In altre parole, il nome della funzione è sottoposto a overload. Per ulteriori informazioni, consulta Overload dei nomi delle funzioni.

py_arg_name py_arg_data_type | sql_arg_data type

Per una UDF Python, un elenco di nomi di argomento di input e i tipi di dati. Per una UDF SQL, un elenco di tipi di dati senza nomi di argomento. In una UDF Python, fai riferimento agli argomenti usando i nomi di argomento. In una UDF SQL, fai riferimento agli argomenti che utilizzano $ 1, $ 2 e così via, in base all'ordine degli argomenti nell'elenco degli argomenti.

Per una UDF SQL, i tipi di dati di input e di restituzione possono essere qualsiasi tipo di dati Amazon Redshift standard. Per una UDF Python, il tipo di dati di input e restituiti può essere SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE PRECISION, BOOLEAN, CHAR, VARCHAR, DATE o TIMESTAMP. Inoltre, le funzioni definite dall'utente (FDU) Python supportano il tipo di dati ANYELEMENT. Questo viene automaticamente convertito in un tipo di dati standard basato sul tipo di dati dell'argomento corrispondente fornito in fase di runtime. Se più argomenti utilizzano ANYELEMENT, verranno risolti tutti nello stesso tipo di dati in fase di runtime, in base al primo argomento ANYELEMENT dell'elenco. Per ulteriori informazioni, consultare Tipi di dati delle funzioni definite dall'utente Python e Tipi di dati.

Puoi specificare un massimo di 32 argomenti.

RETURNS data_type

Il tipo di dati del valore restituito dalla funzione. Il tipo di dati RETURNS può essere qualsiasi tipo di dati standard Amazon Redshift. Inoltre, le UDF Python possono utilizzare un tipo di dati ANYELEMENT, che viene automaticamente convertito in un tipo di dati standard basato sull'argomento fornito in fase di runtime. Se specifichi ANYELEMENT per il tipo di dati di restituzione, almeno un argomento deve utilizzare ANYELEMENT. Il tipo di dati di restituzione effettivo corrisponde al tipo di dati fornito per l'argomento ANYELEMENT quando viene chiamata la funzione. Per ulteriori informazioni, consulta Tipi di dati delle funzioni definite dall'utente Python.

VOLATILE | STABLE | IMMUTABLE

Informa l'ottimizzatore di query circa la volatilità della funzione.

Ottieni la migliore ottimizzazione se etichetti la funzione con la categoria di volatilità più rigida valida. Tuttavia, se la categoria è troppo rigida, c'è il rischio che l'ottimizzatore salti erroneamente alcune chiamate, determinando un set di risultati errato. In ordine di rigidità, a partire dalla meno rigida, le categorie di volatilità sono le seguenti:

• VOLATILE

• STABLE

• IMMUTABLE

VOLATILE

Dati gli stessi argomenti, la funzione può restituire risultati diversi su chiamate successive, anche per le righe in una singola istruzione. L'ottimizzatore di query non può fare alcuna ipotesi sul comportamento di una funzione volatile, quindi una query che utilizza una funzione volatile deve rivalutare la funzione per ogni riga di input.

STABLE

Dati gli stessi argomenti, la funzione è garantita per restituire gli stessi risultati per tutte le righe elaborate all'interno di una singola istruzione. La funzione può restituire risultati diversi se richiamati in istruzioni diverse. Questa categoria consente all'ottimizzatore di ottimizzare più chiamate della funzione all'interno di una singola istruzione in un'unica chiamata per l'istruzione.

IMMUTABLE

Dati gli stessi argomenti, la funzione restituisce sempre lo stesso risultato, per sempre. Quando una query chiama una funzione IMMUTABLE con argomenti costanti, l'ottimizzatore prevaluta la funzione.

AS $$ statement $$

Un costrutto che racchiude l'istruzione da eseguire. Le parole chiavi letterali AS $$ e $$ sono obbligatorie.

Amazon Redshift richiede di racchiudere l'istruzione nella funzione utilizzando un formato chiamato dollar quoting. Qualsiasi elemento all'interno dell'inquadramento viene trasmesso esattamente com'è. Non è necessario impostare il carattere escape per i caratteri speciali, poiché il contenuto della stringa è scritto letteralmente.

Con dollar quoting, utilizzi una coppia di simboli del dollaro ($$) per indicare l'inizio e la fine dell'istruzione da eseguire, come mostrato nell'esempio seguente.

$$ my statement $$

Facoltativamente, tra i segni del dollaro in ciascuna coppia, puoi specificare una stringa per aiutare a identificare l'istruzione. La stringa che utilizzi deve essere uguale sia all'inizio che alla fine delle coppie dell'inquadramento. Questa stringa effettua la distinzione tra lettere maiuscole e minuscole e segue gli stessi vincoli di un identificatore senza virgolette, tranne per il fatto che non può contenere segni di dollaro. Gli esempi seguenti utilizzano la stringa test.

$test$ my statement $test$

Per ulteriori informazioni sul dollar quoting, consultare la sezione relativa alle costanti di stringa racchiuse tra simboli del dollaro nell'argomento relativo alla struttura lessicale della documentazione di PostgreSQL.

python_program

Programma Python eseguibile valido che restituisce un valore. L'istruzione che passi con la funzione deve essere conforme ai requisiti di indentazione specificati nella guida di stile per il codice Python sul sito Web Python. Per ulteriori informazioni, consulta Supporto del linguaggio Python per funzioni definite dall'utente.

SQL_clause

Clausola SQL SELECT.

La clausola SELECT non può includere nessuno dei seguenti tipi di clausole:

• FROM

• INTO

• WHERE

• GROUP BY

• ORDER BY

• LIMIT

LANGUAGE { plpythonu | sql }

Per Python, specifica plpythonu. Per SQL, specifica sql. Devi disporre dell'autorizzazione per l'utilizzo nel linguaggio per SQL o plpythonu. Per ulteriori informazioni, consulta Sicurezza e privilegi dell'UDF.

Note per l'utilizzo

Funzioni nidificate

Puoi chiamare un'altra funzione definita dall'utente (UDF) SQL da una UDF SQL. La funzione nidificata deve esistere quando si esegue il comando CREATE FUNCTION. Amazon Redshift non tiene traccia delle dipendenze per le funzioni definite dall'utente, pertanto se si rimuove la funzione nidificata, Amazon Redshift non restituisce un errore. Tuttavia, l'UDF non riesce se la funzione nidificata non esiste. Ad esempio, la funzione seguente chiama la funzione f_sql_greater nella clausola SELECT.

create function f_sql_commission (float, float )
  returns float
stable
as $$
  select f_sql_greater ($1, $2)
$$ language sql;

Sicurezza e privilegi dell'UDF

Per creare una UDF devi disporre dell'autorizzazione per l'utilizzo nel linguaggio per SQL o plpythonu (Python). Per impostazione predefinita, USAGE ON LANGUAGE SQL è concesso a PUBLIC. Tuttavia, devi concedere esplicitamente USAGE ON LANGUAGE PLPYTHONU a utenti o gruppi specifici.

Per revocare l'utilizzo per SQL, revoca innanzitutto l'utilizzo da PUBLIC. Quindi concedi l'utilizzo su SQL solo a utenti o gruppi specifici autorizzati a creare UDF SQL. L'esempio seguente revoca l'utilizzo su SQL da PUBLIC, quindi concede l'utilizzo al gruppo di utenti udf_devs.

revoke usage on language sql from PUBLIC;
grant usage on language sql to group udf_devs;

Per eseguire una UDF, devi disporre dell'autorizzazione di esecuzione per ciascuna funzione. Per impostazione predefinita, l'autorizzazione di esecuzione per le nuove UDF è concessa a PUBLIC. Per limitare l'utilizzo, revocare l'autorizzazione da PUBLIC per la funzione. Quindi concedi il privilegio a specifici individui o gruppi.

L'esempio seguente revoca l'esecuzione sulla funzione f_py_greater da PUBLIC quindi concede l'utilizzo al gruppo di utenti udf_devs.

revoke execute on function f_py_greater(a float, b float) from PUBLIC;
grant execute on function f_py_greater(a float, b float) to group udf_devs;

Per impostazione predefinita gli utenti con privilegi avanzati hanno tutti i privilegi.

Per ulteriori informazioni, consultare GRANT e REVOKE.

Esempi

Esempio di funzione definita dall'utente Python scalare

L'esempio seguente crea una UDF Python che confronta due numeri interi e restituisce il valore più grande.

create function f_py_greater (a float, b float)
  returns float
stable
as $$
  if a > b:
    return a
  return b
$$ language plpythonu;

L'esempio seguente esegue una query sulla tabella SALES e chiama la nuova funzione f_py_greater per restituire il valore di COMMISSION o il 20 percento di PRICEPAID, a seconda di quale valore è più grande.

select f_py_greater (commission, pricepaid*0.20) from sales;

Esempio di UDF SQL scalare

L'esempio seguente crea una funzione che confronta due numeri e restituisce il valore più grande.

create function f_sql_greater (float, float)
  returns float
stable
as $$
  select case when $1 > $2 then $1
    else $2
  end
$$ language sql;

La query seguente chiama la nuova funzione f_sql_greater per eseguire una query sulla tabella SALES e restituisce il valore di COMMISSION o il 20 percento di PRICEPAID, a seconda di quale valore è più grande.

select f_sql_greater (commission, pricepaid*0.20) from sales;