CREATE VIEW

Translation from Greenplum to Snowflake

Description

This command creates a view in a database, which is run every time the view is referenced in a query.

For more information, please refer to CREATE VIEW documentation.

Grammar Syntax

CREATE [OR REPLACE] [TEMP | TEMPORARY] [RECURSIVE] VIEW <name> [ ( <column_name> [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS <query>
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

Code Examples

[OR REPLACE] [TEMP | TEMPORARY] [RECURSIVE]

This syntax is fully supported in Snowflake.

Input Code:

Greenplum

IN -> Greenplum_01.sql

CREATE OR REPLACE VIEW view1 AS
    SELECT
        product_id,
        SUM(quantity) AS sum_quantity
    FROM
        table1
    GROUP BY
        product_id;

CREATE TEMPORARY RECURSIVE VIEW view2 AS
    SELECT
        product_id,
        SUM(quantity) AS sum_quantity
    FROM
        table1
    GROUP BY
        product_id;

Output Code:

Snowflake

OUT -> Greenplum_01.sql

CREATE OR REPLACE VIEW view1
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "greenplum",  "convertedOn": "04/24/2025",  "domain": "test" }}'
AS
    SELECT
        product_id,
        SUM(quantity) AS sum_quantity
    FROM
        table1
GROUP BY
        product_id;


CREATE TEMPORARY RECURSIVE VIEW view2
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "greenplum",  "convertedOn": "04/24/2025",  "domain": "test" }}'
AS
    SELECT
        product_id,
        SUM(quantity) AS sum_quantity
    FROM
        table1
GROUP BY
        product_id;

WITH CHECK CLAUSE

This WITH CHECK CLAUSE clause on a view enforces that any data inserted or updated through the view must satisfy the view's defining conditions. LOCAL checks only the current view's conditions, while CASCADED checks conditions of the view and all underlying views. It prevents creating rows that are invisible through the view and cannot be used with recursive views.

This syntax is not supported in Snowflake.

Input Code:

Greenplum

IN -> Greenplum_02.sql

CREATE VIEW updatable_products AS
    SELECT id, name, price
    FROM products
    WHERE price > 0
WITH LOCAL CHECK OPTION;

Output Code:

Snowflake

OUT -> Greenplum_02.sql

CREATE VIEW updatable_products
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "postgresql",  "convertedOn": "04/30/2025",  "domain": "test" }}'
AS
    SELECT id, name, price
    FROM
    products
    WHERE price > 0;

WITH PARAMETERS OPTIONS

This WITH PARAMETERS OPTIONS allows setting optional properties for the view, such as how modifications through the view are checked (check_option) and whether to enforce row-level security (security_barrier).

This syntax is not supported in Snowflake.

Input Code:

Greenplum

IN -> Greenplum_03.sql

CREATE VIEW large_orders WITH (security_barrier=true, check_option=local) AS
    SELECT order_id, customer_id, total_amount
    FROM orders
    WHERE total_amount > 1000;

Output Code:

Snowflake

OUT -> Greenplum_03.sql

CREATE VIEW large_orders
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "postgresql",  "convertedOn": "04/30/2025",  "domain": "test" }}'
AS
    SELECT order_id, customer_id, total_amount
    FROM
    orders
    WHERE total_amount > 1000;

VALUES OPTION

This syntax is fully supported in Snowflake.

Input Code:

Greenplum

IN -> Greenplum_03.sql

CREATE VIEW numbers_view (number_1) AS
    VALUES (1,2), (2,2), (3,2), (4,2), (5,2);

Output Code:

Snowflake

OUT -> Greenplum_04.sql

CREATE VIEW numbers_view
AS
SELECT
    *
FROM
    (
        VALUES (1,2), (2,2), (3,2), (4,2), (5,2)
    ) AS numbers_view (
        number_1
    );