User defined data types in standard SQL (BigQuery)?

1
votes

Is it possible to create user defined data types in standard SQL, which can then be referenced from within the SQL code itself?

As an example, let's say I wanted to create a function that would take two instances of STRUCT - each containing three entries of type STRING. This would require writing something like this;

CREATE TEMP FUNCTION myFunc(x STRUCT<STRING, STRING, STRING>, y STRUCT<STRING, STRING, STRING>) AS (...);

What I want to do though, is something like this (pseudo-code);

CREATE TYPE myType STRUCT<STRING, STRING, STRING>
CREATE TEMP FUNCTION myFunc(x myType, y myType) AS (...)
1
sqlgoogle-bigquery
No unfortunately, it's not possible. You still have to use the first method. - Sabri Karagönen
If you think this would be a useful feature for BigQuery you can add a feature request here: issuetracker.google.com/issues/… - Nathan Griffiths

1 Answers

1
votes

Something similar would be using ANY TYPE to create your function like:

CREATE TEMP FUNCTION myFunc(x ANY TYPE, y ANY TYPE) AS (...);

To answer question in comments, if strong type check is needed, you can still do something like this and use to check parameter of myFunc() to report an error if type is incompatible:

CREATE TEMP FUNCTION check_type_x(x ANY TYPE) AS (STRUCT(x.a, x.b, x.c));


SELECT myFunc(check_type_x(col1), check_type_x(col2));