CREATE PROCEDURE

Crea una nuova procedura archiviata o sostituisce una procedura esistente per il database corrente.

Per maggiori informazioni ed esempi, consulta Creazione di procedure archiviate in Amazon Redshift.

Privilegi richiesti

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

• Per CREATE PROCEDURE:

  • Superuser

  • Utenti con privilegi CREATE e USAGE sullo schema in cui viene creata la stored procedure

• Per REPLACE PROCEDURE:

  • Superuser

  • Proprietario della procedura

Sintassi

CREATE [ OR REPLACE ] PROCEDURE sp_procedure_name  
  ( [ [ argname ] [ argmode ] argtype [, ...] ] )
[ NONATOMIC ]
AS $$
  procedure_body
$$ LANGUAGE plpgsql
[ { SECURITY INVOKER | SECURITY DEFINER } ]
[ SET configuration_parameter { TO value | = value } ]

Parametri

OR REPLACE

Una clausola che specifica che se una procedura con stesso nome e tipi di dati degli argomenti di input o firma è già esistente, la procedura esistente viene sostituita. Puoi sostituire una procedura solo con una nuova che definisce un set identico di tipi di dati.

Se definisci una funzione con lo stesso nome di una procedura esistente, ma con una firma diversa, verrà creata una nuova procedura. Ossia, il nome della procedura viene sottoposto a overload. Per ulteriori informazioni, consulta Overload dei nomi delle procedure.

sp_procedure_name

Il nome della procedura. Se specifichi un nome di schema (come myschema.myprocedure), la procedura viene creata utilizzando lo schema specificato. Altrimenti, la procedura viene creata nello schema corrente. Per ulteriori informazioni sui nomi validi, consultare Nomi e identificatori.

Consigliamo di assegnare un prefisso sp_ ai nomi di tutte le procedure archiviate. Amazon Redshift riserva il prefisso sp_ solo per le procedure archiviate. Utilizzando il prefisso sp_, si assicura che il nome della procedura archiviata non sia in conflitto con alcun nome di procedura archiviata o funzione integrate di Amazon Redshift esistente o futuro. Per ulteriori informazioni, consulta Denominazione delle stored procedure.

Puoi definire più di una procedura con lo stesso nome se i tipi di dati per gli argomenti di input o le firme sono diversi. Ossia, in questo caso il nome della procedura viene sottoposto a overload. Per ulteriori informazioni, consulta Overload dei nomi delle procedure

[argname] [ argmode] argtype

Elenco di nomi di argomento, modalità di argomento e tipi di dati. È obbligatorio solo il tipo di dati. Il nome e la modalità sono opzionali e la loro posizione può essere scambiata.

La modalità dell'argomento può essere IN, OUT o INOUT. Il valore predefinito è IN.

Puoi utilizzare gli argomenti OUT e INOUT per restituire uno o più valori dalla chiamata di una procedura. Se sono presenti argomenti OUT o INOUT, la chiamata della procedura restituisce una riga di risultati contenente n colonne, dove n è il numero totale di argomenti OUT o INOUT.

Gli argomenti INOUT sono contemporaneamente argomenti di input e output. Gli argomenti di input includono sia argomenti IN che INOUT, mentre gli argomenti di output includono sia argomenti OUT che INOUT.

Gli argomenti OUT non sono specificati come parte dell'istruzione CALL. Specifica gli argomenti INOUT nell'istruzione CALL della procedura archiviata. Gli argomenti INOUT possono essere utili durante la trasmissione e la restituzione dei valori da una chiamata nidificata e anche durante la restituzione di un refcursor. Per ulteriori informazioni sui tipi refcursor, consultare Cursori.

I tipi di dati dell'argomento possono essere qualsiasi tipo di dati Amazon Redshift standard. Inoltre, un tipo di dati dell'argomento può essere refcursor.

Puoi specificare un massimo di 32 argomenti di input e un massimo di 32 argomenti di output.

AS $$ procedure_body $$

Un costrutto che racchiude la procedura da eseguire. Le parole chiavi letterali AS $$ e $$ sono obbligatorie.

Amazon Redshift richiede di racchiudere l'istruzione nella procedura 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$

Questa sintassi è inoltre utile per il dollar quoting nidificato. Per ulteriori informazioni sul dollar quoting, consultare l'argomento relativo alle costanti di stringa racchiuse tra simboli del dollaro nella sezione relativa alla struttura lessicale della documentazione di PostgreSQL.

procedure_body

Un set di PL/pgSQL statements. PL/pgSQL istruzioni valide integra i comandi SQL con costrutti procedurali, tra cui cicli ed espressioni condizionali, per controllare il flusso logico. La maggior parte dei comandi SQL può essere utilizzato nel corpo della procedura, includendo il linguaggio DML (Data Modification Language) come COPY, UNLOAD e INSERT, e il linguaggio DDL (Data Definition Language) come CREATE TABLE. Per ulteriori informazioni, consulta Riferimento al linguaggio PL/pgSQL.

LANGUAGE plpgsql

Valore di un linguaggio. Specifica plpgsql. Devi disporre dell'autorizzazione per l'utilizzo nel linguaggio per usare plpgsql. Per ulteriori informazioni, consulta GRANT.

NONATOMIC

Crea la procedura archiviata in una modalità di transazione NONATOMIC. La modalità NONATOMIC esegue automaticamente il commit delle istruzioni all'interno della procedura. Inoltre, quando si verifica un errore all'interno della procedura NONATOMIC, l'errore non viene generato nuovamente se viene gestito da un blocco di eccezioni. Per ulteriori informazioni, consulta Gestione delle transazioni e RAISE.

