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 



Thu, 04 May 2000 17:44:28 +0100
"Oliver Elphick" <olly@lfix.co.uk>
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

Thu, 04 May 2000 13:42:12 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Is this a bug in the table definition or in PostgreSQL? 