Cosmetically-varying casts added to view definitions

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

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.