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

    Join Date
    Jun 2013
    Location
    Swindon, UK
    Posts
    8
    Rep Power
    0

    Loop Shema Script


    Hello All,

    (short background) My company are currently building a small application alongside our main work, we will be using a Postgresql DB.

    What I am actually trying to do is a SQL script that loop creates a whole bunch of schemas and names them accordingly.

    Something along the lines of:

    Create Schema ljschema1
    loop until
    name = +1

    If you catch my drift

    so the next one would be called ljschema2 etc. etc.

    Thanks in advance, and don't hesitate to tell me if what I am asking is stupid.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Something like this:
    Code:
    do
    $body$
    declare
      counter integer;
      stmt    text;
    begin
      counter := 1;
      loop
        stmt := 'create schema ljschema' || counter::text;
        execute stmt;
        counter := counter + 1;
        exit when counter > 5;
      end loop;
    end
    $body$;
    
    commit;
    For more details please see the manual:

    http://www.postgresql.org/docs/curre...ic/sql-do.html
    http://www.postgresql.org/docs/curre...c/plpgsql.html

    Comments on this post

    • LukeJohnson agrees
    Last edited by shammat; June 20th, 2013 at 07:48 AM.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Location
    Swindon, UK
    Posts
    8
    Rep Power
    0
    Thankyou very much

    Great help

IMN logo majestic logo threadwatch logo seochat tools logo