Declare

Translation reference to convert DECLARE statement to Snowflake Scripting

Description

DECLARE statement is an optional part of the PL/pgSQL block statement. It allows the creation of variables, constants, procedures declarations and definitions, and functions declarations and definitions. For more information, please refer to PostgreSQL Declarations.

Sample Source Patterns

Empty declaration block

In case the declaration block is empty without variable declarations, it will be removed.

Code

CREATE FUNCTION FOO1(i integer, j varchar) RETURNS INTEGER
    AS $$
DECLARE
BEGIN
	INSERT INTO TABLE1 VALUES (i,j);
	RETURN i + 1;
END;
$$ LANGUAGE plpgsql;

Result

CREATE FUNCTION FOO1(i integer, j varchar) RETURNS INTEGER
    AS $$
BEGIN
	INSERT INTO TABLE1 VALUES (i,j);
	RETURN i + 1;
END;
$$ LANGUAGE plpgsql;

Multiple variable declarations block

If the declare keyword is at the beginning of each variable declaration, it will be removed.

Code

CREATE FUNCTION FOO() RETURNS INTEGER
AS $$
DECLARE
    DECLARE i integer = 20;
    DECLARE j integer default 30;
BEGIN
	INSERT INTO TABLE1 VALUES (i,j);
	RETURN i + 1;
END;
$$ LANGUAGE plpgsql;

------------------------------------------------------------------------------

CREATE FUNCTION FOO() RETURNS INTEGER
AS $$
    DECLARE i integer = 20;
    DECLARE j integer default 30;
BEGIN
	INSERT INTO TABLE1 VALUES (i,j);
	RETURN i + 1;
END;
$$ LANGUAGE plpgsql;

Result

CREATE FUNCTION FOO() RETURNS INTEGER
AS $$
DECLARE
    i integer = 20;
    j integer default 30;
BEGIN
	INSERT INTO TABLE1 VALUES (i,j);
	RETURN i + 1;
END;
$$ LANGUAGE plpgsql;

------------------------------------------------------------------------------

CREATE FUNCTION FOO() RETURNS INTEGER
AS $$
DECLARE
    i integer = 20;
    j integer default 30;
BEGIN
	INSERT INTO TABLE1 VALUES (i,j);
	RETURN i + 1;
END;
$$ LANGUAGE plpgsql;

The following declaration block is fully supported by Snowflake.

Code

CREATE FUNCTION FOO() RETURNS INTEGER
AS $$
DECLARE
    i integer = 20;
    j integer default 30;
BEGIN
	INSERT INTO TABLE1 VALUES (i,j);
	RETURN i + 1;
END;
$$ LANGUAGE plpgsql;

Result

CREATE FUNCTION FOO() RETURNS INTEGER
AS $$
DECLARE
    i integer = 20;
    j integer default 30;
BEGIN
	INSERT INTO TABLE1 VALUES (i,j);
	RETURN i + 1;
END;
$$ LANGUAGE plpgsql;