Littéraux et variables comme identificateurs avec la syntaxe IDENTIFIER()

Dans les instructions SQL Snowflake, en plus de faire référence aux objets par leur nom (voir Exigences relatives à l’identificateur), vous pouvez également utiliser un littéral de chaîne, une variable de session, une variable de liaison ou une variable Exécution de scripts Snowflake pour faire référence à un objet. Par exemple, vous pouvez utiliser une variable de session définie comme le nom d’une table dans la clause FROM d’une instruction SELECT. Pour utiliser un nom d’objet spécifié dans un littéral ou une variable, utilisez la syntaxe IDENTIFIER().

Utiliser IDENTIFIER() pour identifier les objets de base de données est une bonne pratique car cela peut rendre le code plus réutilisable et aider à prévenir les risques d”injection SQL.

Syntaxe

IDENTIFIER( { string_literal | session_variable | bind_variable | snowflake_scripting_variable } )
string_literal

Chaîne identifiant le nom de l’objet :

  • La chaîne doit être délimitée par des guillemets simples ('name') ou commencer par un signe dollar ($name).

  • Le littéral de chaîne peut être un nom d’objet complet (par exemple 'db_name.schema_name.object_name' ou $db_name.schema_name.object_name).

session_variable

Une variable SQL qui a été définie pour la session.

bind_variable

Variable de liaison, sous la forme de ? ou :variable, qui peut être utilisée par des interfaces client/programme qui prennent en charge la liaison (JDBC, ODBC, Python, etc.).

snowflake_scripting_variable

Variable Exécution de scripts Snowflake qui a été définie.

Notes sur l’utilisation

  • Vous pouvez utiliser des littéraux et des variables (session ou liaison) dans certains cas lorsque vous devez identifier un objet par son nom (requêtes, DML, DDL, etc.).

  • Vous pouvez utiliser des variables de liaison pour des identificateurs d’objets et des variables de liaison pour les valeurs dans la même requête.

  • Dans une clause FROM vous pouvez utiliser TABLE( { string_literal | session_variable | bind_variable | snowflake_scripting_variable } ) comme synonyme de IDENTIFIER( { string_literal | session_variable | bind_variable | snowflake_scripting_variable } ).

  • Bien que IDENTIFIER() utilise la syntaxe d’une fonction, il ne s’agit pas d’une vraie fonction. Des commandes comme SHOWFUNCTIONS ne la renvoient pas.

Exemples

Les exemples suivants utilisent la syntaxe IDENTIFIER().

Utilisation de la syntaxe IDENTIFIER() avec des chaînes littérales

Ces exemples montrent comment faire référence à un objet lorsqu’un littéral de chaîne contient l’identificateur de l’objet.

Créer une base de données :

CREATE OR REPLACE DATABASE IDENTIFIER('my_db');

+--------------------------------------+
| status                               |
|--------------------------------------|
| Database MY_DB successfully created. |
+--------------------------------------+

Créez un schéma :

CREATE OR REPLACE SCHEMA IDENTIFIER('my_schema');

+----------------------------------------+
| status                                 |
|----------------------------------------|
| Schema MY_SCHEMA successfully created. |
+----------------------------------------+

Créez une table en utilisant un nom de table insensible à la casse spécifié dans une chaîne contenant le nom complet :

CREATE OR REPLACE TABLE IDENTIFIER('my_db.my_schema.my_table') (c1 number);

+--------------------------------------+
| status                               |
|--------------------------------------|
| Table MY_TABLE successfully created. |
+--------------------------------------+

Créez une table en utilisant un nom de table sensible à la casse spécifié dans une chaîne entre guillemets doubles :

CREATE OR REPLACE TABLE IDENTIFIER('"my_table"') (c1 number);

+--------------------------------------+
| status                               |
|--------------------------------------|
| Table my_table successfully created. |
+--------------------------------------+

Affichez les tables dans un schéma :

SHOW TABLES IN SCHEMA IDENTIFIER('my_schema');

