adventofcode 2020, day 2 part 1

day2.sql

create table day2 (policy text, password text);
insert into day2 (policy,password) 
values ('1-3 a','abcde'), 
       ('1-3 b','cdefg'),
       ('2-9 c','ccccccccc');

CREATE OR REPLACE FUNCTION count_substring_matches(string TEXT, substr TEXT) RETURNS INTEGER AS $$
DECLARE
  pos INTEGER;
  c INTEGER;
BEGIN
  c := 0;
  pos := strpos(string,substr);
  WHILE pos > 0 LOOP
    string := right(string, -pos);
    c := c + 1;
    pos := strpos(string,substr);
  END LOOP;
  RETURN c;
END
$$ LANGUAGE plpgsql;
  
-- part 1
SELECT SUM(CASE WHEN x.matches >= x.mincount and x.matches <= x.maxcount THEN 1 ELSE 0 END) FROM (
  SELECT substring(policy from 1 for strpos(policy, '-')-1)::integer as mincount,
         substring(policy from strpos(policy,'-')+1 for strpos(policy,' ')-strpos(policy,'-'))::integer as maxcount,
         count_substring_matches(password,  substring(policy from strpos(policy,' ')+1))::integer as matches,
         password
    FROM day2) x;
    

-- part2
SELECT SUM(CASE WHEN y.ch1+y.ch2 = 1 THEN 1 ELSE 0 END) FROM (
SELECT CASE WHEN substring(x.password from x.pos1 for 1) = x.req THEN 1 ELSE 0 END AS ch1, 
       CASE WHEN substring(x.password from x.pos2 for 1) = x.req THEN 1 ELSE 0 END AS ch2 FROM (
  SELECT substring(policy from 1 for strpos(policy, '-')-1)::integer as pos1,
         substring(policy from strpos(policy,'-')+1 for strpos(policy,' ')-strpos(policy,'-'))::integer as pos2,
         substring(policy from strpos(policy,' ')+1) as req,
         password
    FROM day2) x) y;