Hello,
A colleague and I were playing around with dumping views and found an inconsistency for a view definition involving subqueries and undecorated constants in a UNION. When we took that view definition and restored it, dumping the view gave different syntax again. Although the slightly different view definitions were semantically the same, it was weird to see the syntax difference.
Our view SQL where 'bar' constant is not decorated:
CREATE VIEW fooview AS SELECT t.a FROM (SELECT 1 AS a, 'foo'::text AS baz UNION SELECT 0 AS a, 'bar')t;
// view definition from pg_get_viewdef()
SELECT t.a
FROM ( SELECT 1 AS a,
'foo'::text AS baz
UNION
SELECT 0 AS a,
'bar'::text) t;
Note that the type decorator is appended to 'bar' in the normal fashion.
Then taking the above view definition, and creating a view,
CREATE VIEW fooview AS SELECT t.a FROM (SELECT 1 AS a, 'foo'::text AS baz UNION SELECT 0 AS a, 'bar'::text)t;
// view definition from pg_get_viewdef()
SELECT t.a
FROM ( SELECT 1 AS a,
'foo'::text AS baz
UNION
SELECT 0 AS a,
'bar'::text AS text) t;
results in a view definition that has the alias 'AS text' appended to 'bar'::text.
Contrast this to creating a view without the subquery:
CREATE OR REPLACE VIEW fooview AS SELECT 1 AS a, 'foo'::text AS baz UNION SELECT 0 AS a, 'bar';
// view definition from pg_get_viewdef()
SELECT 1 AS a,
'foo'::text AS baz
UNION
SELECT 0 AS a,
'bar'::text AS baz;
We see that this view will use the view's tuple descriptor to name the columns.
Looking at the internal code (mostly get_from_clause_item() function), we saw that when a subquery is used, there is no tuple descriptor passed to get_query_def() function. Because of this, get_target_list() uses the resnames obtained from the pg_rewrite entry's ev_action field. However, it seems to be fairly simple to construct a dummy tuple descriptor from the ev_action to pass down the call stack so that the column names will be consistent when deparsing both T_A_Const and T_TypeCast parse tree nodes involving a UNION. Attached is a patch that demonstrates this.
Running with the attached patch, it seems to work pretty well:
postgres=# CREATE VIEW fooview AS SELECT t.a FROM (SELECT 1 AS a, 'foo'::text AS baz UNION SELECT 0 AS a, 'bar')t;
postgres=# select pg_get_viewdef('fooview');
pg_get_viewdef
------------------------------------
SELECT t.a
FROM ( SELECT 1 AS a,
'foo'::text AS baz
UNION
SELECT 0 AS a,
'bar'::text AS baz) t;
(1 row)
postgres=# CREATE VIEW fooview AS SELECT t.a FROM (SELECT 1 AS a, 'foo'::text AS baz UNION SELECT 0 AS a, 'bar'::text)t;
postgres=# select pg_get_viewdef('fooview');
pg_get_viewdef
------------------------------------
SELECT t.a
FROM ( SELECT 1 AS a,
'foo'::text AS baz
UNION
SELECT 0 AS a,
'bar'::text AS baz) t;
(1 row)
Nested subqueries also work with the patch. We're not sure how this could break.
Is this an acceptable change that should be pursued?
Regards,
Jimmy Yih and Jim Doty