WITH ... VALUES

Came across something interesting while looking at Marko Tiikkaja's
cut-down WITH patch. I see that our grammar allows a WITH clause in
front of VALUES, and analyze.c makes some effort to process it, but
AFAICT there isn't any actual use case for this because you can't
reference the WITH clause from the body of VALUES:

regression=# with q as (select * from int8_tbl) values (42);
column1
---------
42
(1 row)

regression=# with q as (select * from int8_tbl) values (q1);
ERROR: column "q1" does not exist
LINE 1: with q as (select * from int8_tbl) values (q1);
^
regression=# with q as (select * from int8_tbl) values (q.q1);
ERROR: missing FROM-clause entry for table "q"
LINE 1: with q as (select * from int8_tbl) values (q.q1);
^

Even if you go back to 8.4 and turn on add_missing_from, you get:

regression=# with q as (select * from int8_tbl) values (q.q1);
NOTICE: adding missing FROM-clause entry for table "q"
LINE 1: with q as (select * from int8_tbl) values (q.q1);
^
ERROR: VALUES must not contain table references
LINE 1: with q as (select * from int8_tbl) values (q.q1);
^

So on the whole this seems like useless code. Perhaps we should instead
throw an error along the line of "WITH cannot be attached to VALUES"?

regards, tom lane

On Fri, 2010-02-12 at 16:59 -0500, Tom Lane wrote:
> Came across something interesting while looking at Marko Tiikkaja's
> cut-down WITH patch. I see that our grammar allows a WITH clause in
> front of VALUES, and analyze.c makes some effort to process it, but
> AFAICT there isn't any actual use case for this because you can't
> reference the WITH clause from the body of VALUES:

create table tmp(a int);
insert into tmp values(2);
with tmp2 as (select a + 1 as b from tmp)
values((select b from tmp2));

column1
---------
3
(1 row)

Regards,
Jeff Davis

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> On Fri, 2010-02-12 at 16:59 -0500, Tom Lane wrote:
>> AFAICT there isn't any actual use case for this because you can't
>> reference the WITH clause from the body of VALUES:

> with tmp2 as (select a + 1 as b from tmp)
> values((select b from tmp2));

Ah, sneaky. Never mind that then.

regards, tom lane