postgres-rrule

postgres-rrule is a Postgres extension to make it easier to work with recurring dates in the database.

It parses RRULE statements, and generates occurrences.

Install

Execute postgres-rrule.sql in your database:

  $ psql -X -f postgres-rrule.sql

  $ make all

And modify your search path to include _rrule schema:

  SET search_path TO public, _rrule;

How it works

We create a table (in a newly created _rrule schema) called RRULE to generate start dates from rule criteria. The table constraints enforce the validity of dates in the table.

All of the types and functions are created in that schema. You can parse or query RRULE specifications.

On a sidenote, the current implementation does not support endlessly reapeating events. Please make sure that you include the UNTIL or COUNT rrule to check for multiple occurences.

Example

Parsing

newuser@newuser >> select '
    DTSTART:19970902T090000
    RRULE:FREQ=WEEKLY;UNTIL=19980902T090000
    '::TEXT::RRULESET;
┌───────────────────────────────────────────────────────────────────────────────┐
│                                   rruleset                                    │
├───────────────────────────────────────────────────────────────────────────────┤
│ ("1997-09-02 09:00:00",,"(WEEKLY,1,,""1998-09-02 09:00:00"",,,,,,,,,,MO)",,,) │
└───────────────────────────────────────────────────────────────────────────────┘
(1 row)

Querying RRULESET.

newuser@newuser >> select '
    DTSTART:19970902T090000
    RRULE:FREQ=WEEKLY;UNTIL=19980902T090000
    '::TEXT::RRULESET @> '19970902T090000'::timestamp;
┌──────────┐
│ ?column? │
├──────────┤
│ t        │
└──────────┘
(1 row)

Also works with jsonb

newuser@newuser >> select '{"dtend": "1997-09-03T09:00:00", "rrule": {"freq": "WEEKLY", "wkst": "MO", "count": 4, "interval": 1}, "dtstart": "1997-09-02T09:00:00"}'::text::jsonb::rruleset @> '19970902T090000'::timestamp;
┌──────────┐
│ ?column? │
├──────────┤
│ t        │
└──────────┘
(1 row)

`RRULE` Operators

Operator	Description	Notes	Result
`=`	equal	All parameters match	`boolean`
`<>`	not equal	Any parameters don't match	`boolean`
`@>`	contains `rrule`	All occurrences generated by second rule would also be generated by first rule	`boolean`
`<@`	contained by `rrule`		`boolean`

`RRULESET` Operators

Operator	Description	Notes	Result
`@>`	contains `timestamp`	The date of the timestamp is generated by the ruleset.	`boolean`

`RRULESET[]` Operators

Operator	Description	Notes	Result
`@>`	contains `timestamp`	The date of the timestamp is generated by one of the rulesets in the array.	`boolean`

`RRULESET::jsonb` Operators

Operator	Description	Notes	Result
`@>`	contains `timestamp`	The date of the timestamp is generated by ruleset.	`boolean`

`RRULESET[]::jsonb` Operators

Operator	Description	Notes	Result
`@>`	contains `timestamp`	The date of the timestamp is generated by one of the rulesets in the jsonb array.	`boolean`

Functions

In the case of the rrule functions, there is a second required argument of type timestamp, which is the "dtstart" argument of a rruleset. There is a form for each rrule-accepting function that accepts a text value, and parses it.

Testing

Requires pgTAP and pg_resolve. First let's install those from CPAN.

  $ sudo cpan TAP::Parser::SourceHandler::pgTAP

Now you can run the tests with

  $ make all test

Prior Art

This is a fork of Matthew Schinckel's original repo.

License

The MIT License (MIT)

volkanunsal/postgres-rrule