1 -- Evergreen DB patch 0657.schema.address-alert.sql
5 -- check whether patch can be applied
6 SELECT evergreen.upgrade_deps_block_check('0657', :eg_version);
8 CREATE TABLE actor.address_alert (
10 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
11 active BOOL NOT NULL DEFAULT TRUE,
12 match_all BOOL NOT NULL DEFAULT TRUE,
13 alert_message TEXT NOT NULL,
21 mailing_address BOOL NOT NULL DEFAULT FALSE,
22 billing_address BOOL NOT NULL DEFAULT FALSE
25 CREATE OR REPLACE FUNCTION actor.address_alert_matches (
34 mailing_address BOOL DEFAULT FALSE,
35 billing_address BOOL DEFAULT FALSE
36 ) RETURNS SETOF actor.address_alert AS $$
39 FROM actor.address_alert
42 AND owner IN (SELECT id FROM actor.org_unit_ancestors($1))
44 (NOT mailing_address AND NOT billing_address)
45 OR (mailing_address AND $9)
46 OR (billing_address AND $10)
51 AND COALESCE($2, '') ~* COALESCE(street1, '.*')
52 AND COALESCE($3, '') ~* COALESCE(street2, '.*')
53 AND COALESCE($4, '') ~* COALESCE(city, '.*')
54 AND COALESCE($5, '') ~* COALESCE(county, '.*')
55 AND COALESCE($6, '') ~* COALESCE(state, '.*')
56 AND COALESCE($7, '') ~* COALESCE(country, '.*')
57 AND COALESCE($8, '') ~* COALESCE(post_code, '.*')
71 ORDER BY actor.org_unit_proximity(owner, $1)
77 DROP FUNCTION actor.address_alert_matches(INT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, BOOL, BOOL);
78 DROP TABLE actor.address_alert;