Home > mailing lists

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

From	Ken Tanzer
Subject	Cosmetically-varying casts added to view definitions
Date	July 31, 2018 03:26:53
Msg-id	CAD3a31Vrc1cHZC588JNuYq_oHN2COD1=G5=mecVawbLfb_Vbbg@mail.gmail.com Whole thread Raw
Responses	Re: Cosmetically-varying casts added to view definitions (Adrian Klaver <adrian.klaver@aklaver.com>)
List	pgsql-general

Tree view

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.

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.

Thanks!

Ken