some verification functions for sqitch verify scripts.

verification_functions.sql

  -- select public.verify_index_exists('index','schema',true);
  CREATE OR REPLACE FUNCTION public.verify_index_exists(IN _index VARCHAR, IN _schema VARCHAR, IN _throws_error BOOLEAN DEFAULT TRUE)
  RETURNS BOOLEAN AS
  $$
  BEGIN
    IF NOT EXISTS (SELECT relname FROM pg_class c, pg_namespace n WHERE c.relnamespace = n.oid AND relname = $1 AND nspname = $2 AND relkind = 'i') THEN
      IF $3 THEN
        RAISE 'no such index % on schema %', $1, $2;
      ELSE
        RETURN FALSE;
      END If;
    ELSE
      RETURN TRUE;
    END IF;
  END;
  $$
  LANGUAGE plpgsql STABLE;

  -- select public.verify_index_exists(ARRAY['index1','index2'], 'schema', true);
  CREATE OR REPLACE FUNCTION public.verify_index_exists(IN _indexes VARCHAR[], IN _schema VARCHAR, IN _throws_error BOOLEAN DEFAULT TRUE)
  RETURNS BOOLEAN AS
  $$
   DECLARE
    idx varchar;
    truthiness BOOLEAN := FALSE;
  BEGIN
    FOREACH idx IN ARRAY $1
    LOOP
      IF (public.verify_index_exists(idx, $2, $3)) THEN
        truthiness := TRUE;
      ELSE
        truthiness := FALSE;
      END IF;
    END LOOP;
    RETURN truthiness;
  END;
  $$
  LANGUAGE plpgsql STABLE;

  -- select public.verify_index_exists(ARRAY['index1','index2'], ARRAY['schema1','schema2'], true);
  CREATE OR REPLACE FUNCTION public.verify_index_exists(IN _indexes VARCHAR[], IN _schema VARCHAR[], IN _throws_error BOOLEAN DEFAULT TRUE)
  RETURNS BOOLEAN AS
  $$
  DECLARE
    i integer;
    truthiness BOOLEAN := FALSE;
  BEGIN
    IF array_upper($1,1) <> array_upper($2, 1) THEN
      RAISE 'array of indexes and array of schemas need to be the same size';
    END IF;
    FOR i IN 1 .. array_upper($1, 1)
    LOOP
      IF (public.verify_index_exists($1[i], $2[i], $3)) THEN
        truthiness := TRUE;
      ELSE
        truthiness := FALSE;
      END IF;
    END LOOP;
    RETURN truthiness;
  END;
  $$
  LANGUAGE plpgsql STABLE;

  -- select public.verify_trigger_exists('trigger','table',true)
  CREATE OR REPLACE FUNCTION public.verify_trigger_exists(IN _trggr VARCHAR, IN _tbl VARCHAR, IN _throws_error BOOLEAN DEFAULT TRUE)
  RETURNS BOOLEAN AS
  $$
  BEGIN
    IF NOT EXISTS (SELECT tgrelid FROM pg_trigger trg, pg_class pgc
      WHERE trg.tgname = $1 AND pgc.relname = $2
      AND trg.tgrelid = pgc.oid) THEN
      IF $3 THEN
        RAISE 'no such trigger $ on table %', $1, $2;
      ELSE
        RETURN FALSE;
      END IF;
    ELSE
      RETURN TRUE;
    END IF;
  END;
  $$
  LANGUAGE plpgsql STABLE;

  -- select public.verify_trigger_exists(ARRAY['trigger1','trigger2'], 'table', false)
  CREATE OR REPLACE FUNCTION public.verify_trigger_exists(IN _trggrs VARCHAR[], IN _tbls VARCHAR, IN _throws_error BOOLEAN DEFAULT TRUE)
  RETURNS BOOLEAN AS
  $$
  DECLARE
    trg VARCHAR;
    truthiness BOOLEAN := FALSE;
  BEGIN
    FOREACH trg IN ARRAY $1
    LOOP
      IF (public.verify_trigger_exists(trg, $2, $3)) THEN
        truthiness := TRUE;
      ELSE
        truthiness := FALSE;
      END IF;
    END LOOP;
    RETURN truthiness;
  END;
  $$
  LANGUAGE plpgsql STABLE;

  -- select public.verify_trigger_exists(ARRAY['trigger1','trigger2'], ARRAY['tbl1','tbl2'], false);
  CREATE OR REPLACE FUNCTION public.verify_trigger_exists(IN _trggrs VARCHAR[], IN _tbls VARCHAR[], IN _throws_error BOOLEAN DEFAULT TRUE)
  RETURNS BOOLEAN AS
  $$
  DECLARE
    i integer;
    truthiness BOOLEAN := FALSE;
  BEGIN
    IF array_upper($1,1) <> array_upper($2, 1) THEN
      RAISE 'array of triggers and array of tables need to be the same size';
    END IF;
    FOR i IN 1 .. array_upper($1, 1)
    LOOP
      IF (public.verify_trigger_exists($1[i], $2[i], $3)) THEN
        truthiness := TRUE;
      ELSE
        truthiness := FALSE;
      END IF;
    END LOOP;
    RETURN truthiness;
  END;
  $$
  LANGUAGE plpgsql STABLE;

COMMIT;