diff options
Diffstat (limited to 'db/schema_0035-bodies_str-tidying.sql')
| -rw-r--r-- | db/schema_0035-bodies_str-tidying.sql | 21 |
1 files changed, 21 insertions, 0 deletions
diff --git a/db/schema_0035-bodies_str-tidying.sql b/db/schema_0035-bodies_str-tidying.sql index c4c7badaf..f5e1dbbdd 100644 --- a/db/schema_0035-bodies_str-tidying.sql +++ b/db/schema_0035-bodies_str-tidying.sql @@ -7,4 +7,25 @@ update problem bodies_str = split_part(bodies_str, '|', 1) where bodies_str like '%|%'; +create index problem_bodies_str_array_idx on problem USING gin(regexp_split_to_array(bodies_str, ',')); + +UPDATE alert_type set item_where = + 'problem.non_public = ''f'' and problem.state in + (''confirmed'', ''investigating'', ''planned'', ''in progress'', + ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'', + ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'', + ''internal referral'' ) AND + regexp_split_to_array(bodies_str, '','') && ARRAY[?]' + WHERE ref = 'council_problems'; + +UPDATE alert_type set item_where = + 'problem.non_public = ''f'' and problem.state in + (''confirmed'', ''investigating'', ''planned'', ''in progress'', + ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'', + ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'', + ''internal referral'' ) AND + (regexp_split_to_array(bodies_str, '','') && ARRAY[?] or bodies_str is null) and + areas like ''%,''||?||'',%''' + WHERE ref = 'ward_problems'; + commit; |