Sottoinsiemi di comandi SQL supportati in Aurora DSQL

Questa sezione di PostgreSQL fornisce informazioni dettagliate sulle espressioni supportate, concentrandosi sui comandi con set di parametri e sottocomandi estesi. Ad esempio, CREATE TABLE in PostgreSQL offre molte clausole e parametri. Questa sezione descrive tutti gli elementi della sintassi PostgreSQL supportati da Aurora DSQL per questi comandi.

CREATE TABLE

CREATE TABLE definisce una nuova tabella.

CREATE TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression )|
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) STORED |
  UNIQUE [ NULLS [ NOT ] DISTINCT ] index_parameters |
  PRIMARY KEY index_parameters |

and table_constraint is:

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) |
  UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |

and like_option is:

{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | ALL }

index_parameters in UNIQUE, and PRIMARY KEY constraints are:
[ INCLUDE ( column_name [, ... ] ) ]

ALTER TABLE

ALTER TABLE modifica la definizione di una tabella.

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] name
    RENAME TO new_name
ALTER TABLE [ IF EXISTS ] name
    SET SCHEMA new_schema

where action is one of:

    ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type 
    OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }

CREATE VIEW

CREATE VIEW definisce una nuova vista persistente. Aurora DSQL non supporta le viste temporanee, sono supportate solo le viste permanenti.

Sintassi supportata

CREATE [ OR REPLACE ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]    

Description

CREATE VIEW definisce una vista basata su una query. La vista non è materializzata fisicamente. Al contrario, la query viene eseguita ogni volta che si fa riferimento alla vista in una query.

CREATE or REPLACE VIEW è simile, ma se esiste già una vista con lo stesso nome, questa viene sostituita. La nuova query deve generare le stesse colonne generate dalla query della vista esistente (ovvero gli stessi nomi di colonna nello stesso ordine e con gli stessi tipi di dati), ma può aggiungere ulteriori colonne alla fine dell’elenco. I calcoli che danno origine alle colonne di output possono essere diversi.

Se viene specificato un nome di schema (come CREATE VIEW myschema.myview ...), la vista viene creata utilizzando lo schema specificato. In caso contrario la vista viene creata nello schema corrente.

Il nome della vista deve essere distinto dal nome di qualsiasi altra relazione (tabella, indice, vista) nello stesso schema.

Parametri

CREATE VIEW supporta vari parametri per controllare il comportamento delle viste aggiornabili automaticamente.

RECURSIVE

Crea una vista ricorsiva. La sintassi: CREATE RECURSIVE VIEW [ schema . ] view_name (column_names) AS SELECT ...; è equivalente a CREATE VIEW [ schema . ] view_name AS WITH RECURSIVE view_name (column_names) AS (SELECT ...) SELECT column_names FROM view_name;.

Per una vista ricorsiva è necessario specificare un elenco di nomi di colonne della vista.

name

Il nome della vista da creare, che può essere facoltativamente accompagnato dallo schema. Per una vista ricorsiva è necessario specificare un elenco di nomi di colonne.

column_name

Un elenco facoltativo di nomi da utilizzare per le colonne nella vista. Se non vengono specificati nomi di colonne, questi ricavati dalla query.

WITH ( view_option_name [= view_option_value] [, ... ] )

Questa clausola specifica i parametri opzionali per una vista. Sono supportati i seguenti parametri.

• check_option (enum) - Questo parametro può assumere i valori local o cascaded ed è equivalente a specificare WITH [ CASCADED | LOCAL ] CHECK OPTION.

• security_barrier (boolean) - Deve essere usato se la vista è destinata a fornire una sicurezza a livello di riga. Aurora DSQL attualmente non supporta la sicurezza a livello di riga, ma questa opzione forzerà comunque la valutazione prioritaria delle condizioni WHERE della vista (e di tutte le condizioni che utilizzano operatori contrassegnati come LEAKPROOF).

