Skip to content
HeliosDB HeliosDB Full

WASM Procedures: Procedure Management

WASM Procedures: Procedure Management

Documentation Home > User Guides > Features > Procedure Management

Overview

This guide covers the complete lifecycle of WASM procedures in HeliosDB, including creation, updating, versioning, calling, and managing procedures through SQL and application code.

Related Sections:

Procedure Management

Creating Procedures

SQL Syntax

CREATE [OR REPLACE] PROCEDURE procedure_name (
    parameter1 type1,
    parameter2 type2,
    ...
)
[RETURNS return_type]
LANGUAGE wasm
[SECURITY PROFILE 'profile_name']
[DESCRIPTION 'procedure description']
AS source;

Examples

From file:

CREATE PROCEDURE calculate_discount(
    order_total DECIMAL(10,2),
    tier VARCHAR(20)
)
RETURNS DECIMAL(10,2)
LANGUAGE wasm
SECURITY PROFILE 'standard'
DESCRIPTION 'Calculate customer discount based on tier'
AS '/opt/procedures/discount.wasm';

From binary:

CREATE PROCEDURE process_order(order_id BIGINT)
LANGUAGE wasm
AS BYTEA '\x00\x61\x73\x6d...'; -- WASM binary in hex

With custom security:

CREATE PROCEDURE sync_external_data()
LANGUAGE wasm
SECURITY PROFILE 'elevated'
AS '/opt/procedures/sync.wasm';

Updating Procedures

Hot Reload (Zero Downtime)

ALTER PROCEDURE calculate_discount
SET SOURCE '/opt/procedures/discount_v2.wasm';

HeliosDB performs zero-downtime updates:

  1. Compiles new WASM module
  2. Waits for in-flight executions to complete
  3. Swaps to new version
  4. Keeps old version for rollback

Update Security Profile

ALTER PROCEDURE my_procedure
SET SECURITY PROFILE 'elevated';

Update Metadata

ALTER PROCEDURE my_procedure
SET DESCRIPTION 'Updated description';

Dropping Procedures

-- Drop procedure
DROP PROCEDURE calculate_discount;


-- Drop if exists
DROP PROCEDURE IF EXISTS calculate_discount;


-- Drop cascade (removes dependencies)
DROP PROCEDURE calculate_discount CASCADE;

Calling Procedures

Direct Call

-- Simple call
CALL calculate_discount(1000.00, 'gold');


-- Call with result
SELECT calculate_discount(1000.00, 'gold') AS discount;


-- Use in query
SELECT
    order_id,
    total,
    calculate_discount(total, customer_tier) AS discount
FROM orders;

Prepared Call

PREPARE discount_calc AS
    SELECT calculate_discount($1, $2);


EXECUTE discount_calc(1000.00, 'gold');

Application Integration

Python:

import psycopg2


conn = psycopg2.connect("dbname=heliosdb")
cursor = conn.cursor()


# Call procedure
cursor.execute("SELECT calculate_discount(%s, %s)", (1000.00, 'gold'))
discount = cursor.fetchone()[0]


print(f"Discount: ${discount:.2f}")

Node.js:

const { Client } = require('pg');


const client = new Client({ database: 'heliosdb' });
await client.connect();


const result = await client.query(
    'SELECT calculate_discount($1, $2)',
    [1000.00, 'gold']
);


console.log(`Discount: $${result.rows[0].calculate_discount}`);

Java:

Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost/heliosdb");
CallableStatement stmt = conn.prepareCall("{? = call calculate_discount(?, ?)}");
stmt.registerOutParameter(1, Types.DECIMAL);
stmt.setDouble(2, 1000.00);
stmt.setString(3, "gold");
stmt.execute();


double discount = stmt.getDouble(1);
System.out.println("Discount: $" + discount);

Versioning

HeliosDB supports procedure versioning for safe deployments:

-- Create version 1
CREATE PROCEDURE my_procedure@v1(x INT)
LANGUAGE wasm
AS '/path/to/v1.wasm';


-- Create version 2
CREATE PROCEDURE my_procedure@v2(x INT, y INT)
LANGUAGE wasm
AS '/path/to/v2.wasm';


-- Call specific version
SELECT my_procedure@v1(100);
SELECT my_procedure@v2(100, 200);


-- Update default version
ALTER PROCEDURE my_procedure
SET DEFAULT VERSION 'v2';


-- Now calls v2 by default
SELECT my_procedure(100, 200);

Listing Procedures

-- List all procedures
SELECT * FROM heliosdb_procedures;


-- Filter by language
SELECT name, created_at, size_bytes
FROM heliosdb_procedures
WHERE language = 'wasm';


-- View procedure details
SELECT * FROM heliosdb_procedure_info WHERE name = 'calculate_discount';

Output:

name                | language | security_profile | size_bytes | created_at          | last_modified
--------------------|----------|------------------|------------|---------------------|-------------------
calculate_discount  | wasm     | standard         | 45231      | 2025-11-01 10:00:00 | 2025-11-01 14:30:00
process_order       | wasm     | elevated         | 67890      | 2025-11-01 11:00:00 | 2025-11-01 11:00:00

Key Takeaways

  1. Flexible Deployment: Load procedures from files or inline binary data
  2. Zero-Downtime Updates: Hot reload procedures without service interruption
  3. Version Control: Support multiple versions of procedures simultaneously
  4. Universal Access: Call procedures from SQL queries or any PostgreSQL-compatible client
  5. Comprehensive Metadata: Track procedure information through system catalogs

Next Steps

Navigation: ← Use Cases | Index | Performance →