I´m working with BIGQUERY trying to produce a view using a query with a Function (This is an example not the query itself)
CREATE TEMP FUNCTION validate_rut(s string)
RETURNS string
AS (
if(length(s) = 10 or length(s) = 12 , left(regexp_replace(s, r'[.-]', ''), 8)
, if(length(s) = 11 or length(s) = 9, left(regexp_replace(s, r'[.-]', ''), 7)
, null)
)
);
select rut, validate_rut(rut)
from (select '11.111.111-8' rut union all
select '11111111-8' union all
select '2.222.222-9' union all
select '2222222-9'union all
select '33333333' union all
select '7777777'
)
The problem is that when I try to save the query as a view I get this message.
"No support For create Temporary Function statements inside views"
This is the first time i´m trying to do something like this.
Thank You.
CodePudding user response:
You can create your function as permanant udf with the following syntax :
CREATE FUNCTION `my_project.my_dataset.validate_rut`(s string)
RETURNS string
AS (
if(length(s) = 10 or length(s) = 12 , left(regexp_replace(s, r'[.-]', ''), 8)
, if(length(s) = 11 or length(s) = 9, left(regexp_replace(s, r'[.-]', ''), 7)
, null)
)
);
In this example your permanent udf
is created on an existing Bigquery
dataset
called my_dataset
.
Then you can query your function as expected with the following syntax :
select rut, `my_project.my_dataset.validate_rut`(rut)
from (select '11.111.111-8' rut union all
select '11111111-8' union all
select '2.222.222-9' union all
select '2222222-9'union all
select '33333333' union all
select '7777777'
);