[Bug]: SQL Procedure definitions wrapped in $$ that contain $$ lead to syntax error

[kharigardner]

Terraform CLI Version

1.5.7

Terraform Provider Version

1.0.3

Company Name

PowerSchool

Terraform Configuration

"snowflake_procedure_sql": {
      "procedure_SBX_DATA_ADMINISTRATIONCALLABLESSPROC__DYNAMIC_TABLE_REFRESH": {
        "//": {
          "metadata": {
            "path": "data_platform_sbx/procedure_SBX_DATA_ADMINISTRATION.CALLABLES.SPROC__DYNAMIC_TABLE_REFRESH",
            "uniqueId": "procedure_SBX_DATA_ADMINISTRATIONCALLABLESSPROC__DYNAMIC_TABLE_REFRESH"
          }
        },
        "arguments": [
        ],
        "comment": "This stored procedure is used to refresh dynamic tables based on the environment manually.",
        "database": "SBX_DATA_ADMINISTRATION",
        "depends_on": [
          "module.aws_hcl_module",
          "module.snowflake_hcl_module"
        ],
        "execute_as": "CALLER",
        "name": "SPROC__DYNAMIC_TABLE_REFRESH",
        "null_input_behavior": "RETURNS NULL ON NULL INPUT",
        "procedure_definition": "DECLARE -- noqa: PRS\n    DYN_TABLES RESULTSET;\n    RESOLVED_ENVIRONMENT VARCHAR;\nBEGIN\n    -- First query will be our master table, what we use to loop through\n    SHOW DYNAMIC TABLES IN ACCOUNT;\n    \n    -- If this evaluates to PRD, we want to refresh all tables in the production environment\n    RESOLVED_ENVIRONMENT := IFF(CURRENT_DATABASE() = 'DATA_ADMINISTRATION', 'PRD', SPLIT_PART(CURRENT_DATABASE(), '_', 1));\n    \n    -- Now we can assign the query to this result set, we only want production tables\n    DYN_TABLES := (\n        SELECT CONCAT_WS('.', \"database_name\", \"schema_name\", \"name\") AS FULLY_QUALIFIED_NAME \n        FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) \n        WHERE \"database_name\" ILIKE :RESOLVED_ENVIRONMENT || '%'\n    );\n\n    -- Assign the cursor for the resultset\n    LET DYN_CURSOR CURSOR FOR DYN_TABLES;\n\n    -- Now, we can loop through the resultset and refresh each table\n    FOR RECORD IN DYN_CURSOR DO\n        BEGIN\n        -- First thing, dynamically create a task to refresh the table\n            EXECUTE IMMEDIATE $$\n                CREATE OR REPLACE TASK SPROC__DYNAMIC_TABLE_REFRESH__MIDNIGHT_PST__ || RECORD.FULLY_QUALIFIED_NAME\n                AS (\n                    EXECUTE IMMEDIATE 'ALTER DYNAMIC TABLE ' || RECORD.FULLY_QUALIFIED_NAME || ' REFRESH;'\n                );\n            $$;\n            \n            SYSTEM$LOG_INFO('Created task to refresh dynamic table: ' || RECORD.FULLY_QUALIFIED_NAME);\n            \n            EXECUTE IMMEDIATE 'EXECUTE TASK SPROC__DYNAMIC_TABLE_REFRESH__MIDNIGHT_PST__' || RECORD.FULLY_QUALIFIED_NAME;\n            \n            SYSTEM$LOG_INFO('Executed task to refresh dynamic table: ' || RECORD.FULLY_QUALIFIED_NAME);\n        EXCEPTION\n            WHEN STATEMENT_ERROR THEN\n                SYSTEM$LOG_WARN('Error refreshing dynamic table: ' || RECORD.FULLY_QUALIFIED_NAME);\n            WHEN OTHER THEN\n                RAISE;\n        END;\n    END FOR;\nEND;\n",
        "return_type": "string",
        "schema": "CALLABLES"
      },

Category

category:resource

Object type(s)

No response

Expected Behavior

When a Snowflake SQL procedure contains $$ to denote literals within a Snowflake scripting block, I expect the SQL synthesized to use th esimple CREATE PROCEDURE ... AS ... syntax, without covering the SQL block in $$ delimiter.

Actual Behavior

The procedure definition was wrapped in $$ as CREATE PROCEDURE ... AS $$ ... $$, leading to a query syntax error.

Steps to Reproduce

1. Copy the configuration or create a procedure resource with a scripting block that utilizes $$.
2. terraform apply
3. Apply fails

How much impact is this issue causing?

Low

Logs

No response

Additional Information

No response

Would you like to implement a fix?

• Yeah, I'll take it 😎

[kharigardner]

For the specific procedure above, heres the query Terraform synthesized to execute to create:

CREATE PROCEDURE "SBX_DATA_ADMINISTRATION"."CALLABLES"."SPROC__DYNAMIC_TABLE_REFRESH" () RETURNS STRING(16777216) LANGUAGE SQL RETURNS NULL ON NULL INPUT COMMENT = 'This stored procedure is used to refresh dynamic tables based on the environment manually.' EXECUTE AS CALLER AS $$DECLARE -- noqa: PRS
    DYN_TABLES RESULTSET;
    RESOLVED_ENVIRONMENT VARCHAR;
BEGIN
    -- First query will be our master table, what we use to loop through
    SHOW DYNAMIC TABLES IN ACCOUNT;
    
    -- If this evaluates to PRD, we want to refresh all tables in the production environment
    RESOLVED_ENVIRONMENT := IFF(CURRENT_DATABASE() = 'DATA_ADMINISTRATION', 'PRD', SPLIT_PART(CURRENT_DATABASE(), '_', 1));
    
    -- Now we can assign the query to this result set, we only want production tables
    DYN_TABLES := (
        SELECT CONCAT_WS('.', "database_name", "schema_name", "name") AS FULLY_QUALIFIED_NAME 
        FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) 
        WHERE "database_name" ILIKE :RESOLVED_ENVIRONMENT || '%'
    );

    -- Assign the cursor for the resultset
    LET DYN_CURSOR CURSOR FOR DYN_TABLES;

    -- Now, we can loop through the resultset and refresh each table
    FOR RECORD IN DYN_CURSOR DO
        BEGIN
        -- First thing, dynamically create a task to refresh the table
            EXECUTE IMMEDIATE $$
                CREATE OR REPLACE TASK SPROC__DYNAMIC_TABLE_REFRESH__MIDNIGHT_PST__ || RECORD.FULLY_QUALIFIED_NAME
                AS (
                    EXECUTE IMMEDIATE 'ALTER DYNAMIC TABLE ' || RECORD.FULLY_QUALIFIED_NAME || ' REFRESH;'
                );
            $$;
            
            SYSTEM$LOG_INFO('Created task to refresh dynamic table: ' || RECORD.FULLY_QUALIFIED_NAME);
            
            EXECUTE IMMEDIATE 'EXECUTE TASK SPROC__DYNAMIC_TABLE_REFRESH__MIDNIGHT_PST__' || RECORD.FULLY_QUALIFIED_NAME;
            
            SYSTEM$LOG_INFO('Executed task to refresh dynamic table: ' || RECORD.FULLY_QUALIFIED_NAME);
        EXCEPTION
            WHEN STATEMENT_ERROR THEN
                SYSTEM$LOG_WARN('Error refreshing dynamic table: ' || RECORD.FULLY_QUALIFIED_NAME);
            WHEN OTHER THEN
                RAISE;
        END;
    END FOR;
END;
$$ --terraform_provider_usage_tracking {"json_schema_version":"1","version":"v1.0.3","resource":"snowflake_procedure_sql","operation":"create"}

[sfc-gh-jcieslak]

Hey @kharigardner 👋
As far as I know, you should be able to use single quotes. Single quotes and double-dollar just have different constraints you can read about here. You also have a recent discussion with the same issues that may help you: #3413. Also, the same issues will appear in Snowsight when running this query, which means it's not directly connected to Terraform Provider, but rather Snowflake itself which means the request should be redirected to the Snowflake support in this case, but before that please visit the linked threads and see if you are able to construct a valid query.