+-------------------------------+----------+---------------+-------------+-------+---------+---------+
| created_on                    | name     | database_name | schema_name | kind  | comment | ...     |
|-------------------------------+----------+---------------+-------------+-------+---------+---------|
| 2024-07-03 08:55:11.992 -0700 | MY_TABLE | MY_DB         | MY_SCHEMA   | TABLE |         | ...     |
| 2024-07-03 08:56:00.604 -0700 | my_table | MY_DB         | MY_SCHEMA   | TABLE |         | ...     |
+-------------------------------+----------+---------------+-------------+-------+---------+---------+

Utilisation de la syntaxe IDENTIFIER() avec les variables de session

Ces exemples vous montrent comment utiliser une variable de session qui contient un nom de table ou un nom de schéma.

Définissez une variable de session pour un nom de schéma :

SET schema_name = 'my_db.my_schema';

Définissez une variable de session pour un nom de table :

SET table_name = 'my_table';

Spécifiez le schéma pour la session en cours :

USE SCHEMA IDENTIFIER($schema_name);

Insérez des valeurs dans une table :

INSERT INTO IDENTIFIER($table_name) VALUES (1), (2), (3);

Interroger une table :

SELECT * FROM IDENTIFIER($table_name) ORDER BY 1;

+----+
| C1 |
|----|
|  1 |
|  2 |
|  3 |
+----+

Cet exemple montre comment utiliser une variable de session qui a un nom de fonction.

  1. Créez la fonction speed_of_light :

    CREATE FUNCTION speed_of_light() 
RETURNS INTEGER
AS
  $$
  299792458
  $$;

  2. Appelez la fonction par son nom :

    SELECT speed_of_light();

    +------------------+
| SPEED_OF_LIGHT() |
|------------------|
|        299792458 |
+------------------+

  3. Appelez la fonction en utilisant la syntaxe IDENTIFIER() :

    SET my_function_name = 'speed_of_light';

    SELECT IDENTIFIER($my_function_name)();

    +---------------------------------+
| IDENTIFIER($MY_FUNCTION_NAME)() |
|---------------------------------|
|                       299792458 |
+---------------------------------+

Utilisation de la syntaxe IDENTIFIER() avec des variables de liaison

Ces exemples montrent comment utiliser des variables de liaison pour identifier des objets.

Cet exemple vous montre comment lier un nom de fonction dans JDBC. La fonction s’appelle speed_of_light.

String sql_command;

// Create a Statement object to use later.
System.out.println("Create JDBC statement.");
Statement statement = connection.createStatement();
System.out.println("Create function.");
sql_command = "CREATE FUNCTION speed_of_light() RETURNS INTEGER AS $$ 299792458 $$";
statement.execute(sql_command);

System.out.println("Create prepared statement.");
sql_command = "SELECT IDENTIFIER(?)()";
PreparedStatement ps = connection.prepareStatement(sql_command);
// Bind
ps.setString(1, "speed_of_light");
ResultSet rs = ps.executeQuery();
if (rs.next()) {
  System.out.println("Speed of light (m/s) = " + rs.getInt(1));
}

Les exemples suivants présentent une variété d’instructions SQL qui peuvent utiliser la liaison et une variété d’objets de base de données qui peuvent être liés (y compris les noms de schéma et de table) :

USE SCHEMA IDENTIFIER(?);

CREATE OR REPLACE TABLE IDENTIFIER(?) (c1 NUMBER);

INSERT INTO IDENTIFIER(?) values (?), (?), (?);

SELECT t2.c1
  FROM IDENTIFIER(?) AS t1,
       IDENTIFIER(?) AS t2
  WHERE t1.c1 = t2.c1 AND t1.c1 > (?);

DROP TABLE IDENTIFIER(?);

Utilisation de la syntaxe IDENTIFIER() avec les variables de Snowflake Scripting

Cet exemple montre comment utiliser une variable Snowflake Scripting pour un nom de table dans une instruction SELECT :

BEGIN
  LET res RESULTSET := (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:table_name));
  ...