Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2006
    Posts
    24

    Unanswered: synchronize pgplsql Procedure access

    maybe i'm using the wrong keywords, but i can't find a way to ensure that a stored procedure in pgplsql only runs once per time.

    The basic Idea ist that i will have a table containing numbers, let's say for invoices for example. Everyone creating a new invoice will have to ask for a new invoice-number now.

    Naturally i do not want to have 2 invoices sharing the same number. So there should be some kind of locking on the row containing the next invoice-number and there should be only one instance per Server ( and time) that tries to recieve a new Invoice-Number.

    How can i realize something like that using pgplsql? Any hints on this topic would be great!

    thank you in advance

    Wegus
    an Apple a day keeps Dr. Watson away !

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Why not use a sequence for this task?

    The PostgrSQL Sequence object delivers a separate, distinct number, each time it is called. It is equivalent to an autonumber in Access/MySQL, but is much more flexible, as it can be used across multiple tables, referenced by stored procedures, etc. Sequences can be configured to begin at any value, so if you wanted the invoices to begin at 100000, just set this value as the initial number for the sequence.

    If you wish to have a sequence automatically generated (ala the autonumber field definition) configure the field data type as Serial, or BigSerial.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Dec 2006
    Posts
    24
    Thank you very much!

    As often the best ideas sound simple (in review)
    Actually i am migrating a MS SQL Database to Postgres and mechanisms as Sequences are new to me. I thougt of Sequences as little Helper for comfortable autoincrement-Columns.

    Your Reply and specially the link into the docu showed me up what Sequences really are and what else can be done with them!


    Great!

    Thank you very much!
    an Apple a day keeps Dr. Watson away !

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Sequences ARE used mainly for 'autonumbering.' But, they're a lot more useful, since they don't HAVE to be tied to any single table, or even to a table at all. You can set them up to be accessed only by stored procedures or triggers.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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