_typeof.sql

CREATE TEMP FUNCTION typeof_literal(input STRING)
AS (
      CASE
        -- Process NUMERIC, DATE, DATETIME, TIME, TIMESTAMP,
        WHEN REGEXP_CONTAINS(input, r'^[A-Z]+ "') THEN REGEXP_EXTRACT(input, r'^([A-Z]+) "')
        WHEN REGEXP_CONTAINS(input, r'^-?[0-9]*$') THEN 'INT64'
        WHEN REGEXP_CONTAINS(input, r'^(-?[0-9]+[.e].*|CAST\("([^"]*)" AS FLOAT64\))$') THEN 'FLOAT64'
        WHEN input IN ('true', 'false') THEN 'BOOL'
        WHEN input LIKE '"%' THEN 'STRING'
        WHEN input LIKE 'b"%' THEN 'BYTES'
        WHEN input LIKE '[%' THEN 'ARRAY'
        WHEN REGEXP_CONTAINS(input, r'^(STRUCT)?\(') THEN 'STRUCT'
        WHEN input LIKE 'ST_%' THEN 'GEOGRAPHY'
        WHEN input = 'NULL' THEN 'NULL'
      ELSE
      'UNKNOWN'
    END );

CREATE TEMP FUNCTION typeof(input ANY TYPE)
AS ( typeof_literal(FORMAT('%T', input)) );

CREATE TEMP FUNCTION to_literal(input ANY TYPE)
AS ( FORMAT('%T', input) );

CREATE TEMP FUNCTION typeof_test(input ANY TYPE, expect STRING)
AS ( STRUCT<literal STRING, type STRING, test_result BOOL>(to_literal(input), typeof(input), typeof(input) = expect) );

SELECT typeof_test(b'test', 'BYTES').*
UNION ALL SELECT typeof_test('test', 'STRING').*
UNION ALL SELECT typeof_test(TRUE, 'BOOL').*
UNION ALL SELECT typeof_test(FALSE, 'BOOL').*
UNION ALL SELECT typeof_test(NULL, 'NULL').*
UNION ALL SELECT typeof_test(1, 'INT64').*
UNION ALL SELECT typeof_test(-1, 'INT64').*
UNION ALL SELECT typeof_test(1.0, 'FLOAT64').*
UNION ALL SELECT typeof_test(-1.0, 'FLOAT64').*
UNION ALL SELECT typeof_test(+123e45, 'FLOAT64').*
UNION ALL SELECT typeof_test(-123e-45, 'FLOAT64').*
UNION ALL SELECT typeof_test(1e+234, 'FLOAT64').*
UNION ALL SELECT typeof_test(-1e-234, 'FLOAT64').*
UNION ALL SELECT typeof_test(IEEE_DIVIDE(1, 0), 'FLOAT64').*
UNION ALL SELECT typeof_test(IEEE_DIVIDE(-1, 0), 'FLOAT64').*
UNION ALL SELECT typeof_test(IEEE_DIVIDE(0, 0), 'FLOAT64').*
UNION ALL SELECT typeof_test(DATE '1970-01-01', 'DATE').*
UNION ALL SELECT typeof_test(DATETIME '1970-01-01T00:00:00', 'DATETIME').*
UNION ALL SELECT typeof_test(TIME '00:00:00', 'TIME').*
UNION ALL SELECT typeof_test(TIMESTAMP '1970-01-01T00:00:00Z', 'TIMESTAMP').*
UNION ALL SELECT typeof_test((1, 2, 3), 'STRUCT').*
UNION ALL SELECT typeof_test(STRUCT<>(), 'STRUCT').*
UNION ALL SELECT typeof_test(STRUCT<a INT64, b INT64, c INT64>(1, 2, 3), 'STRUCT').*
UNION ALL SELECT typeof_test([1, 2, 3], 'ARRAY').*
UNION ALL SELECT typeof_test(ARRAY<INT64>[1, 2, 3], 'ARRAY').*
UNION ALL SELECT typeof_test(ST_GEOGPOINT(0, 0), 'GEOGRAPHY').*
UNION ALL SELECT typeof_test(NUMERIC '0', 'NUMERIC').*;

SELECT literal, typeof_literal(literal) AS type, typeof_literal(literal) = expect AS test_result
FROM UNNEST(ARRAY<STRUCT<literal STRING, expect STRING>>[
  (to_literal(b'test'), "BYTES"),
  (to_literal('test'), "STRING"),
  (to_literal(TRUE), "BOOL"),
  (to_literal(FALSE), "BOOL"),
  (to_literal(NULL), "NULL"),
  (to_literal(1), "INT64"),
  (to_literal(-1), "INT64"),
  (to_literal(1.0), "FLOAT64"),
  (to_literal(-1.0), "FLOAT64"),
  (to_literal(1e234), "FLOAT64"),
  (to_literal(-1e-234), "FLOAT64"),
  (to_literal(123e+45), "FLOAT64"),
  (to_literal(-123e-45), "FLOAT64"),
  (to_literal(IEEE_DIVIDE(1, 0)), "FLOAT64"),
  (to_literal(IEEE_DIVIDE(-1, 0)), "FLOAT64"),
  (to_literal(IEEE_DIVIDE(0, 0)), "FLOAT64"),
  (to_literal(DATE '1970-01-01'), "DATE"),
  (to_literal(DATETIME '1970-01-01T00:00:00'), "DATETIME"),
  (to_literal(TIME '00:00:00'), "TIME"),
  (to_literal(TIMESTAMP '1970-01-01T00:00:00Z'), "TIMESTAMP"),
  (to_literal((1, 2, 3)), "STRUCT"),
  (to_literal(STRUCT<>()), "STRUCT"),
  (to_literal(STRUCT<a INT64, b INT64, c INT64>(1, 2, 3)), "STRUCT"),
  (to_literal([1, 2, 3]), "ARRAY"),
  (to_literal(ARRAY<INT64>[1, 2, 3]), "ARRAY"),
  (to_literal(ST_GEOGPOINT(0, 0)), "GEOGRAPHY"),
  (to_literal(NUMERIC '0'), "NUMERIC")
]);

result.png