pgcalendar - Infinite Calendar Extension for PostgreSQL

Overview

Installation

Prerequisites

Method 1: Install from PGXN (Recommended)

pip install pgxnclient
pgxn install pgcalendar

Method 2: Install from Source

# Clone the repository
git clone https://github.com/h4kbas/pgcalendar.git
cd pgcalendar
# Build and install
make
sudo make install
# Create extension in your database
psql -d your_database -c "CREATE EXTENSION pgcalendar;"

Method 3: Manual Installation

# Copy files to PostgreSQL extensions directory
sudo cp pgcalendar.control /usr/share/postgresql/15/extension/
sudo cp pgcalendar.sql /usr/share/postgresql/15/extension/pgcalendar--1.0.1.sql
sudo cp pgcalendar--uninstall.sql /usr/share/postgresql/15/extension/pgcalendar--1.0.1--uninstall.sql
# Create extension in your database
psql -d your_database -c "CREATE EXTENSION pgcalendar;"

Method 4: Direct SQL Installation

# Run SQL file directly (simpler, but not using CREATE EXTENSION)
psql -d your_database -f pgcalendar.sql

Verification

-- Check if extension is installed
SELECT * FROM pg_extension WHERE extname = 'pgcalendar';
-- Test basic functionality
SELECT * FROM pgcalendar.event_calendar LIMIT 5;

Quick Start

-- 1. Create an event
INSERT INTO pgcalendar.events (name, description, category)
VALUES ('Daily Standup', 'Team daily standup meeting', 'meeting');
-- 2. Get the event_id
SELECT event_id FROM pgcalendar.events WHERE name = 'Daily Standup';
-- 3. Create a schedule (replace X with actual event_id)
INSERT INTO pgcalendar.schedules (
 event_id, start_date, end_date, recurrence_type, recurrence_interval
) VALUES (
 X, '2024年01月01日 09:00:00', '2024年01月07日 23:59:59', 'daily', 1
);
-- 4. Get projections
SELECT * FROM pgcalendar.get_event_projections(X, '2024年01月01日'::date, '2024年01月07日'::date);

Usage Examples

Daily Schedule

INSERT INTO pgcalendar.schedules (
 event_id, start_date, end_date, recurrence_type, recurrence_interval
) VALUES (
 1, '2024年01月01日 09:00:00', '2024年01月07日 23:59:59', 'daily', 1
);

Weekly Schedule

INSERT INTO pgcalendar.schedules (
 event_id, start_date, end_date, recurrence_type, recurrence_interval, recurrence_day_of_week
) VALUES (
 1, '2024年01月01日 10:00:00', '2024年12月31日 23:59:59', 'weekly', 1, 1
);
-- recurrence_day_of_week: 0=Sunday, 1=Monday, etc.

Monthly Schedule

INSERT INTO pgcalendar.schedules (
 event_id, start_date, end_date, recurrence_type, recurrence_interval, recurrence_day_of_month
) VALUES (
 1, '2024年01月01日 10:00:00', '2024年12月31日 23:59:59', 'monthly', 1, 15
);
-- recurrence_day_of_month: 1-31

Yearly Schedule

INSERT INTO pgcalendar.schedules (
 event_id, start_date, end_date, recurrence_type, recurrence_interval, recurrence_month, recurrence_day_of_month
) VALUES (
 1, '2024年01月01日 10:00:00', '2030年12月31日 23:59:59', 'yearly', 1, 1, 1
);
-- recurrence_month: 1-12, recurrence_day_of_month: 1-31

Adding Exceptions

