Literals and Variables as Identifiers¶

In Snowflake SQL statements, in addition to referring to objects by name (see Identifier Requirements), you can also use a string literal, session variable, bind variable, or Snowflake Scripting variable to refer to an object. For example, you can use a session variable that is set to the name of a table in the FROM clause of a SELECT statement.

To use an object name specified in a literal or variable, use IDENTIFIER().

Syntax¶

IDENTIFIER( { string_literal | session_variable | bind_variable | snowflake_scripting_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.).

snowflake_scripting_variable

A Snowflake Scripting variable that has been set.

Usage Notes¶

You can use literals and variables (session or bind) in some cases when you need to identify an object by name (queries, DML, DDL, etc.).
You can use bind variables for object identifiers and bind variables for values in the same query.
In a FROM clause, you can use TABLE( { string_literal | session_variable | bind_variable | snowflake_scripting_variable } ) as a synonym for IDENTIFIER( { string_literal | session_variable | bind_variable | snowflake_scripting_variable } ).
Although IDENTIFIER(...) uses the syntax of a function, it is not a true function and is not returned by commands such as SHOW FUNCTIONS.

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
    $$
    299792458
    $$;

Call the function by name:

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

Call the function by using the IDENTIFIER() syntax:

SET MY_FUNCTION_NAME = 'speed_of_light';

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

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 $$ 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));
}

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 identifier(?) values (?), (?), (?);

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

drop table identifier(?);

Snowflake Scripting variables:

This example shows how to use a Snowflake Scripting variable for a table name in a SELECT statement:
BEGIN
  LET res RESULTSET := (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:table_name));
  ...