Table Functions¶
Table functions return a set of rows instead of a single scalar value. Table functions appear in the FROM clause of a SQL statement and cannot be used as scalar functions. Table functions take scalar expressions as input.
Note
You can also define table UDFs that return rows from a SQL query or JavaScript. For more information, see UDFs (User-Defined Functions).
In this Topic:
List of System-Defined Table Functions¶
Snowflake provides the following system-defined table functions:
Sub-category |
Function |
Notes |
---|---|---|
Data Loading |
For more information, see Loading Data into Snowflake. |
|
Data Generation |
||
Data Conversion |
||
Object Modeling |
||
Semi-structured Queries |
For more information, see Querying Semi-structured Data. |
|
Query Results |
Can be used to perform SQL operations on the output from another SQL operation (e.g. SHOW). |
|
Historical & Usage Information (Information Schema): |
||
User Login
|
||
Queries
|
||
Warehouse & Storage Usage
|
||
Column-level Security
|
||
Database Replication
|
DATABASE_REFRESH_HISTORY DATABASE_REFRESH_PROGRESS , DATABASE_REFRESH_PROGRESS_BY_JOB REPLICATION_USAGE_HISTORY |
For more information, see Replicating Databases Across Multiple Accounts |
Data Loading & Transfer
|
||
Data Clustering (within Tables)
|
For more information, see Automatic Clustering |
|
External Functions
|
For more information, see External Functions |
|
External Tables Maintenance
|
For more information, see Working with External Tables |
|
Materialized Views Maintenance
|
For more information, see Working with Materialized Views |
|
SCIM Maintenance
|
For more information, see Auditing with SCIM |
|
Search Optimization Maintenance
|
For more information, see Using the Search Optimization Service |
|
Tasks Maintenance
|
For more information, see Executing SQL Statements on a Schedule Using Tasks |
|
Syntax¶
SELECT ...
FROM [ <input_table> [ [AS] <alias_1> ] ,
[ LATERAL ]
]
TABLE( <table_function>( [ <arg_1> [, ... ] ] ) ) [ [ AS ] <alias_2> ];
For function-specific syntax, see the documentation for the individual system-defined table functions.
Usage Notes¶
Table functions can also be applied to a set of rows using the LATERAL construct.
To enable using table expressions, Snowflake supports ANSI/ISO standard syntax for table expressions in the FROM clause of queries and subqueries. This syntax is used to indicate that an expression returns a collection of rows instead of a single row.
This ANSI/ISO syntax is valid only in the FROM clause of the SELECT list. You cannot omit these keywords and parentheses from a collection subquery specification in any other context.