SSC-FDM-PG0001

FOUND could have a different behavior in Snowflake in some scenarios.

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Severity

Low

Description

The FOUND property in PostgreSQL is a property based on the last executed query, it can be affected by some statements such as INSERT, UPDATE, DELETE, MERGE, SELECT INTO, PERFORM, FETCH and FOR loops. To read more details about this property, this is PostgreSQL documentation.

In Snowflake there is not a direct translation for this property, for the following scenarios:

• INSERT

• UPDATE

• DELETE

• MERGE

The converted code will be SQLFOUND Snowflake property (Here is the documentation) since it behaves like the PostgreSQL FOUND property.

For the other cases such as:

• SELECT INTO

• PERFORM

• FETCH

The converted code will be a custom UDF (IS_FOUND_UDF) that behaves like the PostgreSQL FOUND property.

This happens because SQLFOUND changes its value only when at least one row is affected by the last executed query, if the last query does not change any row, it does not change.

While the IS_FOUND_UDF only works for statements that returns rows, if no row is returned it, it will return FALSE.

SQLFOUND Example

INSERT INTO SampleTable (SampleColumn1)
VALUES ('SampleValue0.1');

The last query affects a table, so the SQLFOUND is the closest to the PostgreSQL functionality.

IS_FOUND_UDF Example

SELECT SampleColumn FROM SampleTable;

The last query will return a row but does not change anything, so the IS_FOUND_UDF() is the closest to the PostgreSQL functionality.

IS_FOUND_UDF Source Code

CREATE OR REPLACE FUNCTION FOUND_UDF() 
RETURNS BOOLEAN
LANGUAGE SQL
IMMUTABLE
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "udf",  "convertedOn": "09/09/2024" }}'
AS
$$
SELECT (count(*) != 0) FROM TABLE(result_scan(last_query_id()))
$$;

Code Example

Insert Statement:

PostgreSQL

IN -> PostgreSQL_01.sql

-- Found property used with INSERT statement.
CREATE OR REPLACE PROCEDURE FoundUsingInsertProcedure()
LANGUAGE plpgsql
AS $$
BEGIN
    -- Insert into SampleTable
    INSERT INTO SampleTable (SampleColumn1)
    VALUES ('SampleValue0.1');

    SELECT FOUND;
END;
$$;

Snowflake

Update Statement:

PostgreSQL

IN -> PostgreSQL_02.sql

-- Found property used with UPDATE statement.
CREATE OR REPLACE PROCEDURE FoundUsingUpdateProcedure()
LANGUAGE plpgsql
AS
$$
    BEGIN
        UPDATE SampleTable 
        SET SampleColumn1 = 'SampleValue0.1' 
        WHERE SampleColumn1 = 'SampleValue0.1';
        SELECT FOUND;        
    END;
$$;

Snowflake

Delete Statement:

PostgreSQL

IN -> PostgreSQL_03.sql

-- Found property used with DELETE statement.
CREATE OR REPLACE PROCEDURE FoundUsingDeleteProcedure()
LANGUAGE plpgsql
AS
$$
    BEGIN
        DELETE FROM SampleTable 
        WHERE SampleColumn1 = 'SampleValue0.1';
        SELECT FOUND;
    END;
$$;

Snowflake

Merge Statement:

PostgreSQL

IN -> PostgreSQL_04.sql

-- Found property used with MERGE statement.
CREATE OR REPLACE PROCEDURE FoundUsingMergeProcedure()
LANGUAGE plpgsql
AS
$$
    BEGIN
        MERGE INTO SampleTableB B
        USING (SELECT * FROM SampleTableA) A
        ON B.SampleColumn1 = A.SampleColumn2
        WHEN MATCHED THEN DELETE;
        SELECT FOUND;
    END;
$$;

Snowflake

Select Into Statement

PostgreSQL

IN -> PostgreSQL_05.sql

-- Found property used with SELECT INTO statement.
CREATE OR REPLACE PROCEDURE FoundUsingSelectIntoProcedure()
LANGUAGE plpgsql
AS
$$
    DECLARE 
        SampleNumber INTEGER;
    BEGIN
        SELECT 1 INTO SampleNumber;
        SELECT FOUND;
    END;
$$;

Snowflake

Perform Statement:

PostgreSQL

IN -> PostgreSQL_06.sql

-- Found property used with PERFORM statement.
CREATE OR REPLACE PROCEDURE FoundUsingPerformProcedure()
LANGUAGE plpgsql
AS
$$
    BEGIN
        PERFORM 1;
        RETURN FOUND;
    END;
$$;

Snowflake

Fetch Statement:

PostgreSQL

IN -> PostgreSQL_07.sql

-- Found property used with FETCH statement.
CREATE OR REPLACE PROCEDURE FoundUsingFetchProcedure ()
LANGUAGE plpgsql
AS
$$
    DECLARE
        SampleRow VARCHAR;
        SampleCursor CURSOR FOR SELECT EmptyColumn FROM EmptyTable;
    BEGIN
        OPEN SampleCursor;
        FETCH SampleCursor;
        CLOSE SampleCursor;
        SELECT FOUND;
    END;
$$;

Snowflake

Recommendations

• If you need more support, you can email us at [email protected]