Is this a bug in the table definition or in PostgreSQL?

Started by Oliver Elphickover 25 years ago2 messages
#1Oliver Elphick
olly@lfix.co.uk

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 ID 32B8FAA1
========================================
"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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Oliver Elphick (#1)
Re: Is this a bug in the table definition or in PostgreSQL?

"Oliver Elphick" <olly@lfix.co.uk> writes:

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?
...
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]

108 ... (checks nodes.h) ... SubLink ... looks like your sub-select
isn't getting processed properly. I'd say it's a bug, but it's
probably too late to fix it for 7.0.

regards, tom lane