View Enhancement

Symptom

Following example illustrates existing behavior on releases prior to 7.2.1.6-P3. We are referring to same example through this document to showcase new feature and compare with existing behavior.

Suppose the user executes following CREATE VIEW SQL statement:

create table table_1(col1 int, col2 int, col3 varchar(20));

/* Comment 1: This is preceding comment*/

create or replace view view_test    -– Comment2: Create view view_test

as

    select                          -- List of projections

        col1                   column1,

        nvl(col1, 0)           column2,

        col2                   column3,

        col2 * 20 + 1 - 2 / 3  column4,

        col3                   column5,

        col3 || ' A string'    column6

    from                            /* Comment 3: Only one base table */

        table_1;

In following sections, we will be referring to

Comment 1: as “preceding comment”

Comment 2: as “single line inline comment”

Comment 3: as “multiline inline comment”

On successful execution of the query, new view is created in Netezza. But stored view definition SQL is not same as the one provided by user.

DB_15597.ADMIN(ADMIN)=> \d view_test

                       View "VIEW_TEST"

 Attribute |         Type          | Modifier | Default Value

-----------+-----------------------+----------+---------------

 COLUMN1   | INTEGER               |          |

 COLUMN2   | INTEGER               |          |

 COLUMN3   | INTEGER               |          |

 COLUMN4   | CHARACTER VARYING(20) |          |

 COLUMN5   | CHARACTER VARYING(29) |          |

View definition: SELECT TABLE_1.COL1 AS COLUMN1, CASE WHEN (TABLE_1.COL1 NOTNULL) THEN TABLE_1.COL1 WHEN (0 NOTNULL) THEN 0 ELSE NULL::INT4 END AS COLUMN2, (((TABLE_1.COL2 * 20) + 1) - (2 / 3)) AS COLUMN3, TABLE_1.COL3 AS COLUMN4, (TABLE_1.COL3 || ' A string'::"VARCHAR") AS COLUMN5 FROM ADMIN.TABLE_1;

As shown in above example, Netezza does not retain user provided SQL.

Customer requirement is to store and retrieve original user provided SQL.