[SQL] Problem with subquery in CHECK constraint.

[Niall Smart]

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:  [EMAIL PROTECTED]
phone:  (087) 8052390