Problem with subquery in CHECK constraint.

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) NOT NULL,

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) )
);

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 FROM XREF 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) NOT NULL,

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) NOT NULL,

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(dot)smart(at)ebeon(dot)com
phone: (087) 8052390