Problem with a constraint check on a table. - Mailing list pgsql-general

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