jigsaw-pg (PostgreSQL extension for Jigsaw Stack)

Small C/PGXS extension that lets you call Jigsaw Stack APIs from SQL.

Install (Docker Compose)

• Create a .env next to docker-compose.yml:
  • JIGSAW_API_KEY=sk_...
  • JIGSAW_BASE_URL=https://api.jigsawstack.com
  • JIGSAW_ROUTES={"summary":"v1/ai/summary","sentiment":"v1/ai/sentiment"}
• Start: docker compose up -d (builds the image, installs the extension).
• Connect: psql -h 127.0.0.1 -U postgres -d postgres
• Verify: SHOW shared_preload_libraries; must include jigsaw_pg.

Install (Manual, host)

• Prereqs: apt-get install -y postgresql-server-dev-15 libcurl4-openssl-dev
• Build: make USE_PGXS=1
• Install: sudo make USE_PGXS=1 install
• In psql: CREATE EXTENSION jigsaw_pg;

Configuration

• GUCs (also read from environment with precedence):
  • jigsaw.api_key (env JIGSAW_API_KEY)
  • jigsaw.base_url (env JIGSAW_BASE_URL, default https://api.jigsawstack.com)
  • jigsaw.routes (env JIGSAW_ROUTES, JSON mapping endpoint keys to paths)
  • jigsaw.timeout_ms (env JIGSAW_TIMEOUT_MS, default 30000)
• Example (SQL):
  • ALTER SYSTEM SET jigsaw.base_url = 'https://api.jigsawstack.com';
  • ALTER SYSTEM SET jigsaw.routes = '{"summary":"v1/ai/summary","sentiment":"v1/ai/sentiment"}';
  • SELECT pg_reload_conf();

Functions

• jigsaw.call(endpoint_key text, payload jsonb) returns jsonb
  • Posts payload to the route resolved from jigsaw.routes[endpoint_key] or uses endpoint_key as the path.
  • Example: SELECT jigsaw.call('summary', '{"text":"Hello","type":"points","max_points":3}'::jsonb);
• jigsaw.summary(text text, url text, type text default 'text', max_points int default 2, max_characters int default 0) returns jsonb
  • Builds the correct JSON for v1/ai/summary.
  • Example: SELECT jigsaw.summary('Hello world', NULL, 'points', 3, 0);
• jigsaw.sentiment(text text) returns jsonb
  • Calls v1/ai/sentiment with { "text": ... }.
  • Example: SELECT jigsaw.sentiment('I love this product');
  • CALL can call all the api endpoints from Jigsaw Stack. At the moment only summary and sentiment can be called with a named function.

Tips & Troubleshooting

• Base vs route: you can set base to .../v1 and routes without v1/, or base without /v1 and routes with v1/.... The extension normalizes to avoid double /v1.
• Ensure env is applied: docker compose exec db env | grep JIGSAW.
• GUCs show after load: SHOW jigsaw.base_url; SHOW jigsaw.routes; (library is preloaded via shared_preload_libraries).
• Inspect the effective URL: SELECT jigsaw.resolve('summary');

jigsaw_pg