#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    40
    Rep Power
    12

    postgres sequence


    I have to do select statement but I wish to add another column which print out sequence number for each row.

    Item Data

    1 mydata1
    2 hel
    3 h
    4 h
    5 s
    6 s
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Just create a temporary sequence, and drop it when you are done. (or, you can just leave it, and expect it to be dropped automatically when you finish the current database session).

    Code:
    CREATE TEMPORARY SEQUENCE tempcounter START 1;
    
    SELECT nextval('tempcounter') as counter, mycolumn FROM mytable;
    
    DROP sequence tempcounter;
    Since temporary sequences (like temporary tables) are private to the session user, it doesn't matter if the same piece of code is being run by many different users, because they will each be only accessing their own 'tempcounter' sequence.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com

IMN logo majestic logo threadwatch logo seochat tools logo