CHECK constraints with plpgsql functions - check 'fires' BEFORE data modification?

The 'data integrity' rule for database I'm designing says that any
subject we're tracking (persons, companies, whatever) is assigned an
agreement that can be in several states: 'Approved', 'Unapproved' or
'Obsolete'. One subject can have only one (or none) 'Approved' or
'Unapproved' agreement, and as many (or none) 'Obsolete' agreements.

I was thinking on employing the CHECK constraint on agreements table
that would check that there is only one 'Approved' state per subject.

My (simplified) schema looks like this:

CREATE TYPE enum_agreement_state AS ENUM  ('unapproved',   'approved',   'obsolete');

CREATE TABLE subjects
( subject_id serial NOT NULL, subject_name character varying NOT NULL, CONSTRAINT subjects_pkey PRIMARY KEY
(subject_id)
);

CREATE TABLE agreements
( agreement_id serial NOT NULL, subject_id integer NOT NULL, agreement_state enum_agreement_state NOT NULL, CONSTRAINT
agreements_pkeyPRIMARY KEY (agreement_id), CONSTRAINT agreements_subject_id_fkey FOREIGN KEY (subject_id)
REFERENCESsubjects (subject_id) MATCH SIMPLE     ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT
check_agreements_onlyone_approvedCHECK
 
(check_agreements_onlyone_approved(subject_id))
);

CREATE FUNCTION check_agreements_onlyone_approved(a_subject_id integer)
RETURNS boolean AS
$$
SELECTCASE COUNT(agreement_id)    WHEN 0 THEN true    WHEN 1 THEN true    ELSE falseEND FROM agreements WHERE
subject_id= $1 AND agreement_state = 'approved';
 
$$ LANGUAGE 'sql';

Now, the above does not work because CHECK function is fired BEFORE
actuall data modification takes place so I can end up with two rows with
'approved' state for particular subject_id. If I change the CASE...WHEN
conditions so that function returns TRUE only when there is 0 rows for
the state = 'approved' then I have problems with UPDATEing:

UPDATE agreements SET agreement_state = 'obsolete' where subject_id =
<whatever> AND agreement_state = 'approved'

That update will fail because the CHECK function is fired before the
actuall update, and there is allready a row with state = 'approved' in
the table.

Now, I know I could use triggers to achieve desired functionality but I
try to use triggers as seldom as I can. Often ETL scripts disable
triggers so I could end up with data integrity broken.

The 'proper' way to do this (as suggested by earlier posts on this
mailing list) is to use partial UNIQUE indexes, but I have problem with
that too: indexes are not part of DDL (no matter that primary key
constraints and/or unique constraints use indexes to employ those
constraints), and as far as I know there is no 'partial unique
constraint' in SQL?

Does anyone has better suggestion on how to employ the data-integrity
rules I have?

And, wouldn't it be better to have CHECK constraints check the data
AFTER data-modification? I also found no reference on using CHECK
constraints with user-defined functions on postgres manual - there
should be a mention of the way the CHECK constraint works - that is,
function referenced by CHECK constraint is fired BEFORE the actual data
modification occur. The error message is also misleading, for instance,
when I run the before mentioned UPDATE:
constraint_check=# update agreements set agreement_state = 'obsolete'
where subject_id = 1 and agreement_state = 'approved';
ERROR:  new row for relation "agreements" violates check constraint
"check_agreements_onlyone_approved"
Mario