
DROP TYPE IF EXISTS node_role CASCADE;
CREATE TYPE node_role AS ENUM ('O', 'R', 'A', 'D');

CREATE TABLE pg_sched
(
  id bigserial NOT NULL PRIMARY KEY,
  datname text NOT NULL,
  proname text NOT NULL,
  pronamespace text NOT NULL,
  proargs text[],
  proargtypes text[],
  usename text,
  frequency interval NOT NULL,
  frequency_offset interval NOT NULL DEFAULT '0s',
  last_run timestamp,
  last_completed timestamp,
  enabled node_role NULL DEFAULT 'O', 
  isexclusive boolean NOT NULL DEFAULT true,
  isloose boolean NOT NULL DEFAULT true,
  tag varchar(50),  
  running integer,
  CONSTRAINT pg_sched_unique UNIQUE (datname, proname, pronamespace, proargs, proargtypes, frequency, frequency_offset, enabled),
  CHECK (array_length(proargs,1) IS NOT DISTINCT FROM array_length(proargtypes,1))
);

CREATE OR REPLACE FUNCTION pg_sched_check_types() RETURNS TRIGGER AS
$BODY$
BEGIN
	IF EXISTS(SELECT 1 FROM unnest(NEW.proargtypes) a
			LEFT JOIN pg_catalog.pg_type 
			ON pg_catalog.format_type(oid, NULL) = a
				OR typname = a WHERE typname IS NULL) THEN
		RAISE EXCEPTION 'Value for column "proargtypes" contains invalid types: %', NEW.proargtypes;
	END IF;
	RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

REVOKE ALL ON pg_sched FROM public;
GRANT SELECT ON pg_sched TO public;

CREATE TRIGGER pg_sched_check_types_trigger
BEFORE UPDATE OR INSERT ON pg_sched
FOR EACH ROW WHEN (NEW.proargtypes IS NOT NULL)
EXECUTE PROCEDURE pg_sched_check_types();

-- Superuser is required to run tasks under differing credentials
-- CREATE USER pg_sched WITH PASSWORD 'my password' SUPERUSER ; 
-- ALTER TABLE pg_sched OWNER TO pg_sched;

-- Example task
-- INSERT INTO pg_sched (usename, datname, pronamespace, proname, proargs, proargtypes, enabled, frequency, frequency_offset) 
-- VALUES ('test_user', 'mydb', 'pg_catalog', 'date_part', '{"hour", "2015-03-25 13:50:59"}', '{text, timestamp}', 'A', '1 hour', '1 minute');
