Table Valued Functions

[jimlloyd]

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request
• Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment. If the issue is assigned to the "modular-magician" user, it is either in the process of being autogenerated, or is planned to be autogenerated soon. If the issue is assigned to a user, that user is claiming responsibility for the issue. If the issue is assigned to "hashibot", a community member has claimed the issue already.

Description

Is there a way to define a BigQuery Table Valued Function with the current capabilities of this provider? I would expect it to be done by defining a google_bigquery_routine resource with a routine_type of TABLE_VALUED_FUNCTION. The BigQuery API RoutineType enum defines three values, but it appears that the google_bigquery_routine resources implements only two.

│ Error: expected routine_type to be one of [SCALAR_FUNCTION PROCEDURE ], got TABLE_VALUED_FUNCTION

New or Affected Resource(s)

• google_bigquery_routine

Potential Terraform Configuration

resource "google_bigquery_routine" "timeslot_range_proc" {
  ...
  routine_type = "TABLE_VALUED_FUNCTION"
  ...
}

I believe the existing arguments/attributes of google_bigquery_routine might be sufficient, but table-valued-functions produce tables so it may be necessary to declare the schema of the produced table along the lines used by google_bigquery_table.

References

• https://cloud.google.com/bigquery/docs/reference/rest/v2/routines#RoutineType

[jimlloyd]

I have figured out a way to use a google_bigquery_job to run a DDL query CREATE OR REPLACE TABLE FUNCTION ... so I am not blocked by the absence of this feature. But the workaround is an ugly hack.

In case anyone else ends up here looking for a way to create table-valued functions, this snippet illustrates how to use this workaround:

resource "google_bigquery_job" "create_timeslot_range" {
  job_id = "create_timeslot_range-${uuid()}"

  query {
    create_disposition = ""
    write_disposition = ""
    query = <<EOF
CREATE OR REPLACE TABLE FUNCTION `${var.gcp_project}.${google_bigquery_dataset.default.dataset_id}.timeslot_range`(time_start TIMESTAMP, time_end TIMESTAMP)
AS
  SELECT *
  FROM unnest(GENERATE_TIMESTAMP_ARRAY(time_start, time_end, INTERVAL 15 MINUTE)) as timeslot
EOF
  }
}

[Tei1988]

I would like to work on it.

[jamiet-msm]

This issue can now be closed can it not? I think this PR fixes it #10743

[melinath]

resolved by GoogleCloudPlatform/magic-modules#5489

[github-actions]

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues.
If you have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.