How to Use Stored Procedures in PostgreSQL

Galaxy Glossary

How do I create and use stored procedures in PostgreSQL?

Stored procedures encapsulate reusable SQL and control-flow logic on the server, improving performance, security, and maintainability.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What are stored procedures in PostgreSQL?

Stored procedures are precompiled database programs that run with a single CALL statement. They can include SQL, PL/pgSQL control flow, and transaction control, letting you bundle complex logic and execute it server-side for speed and security.

When should I prefer procedures over functions?

Use procedures when you need explicit COMMIT/ROLLBACK, multiple result sets, or dynamic transaction control. Functions suit pure calculations or single-statement queries without transaction management.

How do I create a stored procedure?

Create procedures with CREATE PROCEDURE, define input/output parameters, language, and body. Finish with CALL to execute it.

Example: CreateOrder procedure

This procedure inserts an order and its items in one transaction, committing if all succeed or rolling back on error.

How do I call a stored procedure?

Invoke procedures with CALL. Pass arguments positionally or by name. Because procedures cannot return values directly, capture OUT parameters or query modified tables afterward.

Can I Debug or change a procedure?

Replace logic with CREATE OR REPLACE PROCEDURE. To debug, raise notice messages or use pgAdmin’s debugger. Version procedures in VCS for traceability.

Best practices for stored procedures

1) Keep procedures small and focused. 2) Validate inputs early. 3) Add comments describing parameters and side effects. 4) Use RAISE EXCEPTION for predictable error handling.

How do I grant access?

GRANT EXECUTE ON PROCEDURE lets specific roles call the procedure while keeping underlying tables locked down, enforcing least privilege.

What performance gains can I expect?

Procedures reduce network round-trips, reuse execution plans, and execute transaction logic closer to data, typically cutting latency for chatty workloads by 20-80%.

Why How to Use Stored Procedures in PostgreSQL is important

How to Use Stored Procedures in PostgreSQL Example Usage


-- Call the procedure to create a new order for customer 5
CALL create_order(
    p_customer_id => 5,
    p_items => '[{"product_id":1,"qty":2},{"product_id":3,"qty":1}]'::jsonb,
    p_order_id => NULL
);

-- Verify
SELECT * FROM orders WHERE id = currval('orders_id_seq');

How to Use Stored Procedures in PostgreSQL Syntax


CREATE PROCEDURE procedure_name ( [ IN | OUT | INOUT ] param_name data_type [, ...] )
LANGUAGE plpgsql
[ SECURITY DEFINER ]
AS $$
DECLARE
    -- variable declarations
BEGIN
    -- business logic
    -- you may use COMMIT | ROLLBACK;
END;
$$;

-- Ecommerce example: insert an order and items
CREATE OR REPLACE PROCEDURE create_order (
    IN p_customer_id INT,
    IN p_items JSONB,
    OUT p_order_id INT
) LANGUAGE plpgsql AS $$
DECLARE
    l_total NUMERIC := 0;
BEGIN
    INSERT INTO orders (customer_id, order_date, total_amount)
    VALUES (p_customer_id, NOW(), 0)
    RETURNING id INTO p_order_id;

    INSERT INTO orderitems (order_id, product_id, quantity)
    SELECT p_order_id, (item->>'product_id')::INT, (item->>'qty')::INT
    FROM jsonb_array_elements(p_items) AS item;

    SELECT SUM(p.price * oi.quantity)
    INTO l_total
    FROM orderitems oi
    JOIN products p ON p.id = oi.product_id
    WHERE oi.order_id = p_order_id;

    UPDATE orders SET total_amount = l_total WHERE id = p_order_id;
    COMMIT;
END;
$$;

Common Mistakes

Mixing COMMIT/ROLLBACK inside functions instead of procedures. Functions cannot manage transactions; move the logic to a procedure or remove explicit commits.
Granting table privileges instead of EXECUTE ON PROCEDURE. This exposes data; instead, only GRANT EXECUTE on the procedure and keep tables locked down.

Frequently Asked Questions (FAQs)

Do procedures return values?

They cannot return a scalar directly. Use OUT parameters, temporary tables, or query affected rows afterward.

Can I use procedures in triggers?

No. Triggers require functions. Wrap the procedure logic inside a trigger function if needed.

Are procedures replicated in logical replication?

The CALL statement is replicated, but side effects rely on target tables existing on the subscriber.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie