SnowConvert: PostgreSQL の機能差¶

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

 SELECT SampleColumn FROM SampleTable;

 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()))
$$;

 -- Found property used with INSERT statement.
CREATE OR REPLACE FUNCTION FoundUsingInsertFunction()
RETURNS BOOLEAN
AS
$$
  BEGIN
    INSERT INTO SampleTable (SampleColumn1)
    VALUES ('SampleValue0.1');
    RETURN FOUND;
  END;
$$
LANGUAGE plpgsql;

 -- Found property used with INSERT statement.
!!!RESOLVE EWI!!! /*** SSC-EWI-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FoundUsingInsertFunction ()
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
  BEGIN
    INSERT INTO SampleTable (SampleColumn1)
    VALUES ('SampleValue0.1');
    RETURN SQLFOUND /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
  END;
$$;

 -- Found property used with UPDATE statement.
CREATE OR REPLACE FUNCTION FoundUsingUpdateFunction()
RETURNS BOOLEAN
AS
$$
    BEGIN
        UPDATE SampleTable 
        SET SampleColumn1 = 'SampleValue0.1' 
        WHERE SampleColumn1 = 'SampleValue0.1';
        RETURN FOUND;
    END;
$$
LANGUAGE plpgsql;

 -- Found property used with UPDATE statement.
!!!RESOLVE EWI!!! /*** SSC-EWI-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FoundUsingUpdateFunction ()
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
    BEGIN
        UPDATE SampleTable
        SET SampleColumn1 = 'SampleValue0.1'
        WHERE SampleColumn1 = 'SampleValue0.1';
        RETURN SQLFOUND /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
    END;
$$;

 -- Found property used with DELETE statement.
CREATE OR REPLACE FUNCTION FoundUsingDeleteFunction()
RETURNS BOOLEAN
AS
$$
    BEGIN
        DELETE FROM SampleTable 
        WHERE SampleColumn1 = 'SampleValue0.1';
        RETURN FOUND;
    END;
$$
LANGUAGE plpgsql;

 -- Found property used with DELETE statement.
!!!RESOLVE EWI!!! /*** SSC-EWI-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FoundUsingDeleteFunction ()
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
    BEGIN
        DELETE FROM
            SampleTable
        WHERE SampleColumn1 = 'SampleValue0.1';
        RETURN SQLFOUND /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
    END;
$$;

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

 -- Found property used with MERGE statement.
!!!RESOLVE EWI!!! /*** SSC-EWI-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FoundUsingMergeFunction ()
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
    BEGIN
        MERGE INTO SampleTableB B
        USING (SELECT * FROM SampleTableA) A
        ON B.SampleColumn1 = A.SampleColumn2
        WHEN MATCHED THEN DELETE !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'MergeStatement' NODE ***/!!!;
        RETURN SQLFOUND /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
    END;
$$;

 -- Found property used with SELECT INTO statement.
CREATE OR REPLACE FUNCTION FoundUsingSelectIntoFunction()
RETURNS BOOLEAN
AS
$$
    DECLARE 
        SampleNumber INTEGER;
    BEGIN
        SELECT 1 INTO SampleNumber;
        RETURN FOUND;
    END;
$$
LANGUAGE plpgsql;

 -- Found property used with SELECT INTO statement.
!!!RESOLVE EWI!!! /*** SSC-EWI-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FoundUsingSelectIntoFunction ()
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
    DECLARE
        SampleNumber INTEGER;
    BEGIN
        SELECT 1 INTO
            : SampleNumber;
        RETURN FOUND_UDF() /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
    END;
$$;

 -- Found property used with PERFORM statement.
CREATE OR REPLACE FUNCTION FoundUsingPerformFunction()
RETURNS BOOLEAN
AS
$$
    BEGIN
        PERFORM 1;
        RETURN FOUND;
    END;
$$
LANGUAGE plpgsql;

 -- Found property used with PERFORM statement.
!!!RESOLVE EWI!!! /*** SSC-EWI-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FoundUsingPerformFunction ()
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
    BEGIN
        SELECT
            1;
        RETURN FOUND_UDF() /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
    END;
$$;

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

 -- Found property used with FETCH statement.
!!!RESOLVE EWI!!! /*** SSC-EWI-GP0003 - RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FoundUsingFetchFunction ()
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
   DECLARE
       SampleRow VARCHAR;
       SampleCursor CURSOR FOR SELECT EmptyColumn FROM
           EmptyTable;
   BEGIN
       OPEN SampleCursor;
       !!!RESOLVE EWI!!! /*** SSC-EWI-PG0015 - FETCH CURSOR WITHOUT TARGET VARIABLES IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
       FETCH SampleCursor;
       CLOSE SampleCursor;
       RETURN FOUND_UDF() /*** SSC-FDM-PG0001 - FOUND COULD HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE IN SOME SCENARIOS. ***/;
   END;
