CREATE TEMPORARY FUNCTION¶

Creates a transaction-scoped user-defined function that is automatically dropped when the transaction commits.

Syntax¶

CREATE [OR REPLACE] TEMPORARY FUNCTION function_name (
    [IN] parameter_name data_type [DEFAULT default_value], ...
) ON COMMIT DROP FUNCTION AS query

Parameters¶

OR REPLACE: If specified, replaces an existing temporary function with the same name in the current transaction. Without this clause, creating a function with a name that already exists raises an error.
function_name: The name of the temporary function to create. Scoped to the current transaction.
parameter_name: The name of a function parameter. Parameters can be referenced in the function body using their names.
data_type: The SQL data type of the parameter (e.g., BIGINT, STRING, INTEGER).
default_value: Optional default value for the parameter. If provided, the parameter becomes optional when calling the function.
query: The SQL SELECT statement that defines the function body.

Returns¶

Returns the result of executing the function’s query with the provided parameter values.

Examples¶

Setup¶

For these examples, assume we have a restaurants table:

CREATE TABLE restaurants(
    rest_no BIGINT,
    name STRING,
    city STRING,
    PRIMARY KEY(rest_no))

INSERT INTO restaurants VALUES
    (1001, 'The Burger Place', 'New York'),
    (1002, 'Pizza Palace',     'New York'),
    (2001, 'Sushi World',      'San Francisco'),
    (2002, 'Taco Town',        'San Francisco')

Basic Temporary Function¶

Create a temporary function scoped to the current transaction:

CREATE TEMPORARY FUNCTION high_no_restaurants()
ON COMMIT DROP FUNCTION AS
    SELECT * FROM restaurants WHERE rest_no > 1000

Call the function like a regular table-valued function:

SELECT * FROM high_no_restaurants()

The function is automatically dropped when the transaction commits.

Replacing an Existing Temporary Function¶

Use OR REPLACE to redefine a temporary function within the same transaction:

CREATE OR REPLACE TEMPORARY FUNCTION high_no_restaurants()
ON COMMIT DROP FUNCTION AS
    SELECT * FROM restaurants WHERE rest_no > 2000

Temporary Function with Parameters¶

Temporary functions support the same parameter syntax as permanent functions:

CREATE TEMPORARY FUNCTION restaurants_in_city(IN target_city STRING)
ON COMMIT DROP FUNCTION AS
    SELECT rest_no, name FROM restaurants WHERE city = target_city

SELECT * FROM restaurants_in_city('New York')

`rest_no`	`name`
`1001`	`"The Burger Place"`
`1002`	`"Pizza Palace"`

Dropping Early¶

A temporary function can be dropped explicitly before the transaction commits using DROP TEMPORARY FUNCTION:

DROP TEMPORARY FUNCTION high_no_restaurants

Important Notes¶

Transaction Scope¶

Temporary functions exist only for the duration of the transaction in which they are created. They are automatically dropped on commit and are not visible to other transactions or after the transaction ends.

Relationship to Permanent Functions¶

Temporary functions are distinct from permanent functions created with CREATE FUNCTION. Permanent functions are stored at the schema template level and persist across transactions. Creating a temporary function with the same name as an existing permanent function raises a DUPLICATE_FUNCTION error.

Parameter Syntax¶

Temporary functions share the same parameter declaration syntax as permanent functions. See CREATE FUNCTION for full details on positional parameters, named parameters, and default values.