- Categories:
System Functions (Information)
SYSTEM$REFERENCE¶
Returns a reference to an object (a table, view, or function). When you execute SQL actions on a reference to an object, the actions are performed using the role of the user who created the reference.
- See also:
Syntax¶
SYSTEM$REFERENCE('<object_type>', '<object_identifier>',
[ , <reference_scope> [ , '<privilege>' [ , '<privilege>' ... ] ] ] )
Arguments¶
Required
object_type
Type of the object. You can specify one of the following values:
api_integration
database
external_table
function
materialized_view
pipe
procedure
schema
table
task
view
warehouse
object_identifier
Identifier for the object. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.
For more details about identifiers, see Identifier requirements.
Optional
reference_scope
Specifies the scope of the reference.
If
CALL
or omitted, specifies that the reference is valid within the context in which it was created. See Specifying the Scope of the Reference.If
SESSION
, specifies that the reference should be valid for the duration for the session.If
PERSISTENT
, specifies that the reference should be valid until the object is dropped. See persistent references.Note: If you need to specify the
privilege
argument, thereference_scope
argument is required.Valid values:
CALL
SESSION
PERSISTENT
Default value:
CALL
privilege
Additional privilege that is needed to perform an SQL action on the object.
For example, suppose that you are passing the reference for a table to a stored procedure that inserts rows into that table. Specify
'INSERT'
to confer the INSERT privilege on that table to the stored procedure.For a list of supported objects and privileges, see Supported object types and privileges for references.
To specify more than one additional privilege, pass each privilege name as an additional argument to the function. For example, to confer the INSERT, UPDATE, and TRUNCATE privileges:
CALL myprocedure( SYSTEM$REFERENCE('TABLE', 'table_with_different_owner', 'SESSION', 'INSERT'. 'UPDATE', 'TRUNCATE'));
Note that you cannot specify OWNERSHIP or ALL as privileges.
Returns¶
A serialized string representation of the reference that can be used as an identifier.
Examples¶
See A Simple Example.