Re: Surprising (?) Sequence Behavior

["Richard M. Kues" <software(at)riva(dot)at>]

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