Re: Surprising (?) Sequence Behavior

Tom Lane schrieb:

>
>> 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;

Of ocurse!

>
> 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.
>

For the archves.
This works:

DROP SEQUENCE IF EXISTS s;
CREATE TEMPORARY SEQUENCE s;

SELECT * FROM
(
SELECT
nextval('s') AS num,
tablename AS name
FROM
(
SELECT
tablename
FROM
pg_tables
ORDER BY tablename
) AS t
OFFSET 0
) AS ranked
WHERE
ranked.name = 'pg_am'

Thanks a lot!
richard