examples for functions

[cstmgl]

Hi all,
I'm trying to migrate some functions to terraform and I have some questions.
I'll probably going to make it work but I was wondering what exactly is the syntax to create them?
Unfortunately I did not see any example in the documentation.

As an example this is my function in normal SQL:

CREATE OR REPLACE FUNCTION PROC.ARRAY_MAX(A ARRAY) RETURNS VARCHAR LANGUAGE JAVASCRIPT AS
$$ return ''+(A.sort().reverse().shift() ?? ''); $$;

This is my latest test on how to try and make it into terraform:

resource "snowflake_function" "proc_array_max" {
  arguments {
    name = "A"
    type = "ARRAY"
  }
  provider    = snowflake.datavault_sysadmin
  for_each    = local.databases
  database    = each.value
  name        = "ARRAY_MAX"
  return_type = "VARCHAR"
  schema      = "PROC"
  language    = "javascript"
  statement   = <<-EOT
    $$ return ''+(A.sort().reverse().shift() ?? ''); $$;
  EOT
  depends_on  = [snowflake_schema.proc]
}

And I'm getting:

08:59:23  �[31m│�[0m �[0m�[1m�[31mError: �[0m�[0m�[1merror creating function ARRAY_MAX: 001003 (42000): SQL compilation error:
08:59:23  �[31m│�[0m �[0msyntax error line 1 at position 178 unexpected 'return'.�[0m

Previously I tried what was more intuitive to me:

  statement   = <<-EOT
    return ''+(A.sort().reverse().shift() ?? '');
  EOT

But that also got me an error:

08:41:31  �[31m│�[0m �[0m�[1m�[31mError: �[0m�[0m�[1merror creating function ARRAY_MAX: 090203 (42601): Compilation of SQL UDF failed: SQL compilation error:
08:41:31  �[31m│�[0m �[0msyntax error line 1 at position 8 unexpected ''''.
08:41:31  �[31m│�[0m �[0msyntax error line 1 at position 20 unexpected '.'.�[0m

Anyway I was wondering if I could find any examples of how to create functions with some simple code I'm struggling to find the correct syntax

[cstmgl]

I think my first guess was close enough, this seems to work:

resource "snowflake_function" "proc_array_max" {
  arguments {
    name = "A"
    type = "ARRAY"
  }
  provider    = snowflake.datavault_sysadmin
  for_each    = local.databases
  database    = each.value
  name        = "ARRAY_MAX"
  return_type = "VARCHAR"
  schema      = "PROC"
  language    = "javascript"
  statement   = <<-EOT
    return ''+(A.sort().reverse().shift() ?? '');
  EOT
  depends_on  = [snowflake_schema.proc]
}

It probably, at least in my mind, would be nice to have an example in the actual documentation

[Dinesh-Rupchandani]

Hello,

Can you Please provide similar example for SQL Language also

Regards,