The complicated constraint in this definition of the product table doesn't
work, but should it? or is there a better way to do this?
CREATE FUNCTION ean_checkdigit(bpchar, bpchar) RETURNS BOOLEAN AS '/usr1/proj/bray/sql/funcs.so' LANGUAGE 'C';
CREATE TABLE brandname
( id CHAR(12) PRIMARY KEY, ean_prefix CHAR(8) CHECK (ean_prefix ~ '50-[0-9]{5}')
REFERENCES company(ean_prefix) ON UPDATE CASCADE
ON DELETE NO ACTION, name TEXT NOT NULL
)
;
CREATE TABLE product
( id CHAR(10) PRIMARY KEY, brand CHAR(12) REFERENCES brandname(id)
ON UPDATE CASCADE ON DELETE NO
ACTION, eancode CHAR(6) CHECK (eancode IS NULL OR
eancode~ '[0-9]{6}'), ... CONSTRAINT ean CHECK ( CASE WHEN eancode IS NULL OR brand IS NULL
THEN't' ELSE ean_checkdigit( (SELECT ean_prefix FROM
brandname WHERE brandname.id = brand ), eancode) END )
)
;
copy product from '/rover/avoca/dumps/dbdump.product'
ERROR: copy: line 2, ExecEvalExpr: unknown expression type 108
[line 1 had null values in the relevant fields]
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key
ID32B8FAA1 ======================================== "Live in harmony with one another. Do not be
proud,but be willing to associate with people of low position. Do not be conceited." Romans 12:16