Re: Cosmetically-varying casts added to view definitions

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'::character varying::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(dot)tanzer(at)agency-software(dot)org <mailto:ken(dot)tanzer(at)agency-software(dot)org>
> (253) 245-3801
>
> Subscribe to the mailing list
> <mailto:agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com