• security_invoker (boolean) - Questa opzione fa sì che le relazioni di base sottostanti vengano verificate con i privilegi dell’utente della vista anziché con quelli del proprietario della vista. Per dettagli completi, consulta le note riportate di seguito.

Tutte le opzioni precedenti possono essere modificate nelle viste esistenti utilizzando ALTER VIEW.

query

Un comando SELECT o VALUES che fornisce le colonne e le righe della vista.

• WITH [ CASCADED | LOCAL ] CHECK OPTION - Questa opzione controlla il comportamento delle viste aggiornabili automaticamente. Quando viene specificata questa opzione, i comandi INSERT e UPDATE sulla vista verranno controllati per garantire che le nuove righe soddisfino la condizione di definizione della vista (ovvero, le nuove righe vengono controllate per garantire che siano visibili attraverso la vista). In caso contrario, l’aggiornamento verrà rifiutato. Se CHECK OPTION non è specificato, i comandi INSERT e UPDATE sulla vista possono creare righe che non sono visibili attraverso la vista stessa. Di seguito sono riportate le opzioni supportate.

• LOCAL - Le nuove righe vengono verificate solo in base alle condizioni definite direttamente nella vista stessa. Qualsiasi condizione definita nelle viste di base sottostanti non viene verificata (a meno che anch’esse non specifichino l’opzione CHECK OPTION).

• CASCADED - Le nuove righe vengono verificate rispetto alle condizioni della vista e di tutte le viste di base sottostanti. Se viene indicato CHECK OPTION e non viene specifica l’opzioni LOCAL né l’opzione CASCADED, viene assunto il valore CASCADED.

Nota

CHECK OPTION può essere utilizzato con le viste RECURSIVE. CHECK OPTION è supportato solo nelle viste aggiornabili automaticamente.

Note

Utilizzare l’istruzione DROP VIEW per eliminare le viste.

I nomi e i tipi di dati delle colonne della vista devono essere considerati attentamente. Ad esempio, CREATE VIEW vista AS SELECT ’Hello World’; non è consigliato perché il nome della colonna predefinito è ?column?;. Inoltre, il tipo di dati della colonna predefinito è text, il che potrebbe non essere quello desiderato.

Un approccio migliore consiste nello specificare esplicitamente il nome della colonna e il tipo di dati, ad esempio: CREATE VIEW vista AS SELECT text 'Hello World' AS hello;.

Per impostazione predefinita, l’accesso alle relazioni di base sottostanti a cui si fa riferimento nella vista è determinato dalle autorizzazioni del proprietario della vista. In alcuni casi, questo può essere utilizzato per fornire un accesso sicuro ma limitato alle tabelle sottostanti. Tuttavia, non tutte le viste sono protette dalla manomissione.

• Se la proprietà security_invoker della vista è impostata su true, l’accesso alle relazioni di base sottostanti è determinato dalle autorizzazioni dell’utente che esegue la query, anziché su quelle del proprietario della vista. Pertanto, l’utente di una vista con l’opzione Security Invoker deve disporre delle autorizzazioni pertinenti sulla vista e sulle relative relazioni di base sottostanti.

• Se una delle relazioni di base sottostanti è una vista con l’opzione Security Invoker, verrà trattata come se vi fosse stato effettuato l’accesso direttamente dalla query originale. Pertanto, una vista con l’opzione Security Invoker verificherà sempre le relazioni di base sottostanti utilizzando le autorizzazioni dell’utente corrente, anche se vi si accede da una vista senza la proprietà security_invoker.

• Le funzioni richiamate nella vista vengono trattate come se fossero state chiamate direttamente dalla query che utilizza la vista. Pertanto, l’utente di una vista deve disporre delle autorizzazioni per richiamare tutte le funzioni utilizzate dalla vista. Le funzioni nella vista vengono eseguite con i privilegi dell’utente che esegue la query o del proprietario della funzione, a seconda che le funzioni siano definite come SECURITY INVOKER o SECURITY DEFINER. Ad esempio, la chiamata CURRENT_USER diretta in una vista restituirà sempre l’utente che la invoca, non il proprietario della vista. Ciò non è influenzato dall’impostazione dell’opzione security_invoker della vista, quindi una vista con l’opzione security_invoker impostata su false non è equivalente a una funzione SECURITY DEFINER.

