Re: Assignment of valid collation for SET operations on queries with UNKNOWN types.

Hello,

>And ideally fix things so
>that the type of the view column will be resolved as text, so that you
>don't hit this condition in the first place; but there is no good that
>comes out of allowing a view to be created like this

Thank you for suggestion. Attached is a patch which resolves the columns
with literal constants as TEXT for view creation.

Following views can be created with literal columns resolved as TEXT.

postgres=# create view v as select 'abc' a;
CREATE VIEW
postgres=# create view v1 as select 'def' a;
CREATE VIEW
postgres=# \d+ v1;
View "public.v1"
Column | Type | Collation | Nullable | Default | Storage | Description
--------+------+-----------+----------+---------+----------+-------------
a | text | | | | extended |
View definition:
SELECT 'def'::text AS a;

This allows following queries to run successfully which wasn't the case
earlier.

postgres=# select a from v UNION select a from v1;
a
-----
abc
def
(2 rows)

AND

postgres=# select * from v order by 1;
a
-----
abc
(1 row)

Kindly give your opinion.

Thank you,
Rahila Syed.

On Thu, Nov 17, 2016 at 8:59 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Rahila Syed <rahilasyed90(at)gmail(dot)com> writes:
> > CASE 2:
> > postgres=# create view v as select 'abc' a;
> > 2016-11-16 15:28:48 IST WARNING: column "a" has type "unknown"
> > 2016-11-16 15:28:48 IST DETAIL: Proceeding with relation creation
> anyway.
> > WARNING: column "a" has type "unknown"
> > DETAIL: Proceeding with relation creation anyway.
> > CREATE VIEW
>
> We really ought to make that a hard error. And ideally fix things so
> that the type of the view column will be resolved as text, so that you
> don't hit this condition in the first place; but there is no good that
> comes out of allowing a view to be created like this.
>
> > Attached WIP patch does that. Kindly let me know your opinion.
>
> This is a seriously ugly kluge that's attacking the symptom not the
> problem. Or really, a symptom not the problem. There are lots of
> other symptoms, for instance
>
> regression=# select * from v order by 1;
> ERROR: failed to find conversion function from unknown to text
>
> regards, tom lane
>