Re: About bug #6049

On Fri, Jun 3, 2011 at 11:25 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I looked into $SUBJECT, which complains about this:
>
> CREATE VIEW test_view AS VALUES (1), (2), (3) ORDER BY 1;
>
> This dumps like so:
>
> regression=# \d+ test_view
>                View "public.test_view"
>  Column  |  Type   | Modifiers | Storage | Description
> ---------+---------+-----------+---------+-------------
>  column1 | integer |           | plain   |
> View definition:
>  VALUES (1), (2), (3)
>  ORDER BY "*VALUES*".column1;
>
> which is problematic because it'll fail during dump/restore, because
> you can't write it that way:
>
> regression=# VALUES (1), (2), (3) ORDER BY "*VALUES*".column1;
> ERROR:  invalid reference to FROM-clause entry for table "*VALUES*"
> LINE 1: VALUES (1), (2), (3) ORDER BY "*VALUES*".column1;
>                                      ^
> HINT:  There is an entry for table "*VALUES*", but it cannot be referenced from this part of the query.
>
> The HINT gives a hint what's going on: we make an RTE for the VALUES
> clause, and then we have to give it an alias, for which we use
> "*VALUES*".  But the code is trying to hide the existence of that
> nonstandard alias by not exposing it in the parser's p_relnamespace
> list.  So you can write column1 to refer to the first result column
> of the VALUES, but not "*VALUES*".column1.
>
> On reflection this looks pretty stupid --- column1 is just as
> nonstandard an alias, but we're allowing that to be used explicitly,
> so why not the made-up table alias as well?
>
> But anyway, there are basically two things we could do here: either
> allow the table alias to be referenced, or try to teach ruleutils.c
> not to qualify the column reference.  The second looks pretty tricky
> and maybe not future-proof, so I'm leaning to the first.  Comments?

I think that makes sense, although it would less totally arbitrary if
the alias were just "values" rather than "*VALUES*". The asterisks
suggest that the identifier is fake. But it's probably too late to do
anything about that.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company