Skip to content

thesanjeevsharma/postgres-rrule

 
 

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

38 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

postgres-rrule

Parse RRULE statements, and generate occurrences.

Install

Execute postgres-rrule.sql in your database:

  $ psql -X -f postgres-rrule.sql

Or

  $ 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.

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)

Copyright (c) 2015 Matthew Schinckel, 2019 Volkan Unsal

About

Recurring occurrences for Postgres.

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published

Languages

  • PLpgSQL 98.5%
  • Other 1.5%