SnowConvert: Postgres Issues¶

ALTER TABLE Table1
ADD CONSTRAINT const3 UNIQUE (zip);

!!!RESOLVE EWI!!! /*** SSC-EWI-PG0003 - TABLE INHERITANCE IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
ALTER TABLE Table1
ADD CONSTRAINT const3 UNIQUE (zip);

CREATE FUNCTION FunctionName1() RETURNS void
AS
$$
DECLARE
	v_var1        	character varying(50);
	v_var2        	character varying(50);
BEGIN
	v_var1 := 'exception1';
	v_var2 := 'exception2';
	RAISE EXCEPTION 'Exception %',v_var1;
    EXCEPTION
      WHEN OTHERS THEN
         RAISE EXCEPTION '%',v_var2;
         RETURN;
END;
$$
    LANGUAGE plpgsql NO SQL;

!!!RESOLVE EWI!!! /*** SSC-EWI-GP0002 - NON-RETURNING FUNCTION TRANSLATED TO STORED PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FunctionName1 () RETURNS STRING
    LANGUAGE SQL
AS
$$
DECLARE
	v_var1        	character varying(50);
	v_var2        	character varying(50);
BEGIN
	v_var1 := 'exception1';
	v_var2 := 'exception2';
	LET SC_RAISE_MESSAGE VARCHAR;
	SC_RAISE_MESSAGE := STRING_FORMAT_UDF('Exception %', array_construct(:v_var1));
	!!!RESOLVE EWI!!! /*** SSC-EWI-PG0013 - RAISE EXCEPTION TRANSLATED TO USER-DEFINED STORED PROCEDURE CALL. ***/!!!
	CALL RAISE_MESSAGE_UDF('EXCEPTION', :SC_RAISE_MESSAGE);
	RETURN 'SUCCESS';
    EXCEPTION
      WHEN OTHER THEN
		SC_RAISE_MESSAGE := STRING_FORMAT_UDF('%', array_construct(:v_var2));
		!!!RESOLVE EWI!!! /*** SSC-EWI-PG0013 - RAISE EXCEPTION TRANSLATED TO USER-DEFINED STORED PROCEDURE CALL. ***/!!!
		CALL RAISE_MESSAGE_UDF('EXCEPTION', :SC_RAISE_MESSAGE);
		RAISE;
         RETURN;
END;
$$;

CREATE OR REPLACE FUNCTION STRING_FORMAT_UDF(PATTERN VARCHAR, ARGS VARIANT)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "udf",  "convertedOn": "09/09/2024" }}'
AS
$$
	var placeholder_str = "{%}";
	var result = PATTERN.replace(/(?<!%)%(?!%)/g, placeholder_str).replace("%%","%");
	for (var i = 0; i < ARGS.length; i++)
	{
		result = result.replace(placeholder_str, ARGS[i]);
	}
	return result;
$$;

CREATE OR REPLACE PROCEDURE RAISE_MESSAGE_UDF(LEVEL VARCHAR, MESSAGE VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "udf",  "convertedOn": "09/09/2024" }}'
AS
$$
  BEGIN
    RETURN 'Logged message: ' || LEVEL || ' - ' || MESSAGE;
  END;
$$;

CREATE OR REPLACE PROCEDURE procedure1(out result VARCHAR(100))
LANGUAGE plpgsql
AS $$
BEGIN
result := '<';
<<outer_loop>>
for i in 1..3 loop
  <<inner_loop>>
  for i in 4..6 loop
  result := result || '(' || outer_loop.i || ', ' || i || ')';
  end loop inner_loop;
end loop outer_loop;
result := result || '>';
END;
$$;

CREATE OR REPLACE PROCEDURE procedure1 (result VARCHAR(100))
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/16/2025",  "domain": "test" }}'
AS $$
BEGIN
result := '<';
for i in 1 TO 3 loop
  for i in 4 TO 6 loop
  result := result || '(' ||
                             !!!RESOLVE EWI!!! /*** SSC-EWI-PG0006 - REFERENCE TO A VARIABLE USING THE LABEL IS NOT SUPPORTED BY SNOWFLAKE. ***/!!! outer_loop.i || ', ' || i || ')';
  end loop inner_loop;
