On Fri, Jun 3, 2011 at 11:25 AM, Tom Lane <tgl@sss.pgh.pa.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
