Re: Cosmetically-varying casts added to view definitions - Mailing list pgsql-general

On 07/30/2018 02:26 PM, Ken Tanzer wrote:
> Hi.  As background/context, I'm working on a script to take a series of 
> databases and make them timezone-aware.  This basically involves saving 
> all the view definitions, dropping all the views, changing all the 
> timestamp columns without time zones to TS with TZ, and then recreating 
> all the views.  As a sanity check on all of this, I compared the 
> resulting view definitions to what existed before starting.  Most of 
> them were equivalent, with a few exceptions.
> 
> These exceptions all seem to be where slightly different casting is used 
> when the view definition gets created.  For example, starting with this 
> view (payment_form_code is a varchar(20):
> 
> ag_reach=> CREATE VIEW test_tmp AS (SELECT 1 FROM l_payment_form WHERE 
> payment_form_code IN ('CREDIT_CARD','OTHER'));
> 
> CREATE VIEW
> ag_reach=> \d+ test_tmp
>                   View "public.test_tmp"
>    Column  |  Type   | Modifiers | Storage | Description
> ----------+---------+-----------+---------+-------------
>   ?column? | integer |           | plain   |
> View definition:
>   SELECT 1
>     FROM l_payment_form
>    WHERE l_payment_form.payment_form_code::text = ANY (ARRAY['CREDIT_CARD'::character varying, 'OTHER'::character
varying]::text[]);
> 
> I wasn't very surprised by that.  But when I take the view definition as 
> stored in PG above, and create a new view:
> 
> ag_reach=> CREATE VIEW test_tmp2 AS SELECT 1 FROM l_payment_form WHERE 
> l_payment_form.payment_form_code::text = ANY
> 
> (ARRAY['CREDIT_CARD'::character varying, 'OTHER'::character varying]::text[]);
> 
> CREATE VIEW
> 
> ag_reach=> \d+ test_tmp2
>                  View "public.test_tmp2"
>    Column  |  Type   | Modifiers | Storage | Description
> ----------+---------+-----------+---------+-------------
>   ?column? | integer |           | plain   |
> View definition:
>   SELECT 1
>     FROM l_payment_form
>    WHERE l_payment_form.payment_form_code::text = ANY (ARRAY['CREDIT_CARD'::character varying::text,
'OTHER'::charactervarying::text]);
 
> 
>   you'll see it now casts the individual array elements to text, rather 
> than the whole array as in test_tmp.

Which is where it stops from my test:

CREATE  VIEW cp_view AS (SELECT 1 FROM cell_per WHERE season IN 
('annual', 'perennial'));

View "public.cp_view"
   Column  |  Type   | Collation | Nullable | Default | Storage | 
Description
----------+---------+-----------+----------+---------+---------+-------------
  ?column? | integer |           |          |         | plain   |
View definition:
  SELECT 1
    FROM cell_per
   WHERE cell_per.season::text = ANY (ARRAY['annual'::character varying, 
'perennial'::character varying]::text[]);

CREATE  VIEW cp_view AS (SELECT 1 FROM cell_per WHERE 
cell_per.season::text = ANY (ARRAY['annual'::character varying, 
'perennial'::character varying]::text[]));

View "public.cp_view"
   Column  |  Type   | Collation | Nullable | Default | Storage | 
Description
----------+---------+-----------+----------+---------+---------+-------------
  ?column? | integer |           |          |         | plain   |
View definition:
  SELECT 1
    FROM cell_per
   WHERE cell_per.season::text = ANY (ARRAY['annual'::character 
varying::text, 'perennial'::character varying::text]);


CREATE  VIEW cp_view AS (SELECT 1 FROM cell_per WHERE 
cell_per.season::text = ANY (ARRAY['annual'::character varying::text, 
'perennial'::character varying::text]));

View "public.cp_view"
   Column  |  Type   | Collation | Nullable | Default | Storage | 
Description
----------+---------+-----------+----------+---------+---------+-------------
  ?column? | integer |           |          |         | plain   |
View definition:
  SELECT 1
    FROM cell_per
   WHERE cell_per.season::text = ANY (ARRAY['annual'::character 
varying::text, 'perennial'::character varying::text]);

Seems to be simplifying down to an end point.

> 
> This doesn't impair the view's functionality, so I can't necessarily 
> complain.  But it does make it harder for me to know if the views were 
> recreated correctly.  I'd be curious to know what is going on here, and 
> if there's any way to avoid this behavior.

The only way I can think of to maintain a consistent definition is to 
always create the view from the original definition:

CREATE VIEW test_tmp AS (SELECT 1 FROM l_payment_form WHERE 
payment_form_code IN ('CREDIT_CARD','OTHER'));

> 
> Thanks!
> 
> Ken
> 
> -- 
> AGENCY Software
> A Free Software data system
> By and for non-profits
> /http://agency-software.org//
> /https://demo.agency-software.org/client/
> ken.tanzer@agency-software.org <mailto:ken.tanzer@agency-software.org>
> (253) 245-3801
> 
> Subscribe to the mailing list 
> <mailto:agency-general-request@lists.sourceforge.net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.


-- 
Adrian Klaver
adrian.klaver@aklaver.com