How to use string argument in Select statement in BigQuery UDF?

Question

This is my code for Google BigQuery UDF.

CREATE OR REPLACE FUNCTION `my_dataset.ST_EXTENT_TABLE`
(tableName STRING)
RETURNS STRUCT<xmin FLOAT64, ymin FLOAT64, xmax FLOAT64, ymax FLOAT64>
AS (
    WITH data AS ( SELECT geom FROM tableName)
    SELECT ST_EXTENT(geom) AS box FROM data
);

In "SELECT geom FROM tableName", tableName should be replaced with passed value.

Any idea?

Answer 1

According to your requirement, it is not possible to dynamically pass the table name in UDF. As an alternative you can write a dynamic sql query where you can pass the table name dynamically. Using Execute Immediate you can execute those dynamic queries and get the result.

You can try the below sample query.

Query:

DECLARE query String;
SET
  query = '''with cte as (
    select filename from %s limit 10
)
select * from cte''';
EXECUTE IMMEDIATE
  FORMAT(query,"bigquery-public-data.bbc_news.fulltext")

Output: