PostgreSQL Extension SQL Syntax

This page documents the current SQL surface for the pg_kalam PostgreSQL extension.

Extension Metadata

Install and verify the extension:

CREATE EXTENSION IF NOT EXISTS pg_kalam;
 
SELECT kalam_version(), kalam_compiled_mode();

Foreign Server Syntax

Create the PostgreSQL foreign server that points at KalamDB:

CREATE SERVER IF NOT EXISTS kalam_server
  FOREIGN DATA WRAPPER pg_kalam
  OPTIONS (
    host '127.0.0.1',
    port '9188',
    auth_header 'Bearer <token>'
  );

Update it with standard PostgreSQL FDW server syntax:

ALTER SERVER kalam_server OPTIONS (SET host 'kalamdb.internal', SET port '9188');
ALTER SERVER kalam_server OPTIONS (ADD auth_header 'Bearer <replacement-token>');
ALTER SERVER kalam_server OPTIONS (SET timeout '5000');
ALTER SERVER kalam_server OPTIONS (DROP auth_header);

Drop it when you no longer need the bridge:

DROP SERVER kalam_server CASCADE;

Supported Server Options

If you set either client_cert or client_key, you must set both.

Table Creation Syntax

The public table-definition syntax is CREATE TABLE ... USING kalamdb:

CREATE TABLE app.orders (
  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  customer_id TEXT NOT NULL,
  status TEXT DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT NOW()
) USING kalamdb WITH (
  type = 'shared',
  flush_policy = 'rows:1000,interval:60'
);

This does three things:

1. creates the target namespace in KalamDB if needed,
2. creates the KalamDB table using the full DDL,
3. creates the matching PostgreSQL-side relation automatically.

Shared table example

CREATE TABLE app.shared_items (
  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  title TEXT NOT NULL,
  value INTEGER,
  created_at TIMESTAMP DEFAULT NOW()
) USING kalamdb WITH (
  type = 'shared',
  flush_policy = 'rows:1000,interval:60'
);

User table example

SET kalam.user_id = 'user-alice';
 
CREATE TABLE app.profiles (
  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  name TEXT,
  age INTEGER
) USING kalamdb WITH (
  type = 'user'
);

Stream table example

CREATE TABLE app.events (
  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  event_type TEXT NOT NULL,
  payload TEXT,
  created_at TIMESTAMP DEFAULT NOW()
) USING kalamdb WITH (
  type = 'stream',
  ttl_seconds = '3600'
);

Notes for USING kalamdb

• type = 'shared', type = 'user', and type = 'stream' select the KalamDB table type.
• PRIMARY KEY and DEFAULT SNOWFLAKE_ID() are preserved in KalamDB.
• SERIAL, BIGSERIAL, SMALLSERIAL, and GENERATED ... AS IDENTITY are normalized into integer columns with DEFAULT SNOWFLAKE_ID() before the KalamDB DDL is sent.
• Additional WITH options are forwarded to the mirrored KalamDB CREATE TABLE statement.

Any KalamDB CREATE TABLE Form

Any CREATE TABLE form supported by the KalamDB server can be authored from PostgreSQL with the same WITH (...) clause.

That means PostgreSQL can act as the entry point for KalamDB-native table definitions as long as the resulting statement is valid for KalamDB once the extension forwards it.

Common forwarded WITH (...) options

Direct Statement Execution

To pass any statement directly to KalamDB, use kalam_exec(...):

SELECT kalam_exec('ALTER TABLE app.orders ADD COLUMN priority INTEGER DEFAULT 0');
 
SELECT kalam_exec('DROP TABLE app.orders');
 
SELECT kalam_exec('SELECT * FROM system.tables');

kalam_exec(...) sends the SQL string to the KalamDB server as-is.

• For SELECT statements, it returns a JSON array string.
• For DDL and DML statements, it returns a JSON string message.

If you were expecting a schema-qualified helper such as kalam.exec(...), the currently exposed PostgreSQL function name is kalam_exec(...).

Query and Mutation Syntax

Select

SELECT id, title, value
FROM app.shared_items
WHERE value >= 10
ORDER BY id;

Insert

INSERT INTO app.shared_items (id, title, value)
VALUES (SNOWFLAKE_ID(), 'Alpha', 10), (SNOWFLAKE_ID(), 'Beta', 20);

Update

UPDATE app.shared_items
SET value = 25
WHERE id = 2;

Delete

DELETE FROM app.shared_items
WHERE id = 1;

User-scoped DML

SET kalam.user_id = 'user-alice';
 
INSERT INTO app.profiles (id, name, age)
VALUES ('p1', 'Alice', 30);
 
SELECT id, name, age
FROM app.profiles;
 
UPDATE app.profiles
SET age = 31
WHERE id = 'p1';
 
DELETE FROM app.profiles
WHERE id = 'p1';
 
RESET kalam.user_id;

Extension Helper Functions

Session and metadata helpers

SELECT kalam_version();
SELECT kalam_compiled_mode();
SELECT kalam_user_id();
SELECT kalam_user_id_guc_name();

Snowflake ID generation

SELECT SNOWFLAKE_ID();

Use SNOWFLAKE_ID() when you want PostgreSQL-side inserts to generate KalamDB-compatible IDs.

Not Yet Supported

IMPORT FOREIGN SCHEMA is not yet supported in the current remote mode:

IMPORT FOREIGN SCHEMA app FROM SERVER kalam_server INTO app;

For now, use CREATE TABLE ... USING kalamdb for PostgreSQL-side authoring or kalam_exec(...) for direct server execution.

Related

• Getting Started
• Architecture
• Status & Limits