• L’utente che crea o sostituisce una vista deve disporre dei privilegi USAGE su tutti gli schemi a cui si fa riferimento nella query della vista, al fine di poter accedere agli oggetti a cui si fa riferimento in tali schemi. Si noti, tuttavia, che questa ricerca viene eseguita solo quando la vista viene creata o sostituita. Pertanto, l’utente della vista richiede il privilegio USAGE solo sullo schema che contiene la vista, non sugli schemi a cui si fa riferimento nella query della vista, anche per una vista con l’opzione Security Invoker.

• Quando CREATE OR REPLACE VIEW viene utilizzato su una vista esistente, vengono modificate solo la regola di SELECT di definizione della vista, più eventuali parametri WITH ( ... ) e relativa CHECK OPTION. Le altre caratteristiche della vista, tra cui proprietà, autorizzazioni e regole non selezionate, rimangono invariate. Per sostituire una vista è necessario esserne proprietari (ciò include essere un membro del ruolo proprietario).

Viste aggiornabili

Le viste semplici sono aggiornabili automaticamente: il sistema consentirà alle istruzioni INSERT, UPDATE e DELETE di operare sulla vista allo stesso modo in cui avrebbe fatto su una normale tabella. Una vista è aggiornabile automaticamente se soddisfa tutte le seguenti condizioni:

• La vista deve avere esattamente una voce nell’elenco della clausola FROM, e tale elemento deve essere una tabella o un’altra vista aggiornabile.

• La definizione della vista non deve contenere clausole WITH, DISTINCT, GROUP BY, HAVING, LIMIT o OFFSET al livello principale.

• La definizione della vista non deve contenere operazioni sugli insiemi (UNION, INTERSECT oEXCEPT) al livello principale.

• L’elenco di selezione della vista non deve contenere aggregati, funzioni finestra o funzioni che restituiscono insiemi.

Una vista aggiornabile automaticamente può contenere una combinazione di colonne aggiornabili e non aggiornabili. Una colonna è aggiornabile se è un semplice riferimento a una colonna aggiornabile della relazione di base sottostante. In caso contrario, la colonna è di sola lettura e si verifica un errore se un’istruzione INSERT o UPDATE tenta di assegnarle un valore.

Per le viste aggiornabili automaticamente, il sistema converte qualsiasi istruzione INSERT, UPDATE o DELETE sulla vista nell’istruzione corrispondente sulla relazione di base sottostante. Le istruzioni INSERT con una clausola ON CONFLICT UPDATE sono pienamente supportate.

Se una vista aggiornabile automaticamente contiene una condizione WHERE, la condizione limita le righe della relazione di base che possono essere modificate dalle istruzioni UPDATE e DELETE eseguite sulla vista. Tuttavia, un’istruzione UPDATE può modificare una riga in modo che non soddisfi più la condizione WHERE, rendendola invisibile dalla vista. Allo stesso modo, un comando INSERT può potenzialmente inserire righe nella relazione di base che non soddisfano la condizione WHERE, rendendole invisibili attraverso la vista. ON CONFLICT UPDATE può influire in modo analogo su una riga esistente non visibile attraverso la vista.

È possibile utilizzare CHECK OPTION per impedire che i comandi INSERT e UPDATE creino righe che non sono visibili attraverso la vista.

Se una vista aggiornabile automaticamente è contrassegnata con la proprietà security_barrier, tutte le condizioni WHERE della vista (e tutte le condizioni che utilizzano gli operatori contrassegnati come LEAKPROOF) vengono sempre valutate prima di qualsiasi condizione aggiunta da un utente della vista. Bisogne tenere presente che, per questo motivo, le righe che alla fine non vengono restituite (perché non soddisfano le condizioni WHERE dell’utente) potrebbero comunque finire per essere bloccate. È possibile utilizzare EXPLAIN per vedere quali condizioni vengono applicate a livello di relazione (e quindi non bloccano le righe) e quali no.

