Re: Surprising (?) Sequence Behavior - Mailing list pgsql-general

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