functionsnowflake-cloud-data-platformtable-functions

I am trying to create a UDTF in Snowflake but I get an error on RETURNS TABLE even though it matches the syntax


CREATE OR REPLACE FUNCTION DQ_IS_NULL ("SCHEMA_NAME" VARCHAR(500), "TABLE_NAME" VARCHAR(500), "COLUMN_NAME" VARCHAR(500))
RETURNS TABLE (TABLE_NME VARCHAR(500), COLUMN_NME VARCHAR(500), ISSUE_CODE VARCHAR(100), PARAMETERS VARCHAR(500), VALUE VARCHAR (500), ROW_HASH NUMBER(19,0), EXECUTION_TS TIMESTAMP)
LANGUAGE SQL
AS
'
    SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    CASE 
        WHEN V IS NOT NULL
            THEN ''NULLed''
    END AS ISSUE_CODE,
    CASE 
        WHEN COLUMN_NAME IS NOT NULL
            THEN ''NULLS not permitted''
    END AS PARAMETERS,
    HASH(*) AS ROW_HASH,
    CURRENT_TIME() AS EXECUTION_TS
    FROM CONCAT(SCHEMA_NAME ,''.'',TABLE_NAME)
    WHERE COLUMN_NAME IS NULL

The function is intended to return all rows in a given table that are null


Solution

  • The problem is that you're using ' to denote the start of the function definition, but there is a ' within the body as well. You can use $$ to work around the issue:

    CREATE OR REPLACE TEMP FUNCTION DQ_IS_NULL ("SCHEMA_NAME" VARCHAR(500), "TABLE_NAME" VARCHAR(500), "COLUMN_NAME" VARCHAR(500))
    RETURNS TABLE (TABLE_NME VARCHAR(500), COLUMN_NME VARCHAR(500), ISSUE_CODE VARCHAR(100), PARAMETERS VARCHAR(500), VALUE VARCHAR (500), ROW_HASH NUMBER(19,0), EXECUTION_TS TIMESTAMP)
    LANGUAGE SQL
    AS
    $$
        SELECT 
        TABLE_NAME,
        COLUMN_NAME,
        CASE 
            WHEN V IS NOT NULL
                THEN 'NULLed'
        END AS ISSUE_CODE,
        CASE 
            WHEN COLUMN_NAME IS NOT NULL
                THEN 'NULLS not permitted'
        END AS PARAMETERS,
        HASH(*) AS ROW_HASH,
        CURRENT_TIME() AS EXECUTION_TS
        FROM CONCAT(SCHEMA_NAME ,''.'',TABLE_NAME)
        WHERE COLUMN_NAME IS NULL
    $$;
    

    Note that there's another issue still, however; you need to use a real table name in the UDTF body, as opposed to CONCAT(SCHEMA_NAME ,''.'',TABLE_NAME).