google bigquery - Is there a way to procedure results as a subquery?

Question

The problem I am trying to tackle is to be able to call a procedure and run SQL commands on results (like select). Example.

Procedure

-- BQ_procedure
-- Arguments arg1 STRING, arg2 STRING, arg3 STRING, arg4 STRING
SELECT id,
    time,
    text
FROM `bigquery-public-data.hacker_news.comments`
WHERE text in ([arg1, arg2, arg3, arg4]);

Use the results of a procedure in a subquery.

SELECT id,
    time,
    FROM (
        CALL `my_project.my_dataset.BQ_procedure`(
            'stock',
            'overflow',
            'help',
            'me',
        )
    );

Is it possible? Does it make sense?

question from:https://stackoverflow.com/questions/65839184/is-there-a-way-to-procedure-results-as-a-subquery

Answer 1

What you're asking for is normally called a Table-value function or parameterized view. There is no such capability in BigQuery right now.

But your query may be written as a regular view as far as I can see like:

CREATE VIEW yourView AS SELECT id,
    time,
    text
FROM `bigquery-public-data.hacker_news.comments`;

SELECT id,
    time,
FROM yourView where text in [
            'stock',
            'overflow',
            'help',
            'me' ]