[Bug]: SQL Function does not handle multi-line statements

[epmenard-ia]

Terraform CLI Version

1.9.4

Terraform Provider Version

0.95.0

Terraform Configuration

resource "snowflake_function" "TEST_PARSER_SQL_FUNC" {
  database = local.databases[var.DATABASE]
  schema = snowflake_schema.TEST_PARSER.name
  name = "SQL_FUNC"

  arguments {
    name = "arg1"
    type = "number"
  }
  comment             = "Example for SQL language"
  return_type         = "VARCHAR(30)"
  null_input_behavior = "CALLED ON NULL INPUT"
  return_behavior     = "VOLATILE"
  statement           = <<-EOT
    select 
      CONCAT('Result: ', arg1 + 1);
 EOT
}

Category

category:resource

Object type(s)

resource:function

Expected Behavior

The query should be able to handle carriage returns and should escape single quotes using a second single quote.

CREATE FUNCTION "DB_DPT_DEV_STG"."TEST_PARSER"."SQL_FUNC" (arg1 NUMBER) RETURNS VARCHAR VOLATILE COMMENT = 'Example for SQL language' AS 'select CONCAT(\'Result: \', arg1 + 1)'

Actual Behavior

request injects newlines

CREATE FUNCTION "DB_DPT_DEV_STG"."TEST_PARSER"."SQL_FUNC" (arg1 NUMBER) RETURNS VARCHAR VOLATILE COMMENT = 'Example for SQL language' AS 'select \n  CONCAT(\'Result: \', arg1 + 1)'

Compilation of SQL UDF failed: SQL compilation error: syntax error line 1 at position 1 unexpected 'select'. syntax error line 2 at position 9 unexpected ''Result: ''. syntax error line 2 at position 29 unexpected ')'.

Steps to Reproduce

1. Copy configuration
2. Replace database and schema
3. Terraform apply

How much impact is this issue causing?

High

Logs

No response

Additional Information

No response

Would you like to implement a fix?

• Yeah, I'll take it 😎

[epmenard-ia]

Nevermind...pitcher's mistake. Turns out the semicolon at the end of the statement was the culprit!!