Credit card business logic

CREATE TABLE biz (
  cardnumber CHAR(16) NOT NULL
);

oscon=> INSERT INTO biz VALUES ('ABCDABCDABCDABCD');
INSERT 25460 1

oscon=> INSERT INTO biz VALUES ('1234567');
INSERT 25461 1

oscon=> INSERT INTO biz VALUES ('12345678901234567890');
ERROR:  value too long for type character(16)
  • First: numbers are 16 digits long
  • First problem: length

Next


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Second attempt:

CREATE TABLE biz (
 cardnumber VARCHAR(16) NOT NULL CHECK (length(cardnumber) = 16)
);

oscon=> INSERT INTO biz VALUES ('1234567');
ERROR:  new row for relation "biz" violates check constraint "biz_cardnumber"
  • Switch to VARCHAR (padding)
  • Add a (third) constraint
  • Three?

Next


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Better, but awkward.

CREATE DOMAIN creditcardnumber VARCHAR(16) CHECK (length(VALUE) = 16);

CREATE TABLE biz (
  cardnumber creditcardnumber
);

oscon=> INSERT INTO biz VALUES (null);
INSERT 25462 1

oscon=> INSERT INTO biz VALUES ('1234567');
ERROR:  value for domain creditcardnumber violates check constraint "$1"
  • Time for a domain
  • Not truly a custom data type
  • Where is NOT NULL?
  • CHECK is happy on TRUE or NULL.
  • What's still wrong?

Next


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Better

CREATE DOMAIN ccnumber varchar(16)
  CONSTRAINT "ccnumber_length"
  CHECK (VALUE IS NOT NULL AND length(VALUE) = 16);

oscon=> INSERT INTO biz VALUES ('1234567');
ERROR: value for domain creditcardnumber v. check constraint "ccnumber_length"
  • Good, but that CHECK is still too specific
  • Make it into a function
  • Allows us to reuse code elsewhere
  • Allows us to extend logic without driopping tables
  • Allows us to customize error messages

Next


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
  


CREATE OR REPLACE FUNCTION validate_ccnumber(VARCHAR)
LANGUAGE plpgsql
RETURNS BOOLEAN AS '
DECLARE
  mynumber ALIAS FOR $1;
BEGIN

IF length(mynumber) != 16 THEN
  RAISE EXCEPTION \'Credit card numbers must be 16 digits long\';
END IF;

RETURN TRUE;
END;'

-- We have a nice "service function" - not coupled to any data
-- Very useful, very portable
-- Ignore NULL for now

Next


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
  


oscon=> SELECT validate_ccnumber('12341234123412341');
ERROR:  Credit card numbers must be 16 digits long

Next


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
  


CREATE DOMAIN creditcardnumber varchar(16) CHECK (validate_ccnumber(VALUE));

CREATE TABLE biz2 (
  cardnumber creditcardnumber,
  oldway     VARCHAR(16)
);

CREATE TABLE biz2 (
  cardnumber CREDITCARDNUMBER,
  oldway     VARCHAR(16)
);

Next


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
  


oscon=> INSERT INTO biz2 VALUES ('1234567');
ERROR:  Credit card numbers must be 16 digits long

Next


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
  


-- Avoiding the danger of domains by using black boxes

oscon=> CREATE OR REPLACE DOMAIN creditcardnumber varchar(16) check(length(value)=20);
ERROR:  syntax error at or near "DOMAIN" at character 19

oscon=> DROP DOMAIN creditcardnumber;
NOTICE:  table biz column cardnumber depends on type creditcardnumber
ERROR:  cannot drop type creditcardnumber because other objects depend on it
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

oscon=> DROP DOMAIN creditcardnumber;
DROP DOMAIN

CREATE OR REPLACE FUNCTION validate_ccnumber(VARCHAR) 
RETURNS BOOLEAN AS '
DECLARE
  mynumber ALIAS FOR $1;
BEGIN

IF length(mynumber) != 12 THEN
  RAISE EXCEPTION \'Credit card numbers must be 12 digits long\';
END IF;

RETURN TRUE;
END;
' LANGUAGE plpgsql;

Next


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Warning

  • No revalidation
  • Be careful when switching types mid-stream
  • Documentation mantra
      Last             TOC             Next