Hi !
I've got a problem when adding a CONSTRAINT CHECK on a table by calling a
function. It just seems not to work...
Here is the table (simplified to only the relevant fields for this case):
CREATE TABLE public.tb_contacts
(
contact_id serial NOT NULL,
actor_id varchar(50) NOT NULL,
contacttype_id varchar(6) NOT NULL,
contact varchar NOT NULL,
contact_principal bool NOT NULL DEFAULT true,
contact_validity bool DEFAULT true,
CONSTRAINT pk_contact PRIMARY KEY (contact_id),
) WITH OIDS;
I created a function to check whether there is more than one record for a
given actor_id and contacttype_id that has both contact_principal and
contact_validity as 'true'.
CREATE FUNCTION public.is_principalcontact_unique(varchar, varchar) RETURNS
bool AS
'
DECLARE
actorID ALIAS FOR $1;
contactTypeID ALIAS FOR $2;
countage SMALLINT;
BEGIN
SELECT INTO countage count(contact_principal)
FROM tb_contacts
WHERE actor_id = actorID
AND contacttype_id = contactTypeID
AND contact_validity = true
AND contact_principal = true
GROUP BY actor_id, contacttype_id;
IF countage > 1 THEN
RETURN false;
END IF;
RETURN true;
END;
' LANGUAGE 'plpgsql' STABLE;
When testing, this function seems to work.
I then added a constraint using that function, to make sure no new record
can be added that would violate that constraint.
ALTER TABLE tb_contacts
ADD CONSTRAINT CKC_UNIQUE_PRINCIPAL CHECK
(is_principalcontact_unique(actor_id, contacttype_id) = true)
I then tried to add a new record, duplicating another one with both
contact_validity and contact_principal being 'true' (I changed the
contact_id, obviously, to avoid duplicate entries in primary key).
PostgreSQL let me insert it, without raising an error, although the function
is_principalcontact_unique(actor_id, contacttype_id) now returns 'false'
Any idea why it is so?
Fabre Lambeau
Cambridge University
Computer Laboratory