end loop outer_loop;
result := result || '>';
  RETURN OBJECT_CONSTRUCT('result', :result);
END;
$$;

CREATE TABLE Table1 (
    code        char(5),
    date_prod   date,
    CONSTRAINT production UNIQUE(date_prod) INCLUDE(code)
);

CREATE TABLE Table2 (
    name    varchar(40),
    UNIQUE(name) WITH (fillfactor=70)
);

CREATE TABLE Table3 (
    name    varchar(40),
    PRIMARY KEY(name) USING INDEX TABLESPACE tablespace_name
);

CREATE TABLE Table1 (
    code        char(5),
    date_prod   date,
    CONSTRAINT production UNIQUE(date_prod)
                                            !!!RESOLVE EWI!!! /*** SSC-EWI-PG0002 - INCLUDE PARAMETER NOT APPLICABLE. CONSTRAINT INDEX PARAMETERS ARE NOT SUPPORTED IN SNOWFLAKE. ***/!!! INCLUDE(code)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "postgresql",  "convertedOn": "09/17/2024" }}';

CREATE TABLE Table2 (
    name    varchar(40),
    UNIQUE(name)
                 !!!RESOLVE EWI!!! /*** SSC-EWI-PG0002 - WITH PARAMETER NOT APPLICABLE. CONSTRAINT INDEX PARAMETERS ARE NOT SUPPORTED IN SNOWFLAKE. ***/!!! WITH (fillfactor=70)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "postgresql",  "convertedOn": "09/17/2024" }}';

CREATE TABLE Table3 (
    name    varchar(40),
    PRIMARY KEY(name)
                      !!!RESOLVE EWI!!! /*** SSC-EWI-PG0002 - USING PARAMETER NOT APPLICABLE. CONSTRAINT INDEX PARAMETERS ARE NOT SUPPORTED IN SNOWFLAKE. ***/!!! USING INDEX TABLESPACE tablespace_name
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "postgresql",  "convertedOn": "09/17/2024" }}';

ALTER TABLE Table1 *
ADD CONSTRAINT const UNIQUE (zip) NOT VALID;

ALTER TABLE Table1
ADD CONSTRAINT const UNIQUE (zip)
                                  !!!RESOLVE EWI!!! /*** SSC-EWI-PG0012 - NOT VALID CONSTRAINT OPTION IS NOT SUPPORTED BY SNOWFLAKE. ***/!!! NOT VALID;

COMMENT ON RULE rule_name on TABLE_NAME IS 'this is a comment';

!!!RESOLVE EWI!!! /*** SSC-EWI-PG0009 - COMMENT ON 'RULE' IS NOT SUPPORTED BY SNOWFLAKE. ***/!!!
COMMENT ON RULE rule_name on TABLE_NAME IS 'this is a comment';

SELECT to_char(interval '15h 2m 12s', 'HH24:MI:SS');

SELECT to_char(INTERVAL '15h, 2m, 12s', 'HH24:MI:SS') !!!RESOLVE EWI!!! /*** SSC-EWI-PG0008 - THE USE OF INTERVAL WITHIN TO_CHAR IS NOT SUPPORTED BY SNOWFLAKE. ***/!!!;

SELECT
   DATE_TRUNC('decade', TIMESTAMP '2017-03-17 02:09:30'),
   DATE_TRUNC('century', TIMESTAMP '2017-03-17 02:09:30'),
   DATE_TRUNC('millennium', TIMESTAMP '2017-03-17 02:09:30');

