How to Use Stored Procedures in ParadeDB PostgreSQL

Galaxy Glossary

How do I create and execute stored procedures in ParadeDB?

Stored procedures encapsulate SQL logic on the server, enabling reusable, transactional routines.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What problem do ParadeDB stored procedures solve?

Stored procedures let you keep complex logic close to your ParadeDB data, run several SQL statements atomically, and reuse the same code from many clients without shipping it in application code.

When should I prefer a stored procedure over plain SQL?

Create a procedure when you need multi-step business logic, frequent reuse, tight security, or want to reduce network round-trips. Simple read-only analytics usually stay as plain SQL.

How do I create a stored procedure?

Use CREATE PROCEDURE with a name, parameter list, language, and body. Most ParadeDB users write PL/pgSQL because it supports variables and flow control.

Example: calculate a customer’s lifetime spend

This procedure sums paid Orders and returns the value. It demonstrates parameters, variables, and OUT arguments.

CREATE OR REPLACE PROCEDURE calc_lifetime_spend( IN p_customer_id INT, OUT p_total NUMERIC ) LANGUAGE plpgsql AS $$ BEGIN SELECT COALESCE(SUM(total_amount), 0) INTO p_total FROM Orders WHERE customer_id = p_customer_id; END; $$;

How do I call a stored procedure?

Run CALL with matching positional parameters. ParadeDB returns OUT values as a result set.

CALL calc_lifetime_spend(42);

Can a procedure modify data transactionally?

Yes. All statements inside the procedure run in the same transaction started by CALL. Errors roll back automatically unless you trap them.

How do I update or drop a procedure?

Use CREATE OR REPLACE PROCEDURE to update in place, or DROP PROCEDURE if it is no longer needed. Specify the full parameter signature when dropping.

Updating example

CREATE OR REPLACE PROCEDURE calc_lifetime_spend( IN p_customer_id INT, OUT p_total NUMERIC, OUT p_last_order DATE ) LANGUAGE plpgsql AS $$ BEGIN SELECT COALESCE(SUM(total_amount), 0), MAX(order_date) INTO p_total, p_last_order FROM Orders WHERE customer_id = p_customer_id; END; $$;

What permissions are required?

CREATEDB users with the CREATE privilege on the current schema can create procedures. To run them, grant EXECUTE ON PROCEDURE name TO role.

Best practices for ParadeDB procedures

Use schema-qualified names, add comments with COMMENT ON, avoid dynamic SQL when static can work, and write unit tests in pgTAP or similar.

Common pitfalls and how to avoid them

See the mistakes section below for frequent issues and fixes.

Why How to Use Stored Procedures in ParadeDB PostgreSQL is important

How to Use Stored Procedures in ParadeDB PostgreSQL Example Usage


-- Grant execution rights
grant execute on procedure calc_lifetime_spend(int) to analyst;

-- Call from application
CALL calc_lifetime_spend(101);
-- returns a single row with p_total

How to Use Stored Procedures in ParadeDB PostgreSQL Syntax


CREATE [ OR REPLACE ] PROCEDURE schema.proc_name ( [ [ mode ] param_name data_type [, ...] ] )
LANGUAGE plpgsql
[ AS $$
BEGIN
    -- business logic
END;
$$ ];

mode: IN (default) | OUT | INOUT

Example in ecommerce context:
CREATE OR REPLACE PROCEDURE refund_order(
    IN p_order_id INT,
    OUT p_refunded BOOLEAN
) LANGUAGE plpgsql AS $$
DECLARE
    v_total NUMERIC;
BEGIN
    SELECT total_amount INTO v_total FROM Orders WHERE id = p_order_id;
    UPDATE Orders SET total_amount = 0 WHERE id = p_order_id;
    UPDATE Products p
    SET stock = stock + oi.quantity
    FROM OrderItems oi
    WHERE oi.order_id = p_order_id AND p.id = oi.product_id;
    p_refunded := TRUE;
END;
$$;

Common Mistakes

Using functions instead of procedures for multi-statement logic. Functions run in a SELECT context and cannot commit or roll back; procedures allow CALL, transaction control, and OUT params. Use CREATE PROCEDURE when you need those capabilities.
Forgetting to specify the exact parameter signature in DROP PROCEDURE. ParadeDB stores each overload separately. Always include data types: DROP PROCEDURE calc_lifetime_spend(int);

Frequently Asked Questions (FAQs)

Can I use COMMIT or ROLLBACK inside a ParadeDB procedure?

Yes, but only when the CALL is not already inside another transaction block. Use COMMIT or ROLLBACK to split large batch work where needed.

How do I debug a PL/pgSQL procedure?

Add RAISE NOTICE statements for variable values, or attach pldbgapi-compatible debuggers such as the one in DataGrip. Logging levels can be increased for procedure sessions.