egdb3_11_7
.acq
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
po_org_name_date_unique()
Parameters
Name
Type
Mode
IN
Definition
DECLARE collision INT; BEGIN -- -- If order_date is not null, then make sure we don't have a collision -- on order_date (truncated to day), org, and name -- IF NEW.order_date IS NULL THEN RETURN NEW; END IF; -- -- In the WHERE clause, we compare the order_dates without regard to time of day. -- We use a pair of inequalities instead of comparing truncated dates so that the -- query can do an indexed range scan. -- SELECT 1 INTO collision FROM acq.purchase_order WHERE ordering_agency = NEW.ordering_agency AND name = NEW.name AND order_date >= date_trunc( 'day', NEW.order_date ) AND order_date < date_trunc( 'day', NEW.order_date ) + '1 day'::INTERVAL AND id <> NEW.id; -- IF collision IS NULL THEN -- okay, no collision RETURN NEW; ELSE -- collision; nip it in the bud RAISE EXCEPTION 'Colliding purchase orders: ordering_agency %, date %, name ''%''', NEW.ordering_agency, NEW.order_date, NEW.name; END IF; END;