Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2014
    Posts
    2

    Question Unanswered: Check constraint function

    Hi forum
    Newbie here, so thanks for being gentle.

    I have a table containing a numeric field which is validated using a technique similar to the Luhn algorithm,
    described at Seek-It:: Ferry Situmorang: Verifying numbers with the Luhn algorithm

    My actual algorithm check is written in perl, and returns boolean result.
    I'd like PostgreSQL to throw an error if the check fails.

    Using PostgreSQL, is it possible to :
    ALTER TABLE somthing
    ADD CONSTRAINT Perl_Function_Algorithm_Check
    CHECK (Perl_Function_Algorithm_Check ());

    TIA's for any tips or clues for how to proceed.

    - BG
    Last edited by bluegroper; 08-10-14 at 19:33.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    You can write the function in pgPerl, then use it in your constraint.

    As the Lund function only uses data from the record in question, there should be no problem if it's used there. a db purist might argue that, as the validation of the number is a business rule, it should be used within a rule or trigger, instead of in a column constraint, though.
    Last edited by loquin; 08-27-14 at 13:24. Reason: clarify
    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
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by bluegroper View Post
    Using PostgreSQL, is it possible to :
    ALTER TABLE somthing
    ADD CONSTRAINT Perl_Function_Algorithm_Check
    CHECK (Perl_Function_Algorithm_Check ());
    As you can write stored functions in Perl, this should be possible. You just need to convert your Perl code into a function in Postgres.

    http://www.postgresql.org/docs/curre...ic/plperl.html
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

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