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.tanzer@xxxxxxxxxxxxxxxxxxx <mailto:ken.tanzer@xxxxxxxxxxxxxxxxxxx>
(253) 245-3801
Subscribe to the mailing list
<mailto:agency-general-request@xxxxxxxxxxxxxxxxxxxxx?body=subscribe> to
learn more about AGENCY or
follow the discussion.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
