API Reference¶

Constructors¶

Always build a schedule through one of these — never write out the underlying ROW(...) by hand. The composite has seven fields, five or six of which are usually NULL; a misplaced comma in a hand-written ROW() is a silent bug, whereas a constructor call fails loudly at the point you made the mistake, because every constructor returns the validating pgrelay.schedule domain.

Constructor	Mode	Meaning
`pgrelay.on_dow(dow, times)`	1	Every matching weekday
`pgrelay.weekly(dow, times)`	1	Alias for `on_dow` — reads better for "once a week"
`pgrelay.daily(times)`	1	Every day
`pgrelay.weekdays(times)`	1	Monday–Friday
`pgrelay.weekends(times)`	1	Saturday–Sunday
`pgrelay.nth_dow(dow, times, VARIADIC nth)`	2	The nth (or nth-from-last) weekday of the month
`pgrelay.every_weeks(dow, weeks, times[, anchor])`	3	Every N weeks
`pgrelay.fortnightly(dow, times[, anchor])`	3	Alias for `every_weeks(dow, 2, times, anchor)`
`pgrelay.on_dom(times, VARIADIC dom)`	4	Specific day(s) of the month
`pgrelay.month_end(times)`	4	Alias for `on_dom(times, -1)`
`pgrelay.month_start(times)`	4	Alias for `on_dom(times, 1)`
`pgrelay.on_day_times(VARIADIC groups)`	5	Independent per-day times — each argument is one `'dow=times'` group

Why nth and dom take plain integers, not smallint

nth and dom are declared VARIADIC int, not VARIADIC smallint. This is intentional: PostgreSQL doesn't implicitly cast a plain integer literal to smallint, so a smallint[] variadic parameter would reject calls like pgrelay.on_dom('330', 3, 5) outright. The values are narrowed internally. To pass an existing array instead of listing values, use the VARIADIC keyword at the call site: pgrelay.on_dom('330', VARIADIC ARRAY[3, 5, 22]).

Examples¶

-- weekdays at 04:30 and 17:00
SELECT pgrelay.weekdays('4:30,1700');

-- every day at 02:00
SELECT pgrelay.daily('0200');

-- Tuesdays at 06:45
SELECT pgrelay.weekly('tue', '645');

-- 3rd, 5th, 7th, 11th, 22nd of the month at 03:30
SELECT pgrelay.on_dom('330', 3, 5, 7, 11, 22);

-- 2nd Tuesday of the month at 06:45
SELECT pgrelay.nth_dow('tue', '645', 2);

-- last Friday of the month at 17:00
SELECT pgrelay.nth_dow('fri', '1700', -1);

-- 1st and 3rd Monday, twice a day
SELECT pgrelay.nth_dow('mon', '05:30,1700', 1, 3);

-- every fortnight on Tuesday, aligned to the shared default epoch
SELECT pgrelay.fortnightly('tue', '645');

-- every fortnight on Tuesday, staggered onto the opposite week
SELECT pgrelay.fortnightly('tue', '645', DATE '2026-08-10');

-- every third week, Monday and Wednesday
SELECT pgrelay.every_weeks('mon,wed', 3, '0200', DATE '2026-08-03');

-- last day of every month at 23:30
SELECT pgrelay.month_end('2330');

-- Monday at 05:00; Tuesday at 17:30 and 20:00; Wednesday at 12:00 -- one
-- group per argument, or a single pre-joined 'dow=times;dow=times' string
SELECT pgrelay.on_day_times('mon=05:00', 'tue=17:30,20:00', 'wed=12:00');

Evaluation¶

These are what your application actually calls at runtime:

Function	Returns	Meaning
`pgrelay.fires_on(s, date)`	`boolean`	Does this schedule fire on this calendar date?
`pgrelay.occurrences(s, from, to)`	`setof timestamptz`	Every fire instant in `[from, to]` (both boundaries inclusive), in UTC, ascending, with no duplicates.
`pgrelay.next_run(s [, after])`	`timestamptz`	The next fire instant strictly after `after` (default `now()`). Searches a 400-day window; returns `NULL` if nothing qualifies inside it (this only happens with an unrealistic schedule — the largest supported gap, `week_interval = 52`, is 364 days).
`pgrelay.matches(s [, ts])`	`boolean`	Is `ts` (default `now()`) a fire instant, to the minute?
`pgrelay.describe(s)`	`text`	A human-readable rendering, e.g. `'2nd Tuesday of the month at 06:45 UTC'` — useful in logs and admin screens.

matches() needs a reliable per-minute tick to be safe

If whatever calls matches() misses a minute (a restart, a slow query, a paused container), that minute's scheduled run is silently skipped — there is no catch-up. For anything that actually matters, use next_run() stored in a watermark column instead, which self-heals after any outage. See the Watermark Pattern chapter.

-- does this schedule run on 29 Feb 2024?
SELECT pgrelay.fires_on(pgrelay.month_end('2330'), DATE '2024-02-29');
-- t (Feb 2024 is a leap year, so the last day is the 29th)

-- every fire instant in January 2026
SELECT * FROM pgrelay.occurrences(
    pgrelay.weekdays('4:30,1700'), '2026-01-01', '2026-01-31 23:59:59+00');

-- when does this next run, after right now?
SELECT pgrelay.next_run(pgrelay.nth_dow('mon', '0600', 1));

-- a friendly rendering for a dashboard
SELECT pgrelay.describe(pgrelay.fortnightly('tue', '645'));
-- 'every 2nd week on Tue (from week of 1970-01-05) at 06:45 UTC'

Parsers¶

Exposed directly in case you need them — most applications never call these themselves, since the constructors and evaluators already use them internally.

Function	Returns	Meaning
`pgrelay.parse_dow(text)`	`smallint[]`	Parses a `dow` string into sorted, deduplicated ISO weekday numbers.
`pgrelay.parse_times(text)`	`time[]`	Parses a `times` string into sorted, deduplicated times of day.
`pgrelay.dow_token(text)`	`smallint`	Parses a single day token (`'tue'`, `'thursday'`, `'4'`) into its ISO weekday number, or `NULL` if unrecognised.
`pgrelay.ordinal(smallint)`	`text`	Renders a number as an ordinal string (`2` → `'2nd'`, `-1` → `'last'`).
`pgrelay.week_epoch()`	`date`	The fixed default week-zero anchor (1970-01-05, a Monday) used when `anchor` is left `NULL`.
`pgrelay.parse_day_times(text)`	`setof (dow smallint, tm time)`	Parses a `day_times` string into its sorted, deduplicated `(weekday, time)` pairs.

Combining schedules: `pgrelay.schedule[]` overloads¶

Every evaluation function above also has a pgrelay.schedule[] overload — same name, taking an array of schedules instead of one. This is strictly more expressive than any single value: an array can mix modes (weekdays at 08:00 plus month-end at 23:30), which no scalar schedule — in any mode, including day_times — can ever express on its own.

pgrelay.next_run(s[] [, after])     -> timestamptz       -- earliest across every element
pgrelay.fires_on(s[], date)         -> boolean            -- does any element fire?
pgrelay.occurrences(s[], from, to)  -> setof timestamptz  -- union, ascending, de-duplicated
pgrelay.matches(s[] [, ts])         -> boolean            -- does any element match?
pgrelay.describe(s[])               -> text               -- every element's own describe(), joined "; "

-- when is either of these two jobs next due?
SELECT pgrelay.next_run(
    ARRAY[pgrelay.weekdays('0800'), pgrelay.month_end('2330')]);

A NULL array and an empty array both behave predictably — NULL for next_run(), false for the boolean functions, NULL for describe() — never an error, and a NULL element mixed into a real array is simply skipped.

Continue to the Watermark Pattern to see how these fit together in a real table.