-- Cancel a specific occurrence
INSERT INTO pgcalendar.exceptions (
 schedule_id, exception_date, exception_type, notes
) VALUES (
 1, '2024年01月15日', 'cancelled', 'Holiday - meeting cancelled'
);
-- Modify time only
INSERT INTO pgcalendar.exceptions (
 schedule_id, exception_date, exception_type, modified_start_time, modified_end_time, notes
) VALUES (
 1, '2024年01月22日', 'modified', '2024年01月22日 11:00:00', '2024年01月22日 12:00:00', 'Moved to 11 AM'
);
-- Modify date and time
INSERT INTO pgcalendar.exceptions (
 schedule_id, exception_date, exception_type, modified_date, modified_start_time, modified_end_time, notes
) VALUES (
 1, '2024年01月22日', 'modified', '2024年01月23日', '2024年01月23日 14:00:00', '2024年01月23日 15:00:00', 'Moved to next day'
);

Multiple Schedule Configurations

-- First schedule: Daily for first week
INSERT INTO pgcalendar.schedules (
 event_id, start_date, end_date, recurrence_type, recurrence_interval
) VALUES (1, '2024年01月01日 09:00:00', '2024年01月07日 23:59:59', 'daily', 1);
-- Second schedule: Every other day for second week (no overlap!)
INSERT INTO pgcalendar.schedules (
 event_id, start_date, end_date, recurrence_type, recurrence_interval
) VALUES (1, '2024年01月08日 09:00:00', '2024年01月14日 23:59:59', 'daily', 2);

Advanced Functions

Schedule Transition

SELECT pgcalendar.transition_event_schedule(
 p_event_id := 1,
 p_new_start_date := '2024年01月15日 09:00:00',
 p_new_end_date := '2024年01月31日 23:59:59',
 p_recurrence_type := 'weekly',
 p_recurrence_interval := 2,
 p_recurrence_day_of_week := 1,
 p_description := 'Changed to bi-weekly schedule'
);

Overlap Checking

SELECT pgcalendar.check_schedule_overlap(
 p_event_id := 1,
 p_start_date := '2024年01月05日 09:00:00',
 p_end_date := '2024年01月10日 23:59:59'
);

Querying Projections

Get Projections for an Event

SELECT * FROM pgcalendar.get_event_projections(
 p_event_id := 1,
 p_start_date := '2024年01月01日'::date,
 p_end_date := '2024年01月31日'::date
);

Get All Events with Details

SELECT * FROM pgcalendar.get_events_detailed(
 p_start_date := '2024年01月01日'::date,
 p_end_date := '2024年01月31日'::date
);

Use the Calendar View

SELECT * FROM pgcalendar.event_calendar;

Schema Reference

Tables

Functions

Views

Rules and Constraints

Testing

Setup

# Install dependencies
npm install
# Start test database (using Docker)
npm run test:db:start
# Or set environment variables
export PG_HOST=localhost
export PG_PORT=5433
export PG_USER=postgres
export PG_PASSWORD=postgres
export PG_DB=pgcalendar_test

Running Tests

# Run all tests
npm test
# Run with coverage
npm run test:coverage
# Type check
npm run type-check

Test Database Setup

# Start PostgreSQL container
docker run -d --name pgcalendar-test \
 -e POSTGRES_USER=postgres \
 -e POSTGRES_PASSWORD=postgres \
 -e POSTGRES_DB=pgcalendar_test \
 -p 5433:5432 \
 postgres:15
# Install extension
docker exec -i pgcalendar-test psql -U postgres -d pgcalendar_test -f pgcalendar.sql
# Run tests
npm test
# Cleanup
docker stop pgcalendar-test && docker rm pgcalendar-test

Uninstallation

DROP EXTENSION pgcalendar;

Troubleshooting

Extension Not Found

# Check if files are in the right location
ls -la /usr/share/postgresql/*/extension/pgcalendar*
# Verify PostgreSQL version
pg_config --version

Permission Denied

# Fix permissions if needed
sudo chmod 644 /usr/share/postgresql/*/extension/pgcalendar*

Schema Already Exists

DROP SCHEMA IF EXISTS pgcalendar CASCADE;
CREATE EXTENSION pgcalendar;

License

Support