Una vista più complessa che non soddisfa tutte queste condizioni è di sola lettura per impostazione predefinita: il sistema non consente inserimenti, aggiornamento o eliminazioni sulla vista.

Nota

L’utente che esegue l’inserimento, l’aggiornamento o l’eliminazione sulla vista deve disporre del privilegio di inserimento, aggiornamento o eliminazione corrispondente sulla vista. Per impostazione predefinita, il proprietario della vista deve disporre dei privilegi pertinenti sulle relazioni di base sottostanti, mentre l’utente che esegue l’aggiornamento non necessita di alcuna autorizzazione sulle relazioni di base sottostanti. Tuttavia, se la vista ha l’opzione security_invoker impostata su true, è l’utente che esegue l’aggiornamento, anziché il proprietario della vista, che deve disporre dei privilegi pertinenti sulle relazioni di base sottostanti.

Esempi

Per creare una visualizzazione composta da tutti i film comici.

CREATE VIEW comedies AS
    SELECT *
    FROM films
    WHERE kind = 'Comedy';

Questa istruzione creerà una vista contenente le colonne presenti nella tabella film al momento della creazione della vista. Sebbene sia stato utilizzato il simbolo * per creare la vista, le colonne aggiunte successivamente alla tabella non faranno parte della vista.

Creazione di una vista con LOCAL CHECK OPTION.

CREATE VIEW pg_comedies AS
    SELECT *
    FROM comedies
    WHERE classification = 'PG'
    WITH CASCADED CHECK OPTION;

Questa istruzione creerà una vista che controlla kind e classification delle nuove righe.

Creazione di una vista con una combinazione di colonne aggiornabili e non aggiornabili.

CREATE VIEW comedies AS
    SELECT f.*,
           country_code_to_name(f.country_code) AS country,
           (SELECT avg(r.rating)
            FROM user_ratings r
            WHERE r.film_id = f.id) AS avg_rating
    FROM films f
    WHERE f.kind = 'Comedy';

Questa vista supporta INSERT, UPDATE e DELETE. Tutte le colonne della tabella dei film saranno aggiornabili, mentre le colonne calcolate country e avg_rating saranno di sola lettura.

CREATE RECURSIVE VIEW public.nums_1_100 (n) AS
    VALUES (1)
UNION ALL
    SELECT n+1 FROM nums_1_100 WHERE n < 100;

Nota

Sebbene il nome della vista ricorsiva sia qualificato dallo schema in questo comando CREATE, il suo autoreferenziamento interno non è qualificato dallo schema. Questo perché il nome Common Table Expression (CTE) creato implicitamente non può essere qualificato dallo schema.

Compatibilità

CREATE OR REPLACE VIEW è un’estensione del linguaggio PostgreSQL. Anche la WITH ( ... ) clausola è un’estensione, così come le viste con guardabarriere e le viste con security invoker. Aurora DSQL supporta queste estensioni del linguaggio.

ALTER VIEW

L’istruzione ALTER VIEW consente di modificare varie proprietà di una vista esistente e Aurora DSQL supporta tutta la sintassi PostgreSQL per questo comando.

Sintassi supportata

ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER VIEW [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER VIEW [ IF EXISTS ] name RENAME [ COLUMN ] column_name TO new_column_name
ALTER VIEW [ IF EXISTS ] name RENAME TO new_name
ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema
ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] )
ALTER VIEW [ IF EXISTS ] name RESET ( view_option_name [, ... ] )

Description

