String Literals / Session Variables / Bind Variables as Identifiers

In Snowflake, in addition to identifying objects by name (as detailed in Identifier Syntax), 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:

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  |
+----+

Bind variables:

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