Problem

You try to create a temporary view within a function using the following code.

CREATE TEMP VIEW <TEMP-VIEW-NAME> AS
SELECT <COLUMNS>
FROM <TABLE-NAME>
WHERE <CONDITION>;

CREATE FUNCTION <FUNCTION-NAME>(<PARAMETER-DEFINITION>)
RETURNS TABLE(<OUTPUT-COLUMNS-DEFINITION>)
RETURN WITH cte AS (
    SELECT <COLUMNS>
    FROM <TEMP-VIEW-NAME>
)
SELECT * FROM cte;

When you run the code, you receive an error.  

[INVALID_TEMP_OBJ_REFERENCE] Cannot create the persistent object `<CATALOG-NAME>`.`<SCHEMA-NAME>`.`<FUNCTION-NAME>` of the type FUNCTION because it references to the temporary object `<TEMP-VIEW-NAME>` of the type VIEW. SQLSTATE: 42K0F

Cause

A persistent object, such as a function, cannot reference a temporary object, such as a temporary view. Persistent objects have a longer lifespan and are available across different sessions, while temporary objects are session-specific and have a limited lifespan.

Solution

Make the function temporary so both the function and the temporary view have the same lifespan and session scope.

First, create a temporary view to filter or transform data from the persistent table. 

CREATE TEMP VIEW <TEMP-VIEW-NAME> AS
SELECT <COLUMNS>
FROM <TABLE-NAME>
WHERE <CONDITION>;

Next, create a temporary function that uses the temporary view. The function then takes a parameter and returns a table. 

CREATE TEMP FUNCTION <TEMP-FUNCTION-NAME>(<PARAMETER-DEFINITION>)
RETURNS TABLE(<OUTPUT-COLUMNS-DEFINITION>)
RETURN WITH cte AS (
    SELECT <COLUMNS>
    FROM <TEMP-VIEW-NAME>
)
SELECT * FROM cte;

Last, execute the temporary function. 

SELECT * FROM <TEMP-FUNCTION-NAME>(<PARAMETER-VALUE>);