Quando si definisce una procedura archiviata NONATOMIC, considerare quanto segue:

• Quando si annidano le chiamate di procedura archiviata, tutte le procedure devono essere create nella stessa modalità di transazione.

• Le opzioni SECURITY DEFINER e SET configuration_parameter non sono supportate quando si crea una procedura in modalità NONATOMIC.

• Qualsiasi cursore che è aperto (esplicitamente o implicitamente) viene chiuso automaticamente quando viene eseguito un commit implicito. Pertanto, è necessario aprire una transazione esplicita prima di iniziare un ciclo di cursori per garantire che non venga eseguito il commit implicito di qualsiasi codice SQL all'interno dell'iterazione del ciclo.

SECURITY INVOKER | SECURITY DEFINER

L'opzione SECURITY DEFINER non è supportata quando è specificato NONATOMIC.

Modalità di sicurezza con cui la procedura determina i privilegi di accesso alla procedura in fase di runtime. La procedura deve disporre dell'autorizzazione ad accedere agli oggetti di database sottostanti.

Per la modalità SECURITY INVOKER, la procedura utilizza i privilegi dell'utente che la chiama. L'utente deve disporre di autorizzazioni esplicite per gli oggetti di database sottostanti. L'impostazione predefinita è SECURITY INVOKER.

Per la modalità SECURITY DEFINER, la procedura utilizza i privilegi del proprietario. Il proprietario della procedura è definito come l'utente proprietario della procedura in fase di esecuzione, che non è necessariamente l'utente che ha definito inizialmente la procedura. L'utente che chiama la procedura necessita dei privilegi di esecuzione per la procedura, ma non dei privilegi relativi agli oggetti sottostanti.

SET configuration_parameter { TO value | = value }

Queste opzioni non sono supportate quando è specificato NONATOMIC.

La clausola SET implica l'impostazione del parametro configuration_parameter specificato sul valore indicato quando viene immessa la procedura. Questa clausola quindi ripristina configuration_parameter sul suo valore precedente all'uscita della procedura.

Note per l'utilizzo

Se una procedura archiviata è stata creata utilizzando l'opzione SECURITY DEFINER, quando si richiama la funzione CURRENT_USER dall'interno della procedura archiviata, Amazon Redshift restituisce il nome utente del proprietario della procedura archiviata.

Esempi

Nota

Se, durante l'esecuzione di questi esempi, hai riscontrato un errore simile a:

ERROR: 42601: [Amazon](500310) unterminated dollar-quoted string at or near "$$

Per informazioni, consultare Panoramica delle procedure archiviate in Amazon Redshift.

L'esempio seguente crea una procedura con due parametri di input.

CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar(20))
AS $$
DECLARE
  min_val int;
BEGIN
  DROP TABLE IF EXISTS tmp_tbl;
  CREATE TEMP TABLE tmp_tbl(id int);
  INSERT INTO tmp_tbl values (f1),(10001),(10002);
  SELECT INTO min_val MIN(id) FROM tmp_tbl;
  RAISE INFO 'min_val = %, f2 = %', min_val, f2;
END;
$$ LANGUAGE plpgsql;

Nota

Quando si scrivono le stored procedure, si consiglia di attenersi a una best practice per proteggere i valori sensibili:

Non eseguire la codifica fissa delle informazioni sensibili nella logica delle procedure archiviate. Ad esempio, non assegnare una password utente in un'istruzione CREATE USER nel corpo di una procedura archiviata. Ciò rappresenta un rischio per la sicurezza, poiché i valori con codifica fissa possono essere registrati come metadati dello schema nelle tabelle del catalogo. È invece consigliabile passare i valori sensibili, ad esempio le password, come argomenti alla procedura archiviata, mediante parametri.

Per ulteriori informazioni sulle procedure archiviate, consulta CREATE PROCEDURE e Creazione di procedure archiviate in Amazon Redshift. Per ulteriori informazioni sulle tabelle di catalogo, consulta Tabelle di catalogo di sistema.

L'esempio seguente crea una procedura con un parametro IN, un parametro OUT e un parametro INOUT.

CREATE OR REPLACE PROCEDURE test_sp2(f1 IN int, f2 INOUT varchar(256), out_var OUT varchar(256))
AS $$
DECLARE
  loop_var int;
BEGIN
  IF f1 is null OR f2 is null THEN
    RAISE EXCEPTION 'input cannot be null';
  END IF;
  DROP TABLE if exists my_etl;
  CREATE TEMP TABLE my_etl(a int, b varchar);
    FOR loop_var IN 1..f1 LOOP
        insert into my_etl values (loop_var, f2);
        f2 := f2 || '+' || f2;
    END LOOP;
  SELECT INTO out_var count(*) from my_etl;
END;
$$ LANGUAGE plpgsql;

L'esempio seguente crea una procedura che utilizza il parametro. SECURITY DEFINER Questa procedura viene eseguita utilizzando i privilegi dell'utente proprietario della procedura.

CREATE OR REPLACE PROCEDURE sp_get_current_user_definer()
AS $$
DECLARE curr_user varchar(250);
BEGIN
  SELECT current_user INTO curr_user;
  RAISE INFO '%', curr_user;
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER;   
         

L'esempio seguente crea una procedura che utilizza il SECURITY INVOKER parametro. Questa procedura viene eseguita utilizzando i privilegi dell'utente che esegue la procedura.

CREATE OR REPLACE PROCEDURE sp_get_current_user_invoker()
AS $$
DECLARE curr_user varchar(250);
BEGIN
  SELECT current_user INTO curr_user;
  RAISE INFO '%', curr_user;
END;
$$ LANGUAGE plpgsql
SECURITY INVOKER;