Advanced Database Features

WoWSQL supports advanced PostgreSQL features: stored procedures, triggers, views, scheduled jobs (via pg_cron) and extensions. Procedure and trigger bodies are PL/pgSQL; WoWSQL wraps the body you send in BEGIN ... END for you.

Authentication: the procedure, trigger, view, event and extension endpoints on this page are control-plane endpoints on https://api.wowsql.com. Send a WoWSQL account access token (the JWT issued when you sign in to your WoWSQL account) as Authorization: Bearer .... Project API keys (wowsql_anon_... / wowsql_service_...) are rejected with 401 here; use them with the REST API on your project host instead.

Stored Procedures

Create reusable SQL logic:

POST /api/v2/procedures

Headers:
  Authorization: Bearer YOUR_ACCESS_TOKEN
  X-Project-Slug: your-project-slug
  Content-Type: application/json

Body:
{
  "name": "get_user_stats",
  "parameters": [
    {"name": "p_user_id", "type": "INT", "mode": "IN"},
    {"name": "p_order_count", "type": "INT", "mode": "OUT"}
  ],
  "body": "SELECT COUNT(*) INTO p_order_count FROM orders WHERE orders.user_id = p_user_id;",
  "comment": "Get user order statistics"
}

Call a Stored Procedure

Values are passed to CALL positionally, in the order you list them; pass null for OUT parameters. The response contains results (rows returned by CALL) and row_count.

POST /api/v2/procedures/{name}/call

Headers:
  Authorization: Bearer YOUR_ACCESS_TOKEN
  X-Project-Slug: your-project-slug
  Content-Type: application/json

Body:
{
  "parameters": {"p_user_id": 1, "p_order_count": null}
}

Database Triggers

Automatically execute SQL on INSERT/UPDATE/DELETE:

The body runs inside a PL/pgSQL trigger function; WoWSQL adds the RETURN NEW / RETURN OLD for you.

POST /api/v2/triggers

Headers:
  Authorization: Bearer YOUR_ACCESS_TOKEN
  X-Project-Slug: your-project-slug
  Content-Type: application/json

Body:
{
  "name": "update_timestamp",
  "table": "users",
  "timing": "BEFORE",
  "event": "UPDATE",
  "body": "NEW.updated_at = NOW();"
}

Database Views

Create virtual tables from complex queries:

POST /api/v2/views

Headers:
  Authorization: Bearer YOUR_ACCESS_TOKEN
  X-Project-Slug: your-project-slug
  Content-Type: application/json

Body:
{
  "name": "active_users",
  "definition": "SELECT * FROM users WHERE status = 'active'"
}

Query a View

GET /api/v2/views/{name}/data

Headers:
  Authorization: Bearer YOUR_ACCESS_TOKEN
  X-Project-Slug: your-project-slug

Scheduled Events

Run SQL tasks on a schedule (like cron jobs). PostgreSQL has no MySQL-style EVENT objects: POST /api/v2/events responds 501 Not Implemented. Enable the pg_cron extension (see Extensions) and schedule jobs in SQL:

SELECT cron.schedule(
  'daily_cleanup',
  '0 2 * * *',                       -- every day at 02:00
  $$DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days'$$
);

The MySQL-style request below is kept for reference; it is rejected with 501:

POST /api/v2/events

Headers:
  Authorization: Bearer YOUR_ACCESS_TOKEN
  X-Project-Slug: your-project-slug
  Content-Type: application/json

Body:
{
  "name": "daily_cleanup",
  "schedule": "EVERY 1 DAY",
  "body": "DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)"   // MySQL syntax: not supported
}

Extensions

Enable PostgreSQL extensions per project. Enabling extensions requires a paid plan (Pro or Scale).

Group	Extensions
Tier 1 (included)	pgcrypto, uuid-ossp, pg_stat_statements
Tier 2	pg_trgm, btree_gin, btree_gist, citext, hstore, fuzzystrmatch, unaccent, cube, earthdistance (requires cube), intarray, ltree, isn, tablefunc
Optional (if installed on the server)	vector, postgis, pg_cron
Blocked	postgres_fdw, dblink, file_fdw, plpython3u, adminpack, pg_surgery

GET  /api/v1/extensions/available                      // Extensions you can enable on your plan
GET  /api/v1/extensions/{project_slug}/installed       // Extensions installed in a project
POST /api/v1/extensions/{project_slug}/enable          // Enable an extension

Headers:
  Authorization: Bearer YOUR_ACCESS_TOKEN
  Content-Type: application/json

Body:
{
  "extension_name": "pg_trgm"
}

Response:
{
  "success": true,
  "extension": "pg_trgm",
  "error": null
}

You can also enable extensions from Database → Extensions in the dashboard.