Re: Surprising (?) Sequence Behavior

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

"Richard M. Kues" <software(at)riva(dot)at> writes:
> CREATE TEMPORARY SEQUENCE s;

> SELECT
>     nextval('s'), t.name
> FROM
> (
>     SELECT
>        tablename AS name
>     FROM
>        pg_tables
>     ORDER BY
>        tablename
> ) AS t
> WHERE
>     t.name = 'pg_am'
> ;

> The result is:
>     1 pg_am

> instead of:
>     2 pg_am

> At least for me this is surprising!

Why do you find it surprising?  Per spec, the SELECT output list is not
evaluated at rows that fail the WHERE clause.  This must be so; consider
examples like
	SELECT 1/x FROM t WHERE x <> 0;

I think what you need is three levels of nested SELECT, with the
nextval() done in the middle level, and probably an "OFFSET 0" in the
middle one to keep Postgres from collapsing the top and middle together.

			regards, tom lane