SELECT
      !!!RESOLVE EWI!!! /*** SSC-EWI-PG0005 - DECADE FORMAT IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
      DATE_TRUNC('decade', TIMESTAMP '2017-03-17 02:09:30'),
      !!!RESOLVE EWI!!! /*** SSC-EWI-PG0005 - CENTURY FORMAT IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
      DATE_TRUNC('century', TIMESTAMP '2017-03-17 02:09:30'),
      !!!RESOLVE EWI!!! /*** SSC-EWI-PG0005 - MILLENNIUM FORMAT IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
      DATE_TRUNC('millennium', TIMESTAMP '2017-03-17 02:09:30');

FETCH PRIOR FROM cursor1;

!!!RESOLVE EWI!!! /*** SSC-EWI-PG0015 - FETCH CURSOR WITHOUT TARGET VARIABLES IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
FETCH PRIOR FROM cursor1;

SELECT
   age(date1::date, date2::date)
FROM
   Table1;

SELECT
      !!!RESOLVE EWI!!! /*** SSC-EWI-PG0001 - AGE IS NOT SUPPORTED ON SNOWFLAKE. ***/!!!
      age(date1::date, date2::date)
FROM
      Table1;

CREATE UNLOGGED SEQUENCE sequence_name;

--** SSC-FDM-PG0009 - THE SEQUENCE NEXTVAL PROPERTY SNOWFLAKE DOES NOT GUARANTEE GENERATING SEQUENCE NUMBERS WITHOUT GAPS. **
CREATE UNLOGGED !!!RESOLVE EWI!!! /*** SSC-EWI-PG0011 - 'UNLOGGED' IS NOT SUPPORTED BY SNOWFLAKE. ***/!!! SEQUENCE sequence_name;

CREATE TEMPORARY SEQUENCE sequence1;
CREATE TEMP SEQUENCE sequence2;

--** SSC-FDM-PG0009 - THE SEQUENCE NEXTVAL PROPERTY SNOWFLAKE DOES NOT GUARANTEE GENERATING SEQUENCE NUMBERS WITHOUT GAPS. **
CREATE TEMPORARY !!!RESOLVE EWI!!! /*** SSC-EWI-PG0010 - CREATE TEMPORARY SEQUENCE IS NOT SUPPORTED BY SNOWFLAKE. ***/!!! SEQUENCE sequence1;


--** SSC-FDM-PG0009 - THE SEQUENCE NEXTVAL PROPERTY SNOWFLAKE DOES NOT GUARANTEE GENERATING SEQUENCE NUMBERS WITHOUT GAPS. **
 CREATE TEMP !!!RESOLVE EWI!!! /*** SSC-EWI-PG0010 - CREATE TEMPORARY SEQUENCE IS NOT SUPPORTED BY SNOWFLAKE. ***/!!! SEQUENCE sequence2;

CREATE TABLE Table1 (
    id      int,
    EXCLUDE USING gist (id WITH &&)
);

CREATE TABLE Table1 (
    id      int,
    !!!RESOLVE EWI!!! /*** SSC-EWI-PG0004 - EXCLUDE CONSTRAINT IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
    EXCLUDE USING gist (id WITH &&)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "postgresql",  "convertedOn": "09/17/2024" }}';

CREATE OR REPLACE PROCEDURE cursor_test()
AS $$
BEGIN
   FETCH FORWARD FROM cursor1 INTO my_var;
   FETCH FIRST FROM cursor1 INTO my_var;
   FETCH LAST FROM cursor1 INTO my_var;
END;
$$;

CREATE OR REPLACE PROCEDURE cursor_test ()
RETURNS VARCHAR
AS $$
BEGIN
   FETCH
   	cursor1 INTO my_var;
   !!!RESOLVE EWI!!! /*** SSC-EWI-PG0014 - SNOWFLAKE SCRIPTING CURSORS DO NOT SUPPORT FETCH ORIENTATION. ***/!!!
   FETCH FIRST FROM cursor1 INTO my_var;
   !!!RESOLVE EWI!!! /*** SSC-EWI-PG0014 - SNOWFLAKE SCRIPTING CURSORS DO NOT SUPPORT FETCH ORIENTATION. ***/!!!
   FETCH LAST FROM cursor1 INTO my_var;
END;
$$;