$$;

 CREATE TABLE table1 (
    col1 BPCHAR, 
    col2 BPCHAR(20)
);

SELECT 'Y'::BPCHAR;

SELECT 'Y   '::BPCHAR(20);
SELECT COL1::BPCHAR(20) FROM tbl;

 CREATE TABLE table1 (
    col1 VARCHAR /*** SSC-FDM-PG0002 - BPCHAR CONVERTED TO VARCHAR. THESE TYPES MAY HAVE SOME FUNCTIONAL DIFFERENCES. ***/,
    col2 VARCHAR(20) /*** SSC-FDM-PG0002 - BPCHAR CONVERTED TO VARCHAR. THESE TYPES MAY HAVE SOME FUNCTIONAL DIFFERENCES. ***/
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "postgresql",  "convertedOn": "09/17/2024" }}';

 SELECT 'Y':: VARCHAR /*** SSC-FDM-PG0002 - BPCHAR CONVERTED TO VARCHAR. THESE TYPES MAY HAVE SOME FUNCTIONAL DIFFERENCES. ***/;

SELECT
    RTRIM( 'Y   ') :: VARCHAR(20) /*** SSC-FDM-PG0002 - BPCHAR CONVERTED TO VARCHAR. THESE TYPES MAY HAVE SOME FUNCTIONAL DIFFERENCES. ***/;

SELECT
    RTRIM( COL1) :: VARCHAR(20) /*** SSC-FDM-PG0002 - BPCHAR CONVERTED TO VARCHAR. THESE TYPES MAY HAVE SOME FUNCTIONAL DIFFERENCES. ***/
FROM
    tbl;

CREATE TABLE tbl(
    col BYTEA
);

 CREATE TABLE tbl (
    col BINARY /*** SSC-FDM-PG0003 - BYTEA CONVERTED TO BINARY. SIZE LIMIT REDUCED FROM 1GB TO 8MB ***/
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "postgresql",  "convertedOn": "09/17/2024" }}';

 CREATE TABLE table1 (
    dt_update timestamp without time zone DEFAULT clock_timestamp()
);

 CREATE TABLE table1 (
    dt_update TIMESTAMP_NTZ DEFAULT CAST(
    --** SSC-FDM-PG0004 - THE DATE OUTPUT FORMAT MAY VARY DEPENDING ON THE TIMESTAMP TYPE AND THE TIMESTAMP_OUTPUT_FORMAT BEING USED. **
    CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "postgresql",  "convertedOn": "09/17/2024" }}';

 CREATE TABLE sample2 (
    platform_id integer NOT NULL,
    dt_update TIMESTAMP_TZ DEFAULT CAST(
--** SSC-FDM-PG0004 - THE DATE OUTPUT FORMAT MAY VARY DEPENDING ON THE TIMESTAMP TYPE AND THE TIMESTAMP_OUTPUT_FORMAT BEING USED. **
CURRENT_TIMESTAMP() AS TIMESTAMP_TZ)
);

insert into postgres.public.sample2 (platform_id) values (1);
ALTER SESSION SET timestamp_output_format = 'YYYY-MM-DD HH24:MI:SS.FF';

select *,
CURRENT_TIMESTAMP(3)
from
postgres.public.sample2;

 PLATFORM_ID   DT_UPDATE	                        CURRENT_TIMESTAMP(3)
1	      2023-02-05 20:52:30.082000000	2023-02-05 21:20:31.593

 select clock_timestamp();

 clock_timestamp        |
-----------------------+
2023-02-05 23:24:13.740|

 ALTER SESSION SET timestamp_output_format = 'YYYY-MM-DD HH24:MI:SS.FF';
