Re: Better error message for a small problem with WITH RECURSIVE

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> Yes, please. At least DB2 allows recursive queries without the
> "RECURSIVE" keyword, just "WITH" is enough. Without a hint, anyone
> migrating from such a system will spend hours looking at the query,
> seeing nothing wrong.

Huh, interesting ... so they're violating the letter of the spec
as to WITH name scope.

Anyway, here's what we do as of last night:

regression=# with q(x) as (select 1 union all select x+1 from q where x<10)
select * from q;
ERROR: relation "q" does not exist
LINE 1: with q(x) as (select 1 union all select x+1 from q where x<1...
^
DETAIL: There is a WITH item named "q", but it cannot be referenced from this part of the query.
HINT: Use WITH RECURSIVE, or re-order the WITH items to remove forward references.

regards, tom lane