CODEWITHSUNDEEP
sqlgoogle-bigquery
Stewart_R
Stewart_R

Reputation: 14485

Pass parameters by name to BigQuery stored procedure or function

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?

Upvotes: 1

Views: 1371

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

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

Upvotes: 3

Related Questions