Pass parameters by name to BigQuery stored procedure or function

user_2455494 0
Viewed 70

In BigQuery we usually pass parameters to stored procedures and functions by position something like this:

CREATE TEMP FUNCTION FullName(firstName STRING, lastName STRING) 
RETURNS STRING 
AS(
  firstName || ' ' || lastName
);

SELECT FullName("Fred", "Flintstone")

I would like to be able to not care about the position of the parameters when calling the function or stored procedure. Perhaps with something like this:

# This does not work
SELECT FullName("Flintstone" AS lastName, "Fred" AS firstName)

Unfortunately, this results in a syntax error. I have also tried a few other possibilities such as FullName(lastname="Flintstone", firstName="Fred"), etc and tried to search in the docs but came up blank.

Is there a mechanism for passing parameters by name in an arbitrary order like this?

google-bigquery sql
1 Answers

You were very close (I think) - Use below

CREATE TEMP FUNCTION FullName(name ANY TYPE) 
RETURNS STRING 
AS(
  name.firstName || ' ' || name.lastName
);
SELECT 
  FullName(STRUCT("Fred" AS firstName, "Flintstone" AS lastName)),
  FullName(STRUCT("Flintstone" AS lastName, "Fred" AS firstName))

with output

enter image description here

user_52219440
Related