1 SET search_path=public;
3 DROP TYPE IF EXISTS node_role CASCADE;
4 CREATE TYPE node_role AS ENUM ('O', 'R', 'A', 'D');
6 DROP TABLE IF EXISTS pg_sched CASCADE;
9 id bigserial NOT NULL PRIMARY KEY,
10 datname text NOT NULL,
11 proname text NOT NULL,
12 pronamespace text NOT NULL,
16 frequency interval NOT NULL,
17 frequency_offset interval NOT NULL DEFAULT '0s',
19 enabled node_role NULL DEFAULT 'O',
20 isexclusive boolean NOT NULL DEFAULT true,
21 isloose boolean NOT NULL DEFAULT true,
24 CONSTRAINT pg_sched_unique UNIQUE (datname, proname, pronamespace, proargs, proargtypes, frequency, frequency_offset, enabled),
25 CHECK (array_length(proargs,1) IS NOT DISTINCT FROM array_length(proargtypes,1))
28 CREATE OR REPLACE FUNCTION pg_sched_check_types() RETURNS TRIGGER AS
31 IF EXISTS(SELECT 1 FROM unnest(NEW.proargtypes) a
32 LEFT JOIN pg_catalog.pg_type
33 ON pg_catalog.format_type(oid, NULL) = a
34 OR typname = a WHERE typname IS NULL) THEN
35 RAISE EXCEPTION 'Value for column "proargtypes" contains invalid types: %', NEW.proargtypes;
42 REVOKE ALL ON pg_sched FROM public;
43 GRANT SELECT ON pg_sched TO public;
45 CREATE TRIGGER pg_sched_check_types_trigger
46 BEFORE UPDATE OR INSERT ON pg_sched
47 FOR EACH ROW WHEN (NEW.proargtypes IS NOT NULL)
48 EXECUTE PROCEDURE pg_sched_check_types();
50 -- Superuser is required to run tasks under differing credentials
51 -- CREATE USER pg_sched WITH PASSWORD 'my password' SUPERUSER ;
52 -- ALTER TABLE pg_sched OWNER TO pg_sched;
55 -- INSERT INTO pg_sched (usename, datname, pronamespace, proname, proargs, proargtypes, enabled, frequency, frequency_offset)
56 -- VALUES ('test_user', 'mydb', 'pg_catalog', 'date_part', '{"hour", "2015-03-25 13:50:59"}', '{text, timestamp}', 'A', '1 hour', '1 minute');