ALTER VIEW modifica varie proprietà ausiliarie di una vista. (se si intende modificare la query di definizione della vista, utilizzare CREATE OR REPLACE VIEW). Per utilizzare ALTER VIEW è necessario essere proprietari della vista da utilizzare. Per modificare lo schema di una vista, bisogna anche disporre del privilegio CREATE sul nuovo schema. Per modificare il proprietario, bisogna essere in grado di eseguire l’istruzione SET ROLE sul nuovo ruolo proprietario e tale ruolo deve disporre del privilegio CREATE sullo schema della vista. Queste restrizioni impongono che la modifica del proprietario non comporti alcun effetto che non si possa ottenere eliminando e ricreando la visualizzazione.

Parametri

Parametri di ALTER VIEW

name

Il nome (facoltativamente qualificato dallo schema) di una vista esistente.

column_name

Nuovo nome per una colonna esistente.

IF EXISTS

Non generare un errore se la vista non esiste. In questo caso viene emesso un avviso.

SET/DROP DEFAULT

Questi moduli impostano o rimuovono il valore predefinito per una colonna. Il valore predefinito per una colonna di visualizzazione viene sostituito in qualsiasi comando INSERT o UPDATE in cui la destinazione è la vista. Il valore predefinito per la vista avrà la precedenza su qualsiasi valore predefinito delle relazioni sottostanti.

new_owner

Il nome utente del nuovo proprietario della vista.

new_name

Il nuovo nome della vista.

new_schema

Il nuovo schema della vista.

SET ( view_option_name [= view_option_value] [, ... ] )

RESET ( view_option_name [, ... ] )

Imposta o reimposta un’opzione della vista. Di seguito sono riportate le opzioni supportate.

• check_option (enum) - Modifica l’opzione di controllo della vista. Il valore deve essere local o cascaded.

• security_barrier (boolean) - Modifica la proprietà guardabarriere della vista. Il valore deve essere un valore booleano, come true o false.

• security_invoker (boolean) - Modifica la proprietà guardabarriere della vista. Il valore deve essere un valore booleano, come true o false.

Note

Per ragioni storiche di PostgreSQL, ALTER TABLE può essere utilizzato anche sulle viste, ma le uniche varianti di ALTER TABLE consentite sulle viste sono equivalenti a quelle mostrate in precedenza.

Esempi

Ridenominazione della vista foo in bar.

ALTER VIEW foo RENAME TO bar;

Associazione di un valore di colonna predefinito a una vista aggiornabile.

CREATE TABLE base_table (id int, ts timestamptz);
CREATE VIEW a_view AS SELECT * FROM base_table;
ALTER VIEW a_view ALTER COLUMN ts SET DEFAULT now();
INSERT INTO base_table(id) VALUES(1);  -- ts will receive a NULL
INSERT INTO a_view(id) VALUES(2);  -- ts will receive the current time
    

Compatibilità

ALTER VIEW è un’estensione PostgreSQL dello standard SQL supportato da Aurora DSQL.

DROP VIEW

L’istruzione DROP VIEW rimuove una vista esistente. Aurora DSQL supporta la sintassi PostgreSQL completa per questo comando.

Sintassi supportata

DROP VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

Description

DROP VIEW elimina una vista esistente. Per eseguire questo comando è necessario essere proprietari della vista.

Parametri

IF EXISTS

Non generare un errore se la vista non esiste. In questo caso viene emesso un avviso.

name

Il nome (facoltativamente qualificato dallo schema) della vista da rimuovere.

CASCADE

Rilascia automaticamente gli oggetti che dipendono dalla vista (come le altre viste) e, a loro volta, tutti gli oggetti che dipendono da tali oggetti.

RESTRICT

Rifiuta di eliminare la vista se alcuni oggetti dipendono da essa. Questa è l’impostazione predefinita.

Esempi

DROP VIEW kinds;          

Compatibilità

Questo comando è conforme allo standard SQL, tranne per il fatto che lo standard consente di eliminare una sola vista per comando e a parte l’opzione IF EXISTS, che è un’estensione PostgreSQL supportata da Aurora DSQL.