select
    CURRENT_TIMESTAMP(3);

 CURRENT_TIMESTAMP(3)
2023-02-05 21:29:24.258

 SET SEARCH_PATH TO schema1, schema2, schema3;

 --** SSC-FDM-PG0006 - SET SEARCH PATH WITH MULTIPLE SCHEMAS IS NOT SUPPORTED IN SNOWFLAKE **
USE SCHEMA schema1 /*, schema2, schema3*/;

 COMMENT ON TABLE mytable IS NULL;

 COMMENT ON TABLE mytable IS '' /*** SSC-FDM-PG0007 - NULL IS CONVERTED TO '' AND MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/;

 select column1
      into UNLOGGED NewTable
      from oldTable;

 CREATE TABLE IF NOT EXISTS NewTable AS
      select column1
--      --** SSC-FDM-PG0008 - SELECT INTO UNLOGGED TABLES ARE NOT SUPPORTED BY SNOWFLAKE. **
--            into UNLOGGED NewTable
            from
            oldTable;

 SELECT nextval('seq1');

 SELECT seq1.nextval /*** SSC-FDM-PG0009 - THE SEQUENCE NEXTVAL PROPERTY SNOWFLAKE DOES NOT GUARANTEE GENERATING SEQUENCE NUMBERS WITHOUT GAPS. ***/;

 SELECT 1 << 127 AS resultShiftedLeft, 16 >> 32 AS resultShiftedRight;

 SELECT
--** SSC-FDM-PG0010 - RESULTS MAY VARY DUE TO THE BEHAVIOR OF SNOWFLAKE'S BITSHIFTLEFT BITWISE FUNCTION **
BITSHIFTLEFT( 1, 127) AS resultShiftedLeft,
--** SSC-FDM-PG0010 - RESULTS MAY VARY DUE TO THE BEHAVIOR OF SNOWFLAKE'S BITSHIFTRIGHT BITWISE FUNCTION **
BITSHIFTRIGHT( 16, 32) AS resultShiftedRight;

 CREATE TABLE collateTable (
col1 VARCHAR(20) COLLATE CASE_INSENSITIVE,
col2 VARCHAR(30) COLLATE CASE_SENSITIVE);

INSERT INTO collateTable values ('HELLO WORLD!', 'HELLO WORLD!');

SELECT
col1 SIMILAR TO 'Hello%' as ci,
col2 SIMILAR TO 'Hello%' as cs
FROM collateTable;

 CREATE TABLE collateTable (
col1 VARCHAR(20) COLLATE 'en-ci',
col2 VARCHAR(30) COLLATE 'en-cs'
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "01/16/2025",  "domain": "test" }}';

INSERT INTO collateTable
values ('HELLO WORLD!', 'HELLO WORLD!');

SELECT
RLIKE(COLLATE(
--** SSC-FDM-PG0011 - THE USE OF THE COLLATE COLUMN CONSTRAINT HAS BEEN DISABLED FOR THIS PATTERN-MATCHING CONDITION. **
col1, ''), 'Hello.*', 's') as ci,
RLIKE(COLLATE(
--** SSC-FDM-PG0011 - THE USE OF THE COLLATE COLUMN CONSTRAINT HAS BEEN DISABLED FOR THIS PATTERN-MATCHING CONDITION. **
col2, ''), 'Hello.*', 's') as cs
FROM
collateTable;

 CREATE OR REPLACE PROCEDURE variable_Not_Null()
LANGUAGE plpgsql
AS $$
DECLARE
    v_notnull VARCHAR NOT NULL DEFAULT 'Test default';
BEGIN
    v_notnull := NULL;
    -- Procedure logic
END;
$$;

 CREATE OR REPLACE PROCEDURE variable_Not_Null ()
RETURNS VARCHAR
LANGUAGE SQL
AS $$
DECLARE
    --** SSC-FDM-PG0012 - NOT NULL CONSTRAINT HAS BEEN REMOVED. ASSIGNING NULL TO THIS VARIABLE WILL NO LONGER CAUSE A FAILURE. **
    v_notnull VARCHAR DEFAULT 'Test default';
BEGIN
    v_notnull := NULL;
    -- Procedure logic
END;
$$;