Bind Variables as Identifiers¶

In Snowflake, in addition to identifying objects by name (as detailed in Identifier Requirements), identifiers can also be specified with string literals, session variables, or bind variables, using the IDENTIFIER keyword.

Syntax¶

IDENTIFIER( { string_literal | session_variable | bind_variable } )

string_literal

String identifying the name of the object:

The string must either be enclosed by single quotes ('name') or start with a dollar sign ($name).
The string literal can be a fully-qualified object name (e.g. 'db_name.schema_name.object_name' or $db_name.schema_name.object_name).

session_variable

A SQL variable that has been set for the session.

bind_variable

A bind variable, in the form of ? or :variable, which can be used by clients/programmatic interfaces that support binding (JDBC, ODBC, Python, etc.).

Usage Notes¶

Literals and variables (session or bind) can be used anywhere an object can be identified by name (queries, DML, DDL, etc.).
Object identifier binds can be used in the same query with value binds.

Examples¶

String literals:

create or replace database identifier('my_db');

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

create or replace schema identifier('my_schema');

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

-- case-insensitive table name specified in a string containing the fully-qualified name
create or replace table identifier('my_db.my_schema.my_table') (c1 number);

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

-- case-sensitive table name specified in a double-quoted string
create or replace table identifier('"my_table"') (c1 number);

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

show tables in schema identifier('my_schema');

+---------------------------------+----------+---------------+-------------+-------+---------+------------+------+-------+----------+----------------+
|           created_on            |   name   | database_name | schema_name | kind  | comment | cluster_by | rows | bytes |  owner   | retention_time |
+---------------------------------+----------+---------------+-------------+-------+---------+------------+------+-------+----------+----------------+
| Tue, 05 Dec 2017 12:16:18 -0800 | MY_TABLE | MY_DB         | MY_SCHEMA   | TABLE |         |            | 0    | 0     | SYSADMIN | 1              |
| Tue, 05 Dec 2017 12:16:59 -0800 | my_table | MY_DB         | MY_SCHEMA   | TABLE |         |            | 0    | 0     | SYSADMIN | 1              |
+---------------------------------+----------+---------------+-------------+-------+---------+------------+------+-------+----------+----------------+

Session variables:

This shows how to use a session variable that has a table name or schema name:

set schema_name = 'my_db.my_schema';

set table_name = 'my_table';

use schema identifier($schema_name);

insert into identifier ($table_name) values (1), (2), (3);

select * from identifier($table_name) order by 1;

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

This shows how to use a session variable that has a function name. The function is named speed_of_light.

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

Call the function by name:

SELECT SPEED_OF_LIGHT();
+------------------+
| SPEED_OF_LIGHT() |
|------------------|
|           186250 |
+------------------+

Call the function by using the IDENTIFIER() syntax:

SET MY_FUNCTION_NAME = 'speed_of_light';

SELECT IDENTIFIER($MY_FUNCTION_NAME)();
+---------------------------------+
| IDENTIFIER($MY_FUNCTION_NAME)() |
|---------------------------------|
|                          186250 |
+---------------------------------+

Bind variables:

This example shows how to bind a function name in JDBC. The function is named 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 $$ 186250 $$";
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 (MPH) = " + rs.getInt(1));
}

The following examples show a variety of SQL statements that can use binding, and a variety of database objects that can be bound (including schema names and table names):

use schema identifier(?);

create or replace table identifier(?) (c1 number);

insert into table identifier(?) values (?), (?), (?);

select t2.c1 from identifier(?) as t1, identifier(?) as t2 where t1.c1 = t2.c1 and t1.c1 > (?);

drop table identifier(?);