Problem with subquery in CHECK constraint. - Mailing list pgsql-sql

Hi,

I'm using a general lookup table defined as following
to avoid a proliferation of lookup tables:

CREATE TABLE XREF
(       XREF_GROUP      VARCHAR(12)     NOT NULL,       XREF_CD         VARCHAR(8)      NOT NULL,       XREF_VALUE
VARCHAR(128),
       PRIMARY KEY (XREF_GROUP, XREF_CD)
);

INSERT INTO XREF VALUES ('CUST_TYPE', 'EXP', 'Expired');
INSERT INTO XREF VALUES ('CUST_TYPE', 'ACTIV', 'Active');
INSERT INTO XREF VALUES ('CUST_TYPE', 'POT', 'Potential');

I'm trying to define a check constraint to validate
lookup codes used, for example:

CREATE TABLE CUST
(       CUST_ID         INTEGER NOT NULL,       NAME            VARCHAR(64) NOT NULL,       TYPE_CD         VARCHAR(8)
NOTNULL,              CONSTRAINT CUST_PK PRIMARY KEY (CUST_ID),       CONSTRAINT TYPE_CD_OK CHECK ( EXISTS (SELECT 1
FROMXREF WHERE                                       XREF_GROUP = 'CUST_TYPE' AND XREF_CD = TYPE_CD) )
 
);

However when trying to insert into CUST I get the following error:
       ERROR:  ExecEvalExpr: unknown expression type 108

Is this a bug in PostGreSQL?  I can work around it by
defining a function and using it in the CHECK constraint
for now.  See the appended test file for example SQL.

By the way, there was a massive performance difference in using:
       SELECT $2 IS NULL OR EXISTS (SELECT 1 FROM XREF WHERE XREF_GROUP = $1 AND XREF_CD = $2)

versus the slower:
       SELECT $2 IN (SELECT XREF_CD FROM XREF WHERE XREF_GROUP = $1)

for validation, is this to be expected?

Here's the test case:

DROP TABLE XREF;

CREATE TABLE XREF
(       XREF_GROUP      VARCHAR(12)     NOT NULL,       XREF_CD         VARCHAR(8)      NOT NULL,       XREF_VALUE
VARCHAR(128),
       PRIMARY KEY (XREF_GROUP, XREF_CD)
);

INSERT INTO XREF VALUES ('CUST_TYPE', 'EXP', 'Expired'); 
INSERT INTO XREF VALUES ('CUST_TYPE', 'ACTIV', 'Active'); 
INSERT INTO XREF VALUES ('CUST_TYPE', 'POT', 'Potential'); 


DROP FUNCTION VALID_XREF(VARCHAR(12), VARCHAR(8));

CREATE FUNCTION VALID_XREF(VARCHAR(12), VARCHAR(8)) RETURNS BOOLEAN AS       'SELECT $2 IS NULL OR EXISTS (SELECT 1
FROMXREF WHERE XREF_GROUP = $1 AND XREF_CD = $2)'
 
LANGUAGE 'SQL';

--
-- Much slower version:
--
--      'SELECT $2 IN (SELECT XREF_CD FROM XREF WHERE XREF_GROUP = $1)'
--


DROP TABLE CUST;

CREATE TABLE CUST
(       CUST_ID         INTEGER NOT NULL,       NAME            VARCHAR(64) NOT NULL,       TYPE_CD         VARCHAR(8)
NOTNULL,
 
       CONSTRAINT CUST_PK PRIMARY KEY (CUST_ID),       CONSTRAINT TYPE_CD_OK CHECK ( EXISTS (SELECT 1 FROM XREF WHERE
                                           XREF_GROUP = 'CUST_TYPE' AND XREF_CD = TYPE_CD) )
 
);

INSERT INTO CUST VALUES (1, 'Joe Bloggs', 'POT');


DROP TABLE CUST;

CREATE TABLE CUST
(       CUST_ID         INTEGER NOT NULL,       NAME            VARCHAR(64) NOT NULL,       TYPE_CD         VARCHAR(8)
NOTNULL,
 
       CONSTRAINT CUST_PK PRIMARY KEY (CUST_ID),       CONSTRAINT TYPE_CD_OK CHECK (VALID_XREF('CUST_TYPE', TYPE_CD))
);

INSERT INTO CUST VALUES (1, 'Joe Bloggs', 'ACTIV');
INSERT INTO CUST VALUES (2, 'Jim Smith', 'foo');



--
Niall Smart

email:  niall.smart@ebeon.com
phone:  (087) 8052390