Creating a View in BigQuery from Temporary Function and Dynamic SQL

Question

I want to create a view dynamically with a string generated by a temporary function. The code below fails with Creating views with temporary user-defined functions is not supported. How can I create the view without permanently registering the function?

CREATE TEMP FUNCTION CreateViewString()
  RETURNS STRING
  AS (("select 1 as col"));

EXECUTE IMMEDIATE "CREATE OR REPLACE VIEW `project.dataset.view` AS " || CreateViewString()

Answer 1

As a workaround one can store the result of the function and then drop the function.

DECLARE viewString STRING;

CREATE TEMP FUNCTION CreateViewString()
  RETURNS STRING
  AS (("select 1 as col"));

SET viewString = CreateViewString();

DROP FUNCTION CreateViewString;

EXECUTE IMMEDIATE "CREATE OR REPLACE VIEW `project.dataset.view` AS " || viewString

Answer 2

I'm posting this answer for posterity. This is a known issue that the BigQuery team is aware of and improvements have been under consideration. You can also STAR the issue to receive automatic updates and give it traction by referring to this link. Suggested workarounds are as follows.

1. Drop the UDF before view creation, this will prevent the script from attaching the UDF in the last statement. (mentioned in the answer by @p13rr0m)
2. If the view is not related to the UDF or temporary table execution, run them as separate queries.