Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2009
    Posts
    3

    Question Unanswered: Create a sequence/series/list without temp table?

    As part of a fairly complex multiple outer join query, I need to create a list of integers. I am currently doing so with a temp table and for loop:

    CREATE TEMP TABLE numweek (n INTEGER);
    CREATE OR REPLACE FUNCTION fillNumWeek() RETURNS INTEGER AS '
    BEGIN
    FOR i IN 0..200 LOOP
    INSERT INTO numweek VALUES(i);
    END LOOP;
    RETURN 1;
    END;
    ' LANGUAGE plpgsql;
    SELECT fillNumWeek();

    But I need to do this without the temp table or the function, due to restricted permissions. Is it possible to do something like this?:

    SELECT * FROM {1..200};

    I have searched extensively and haven't been able to find a way to do this.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by dfiel View Post
    But I need to do this without the temp table or the function, due to restricted permissions. Is it possible to do something like this?:

    SELECT * FROM {1..200};

    I have searched extensively and haven't been able to find a way to do this.
    Code:
    SELECT *
    FROM generate_series(1,200);
    PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Set Returning Functions

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dfiel View Post
    As part of a fairly complex multiple outer join query, I need to create a list of integers.
    why don't you use a permanent table? then you won't require a function at all, you can just reference the integers table in the query

    see

    but i gotta admit, that postgresql generate_series function is pretty cute

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2009
    Posts
    3

    Perfect!

    That is exactly what I need. Thanks for your fast help, shammat.

  5. #5
    Join Date
    Dec 2009
    Posts
    3
    A permanent table might be better. But I don't own the DB and am trying to have minimum impact. The generate_series call takes only 31ms, so it will cause no problems from this weekly query.

  6. #6
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Using generate_series is typically as fast or faster than reading from disk.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •