Skip to content

Instantly share code, notes, and snippets.

@leklund
Created August 6, 2013 14:41
Show Gist options
  • Save leklund/6165100 to your computer and use it in GitHub Desktop.
Save leklund/6165100 to your computer and use it in GitHub Desktop.
some verification functions for sqitch verify scripts.
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment