PostgreSQL CREATE PROCEDURE – IN/OUT Parameters, CALL, Transactions, and Errors | DbSchema

Table of Contents

• What PostgreSQL CREATE PROCEDURE does
• Prerequisites and version note
• PostgreSQL CREATE PROCEDURE syntax
• Procedure parameters explained
• Procedure vs function
• Creating a procedure in psql
• CALL and transaction behavior
• IN, OUT, and INOUT examples
• Error handling with EXCEPTION
• Common CREATE PROCEDURE mistakes
• Creating a procedure in DbSchema
• FAQ
• Conclusion
• References

PostgreSQL CREATE PROCEDURE is used to store action-oriented logic inside the database and invoke it with CALL. Procedures are helpful when you want to encapsulate multi-step operations, centralize business rules, and keep repetitive SQL close to the data.

This guide covers syntax, IN/OUT/INOUT parameters, CALL, transaction considerations, EXCEPTION handling, and how DbSchema helps when procedures are part of a larger PostgreSQL schema.

What PostgreSQL CREATE PROCEDURE does

A procedure is a named routine stored in the database server. It can accept parameters, execute SQL and PL/pgSQL statements, and coordinate multi-step workflows such as posting data, moving rows, or updating several tables in one call.

People often search for PostgreSQL stored procedure, but PostgreSQL distinguishes between procedures and functions. That difference matters because procedures are invoked with CALL, while functions are used in expressions such as SELECT, WHERE, or JOIN clauses.

Prerequisites and version note

Before you start:

• install PostgreSQL and connect with psql or a GUI
• make sure the procedural language you want to use is available; plpgsql is the default in standard installations
• confirm you are on PostgreSQL 11 or later, because CREATE PROCEDURE was introduced in PostgreSQL 11

If you are still exploring the database itself, the companion pages are List All Schemas in PostgreSQL, Show Tables in PostgreSQL, and Create Table in PostgreSQL.

PostgreSQL CREATE PROCEDURE syntax

The basic template looks like this:

CREATE OR REPLACE PROCEDURE procedure_name(
    [argmode] argument_name data_type
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- procedure body
END;
$$;

A small example:

CREATE OR REPLACE PROCEDURE update_salary(
    IN p_employee_id INT,
    IN p_increase_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees
    SET salary = salary + p_increase_amount
    WHERE id = p_employee_id;
END;
$$;

You execute it with:

CALL update_salary(1, 500);

Procedure parameters explained

When creating a PostgreSQL procedure, you can specify several parameters:

Parameter modes change how callers interact with the routine:

Procedure vs function

This distinction matters in PostgreSQL because both objects encapsulate logic, but they fit different workflows.

Creating a procedure in psql

A typical workflow in psql is:

1. connect to the database
2. define the procedure with CREATE OR REPLACE PROCEDURE
3. call it with CALL
4. verify the table changes with SELECT

Example:

CREATE OR REPLACE PROCEDURE archive_old_orders(
    IN p_cutoff_date DATE
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO archived_orders (order_id, customer_id, created_at, total_amount)
    SELECT order_id, customer_id, created_at, total_amount
    FROM orders
    WHERE created_at < p_cutoff_date;

    DELETE FROM orders
    WHERE created_at < p_cutoff_date;
END;
$$;

Then execute it:

CALL archive_old_orders(CURRENT_DATE - INTERVAL '180 days');

This kind of routine is useful when the application should trigger a business action but not repeat the SQL everywhere.

CALL and transaction behavior

CALL is the correct way to execute a PostgreSQL procedure:

CALL update_salary(1, 500);

A few practical rules help avoid confusion:

• use CALL, not SELECT, for procedures
• procedures are the PostgreSQL routine type that can participate in transaction-control workflows
• transaction control has restrictions: if CALL runs inside an already-open explicit transaction block, issuing COMMIT or ROLLBACK inside the procedure is not allowed
• keep the calling contract clear so the application knows whether the routine is expected to change data, return parameters, or both

That difference is one of the main reasons to choose a procedure over a function.

IN, OUT, and INOUT examples

You can use IN, OUT, and INOUT parameters to model richer procedure behavior:

CREATE OR REPLACE PROCEDURE get_order_totals(
    IN p_customer_id INT,
    INOUT p_order_count INT,
    INOUT p_total_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*), COALESCE(SUM(amount), 0)
    INTO p_order_count, p_total_amount
    FROM orders
    WHERE customer_id = p_customer_id;
END;
$$;

CALL get_order_totals(42, NULL, NULL);

This pattern is useful when one call needs to populate several related outputs.

Error handling with EXCEPTION

PostgreSQL procedures often wrap multi-step operations, so explicit error handling is important:

CREATE OR REPLACE PROCEDURE transfer_balance(
    IN p_from_account INT,
    IN p_to_account INT,
    IN p_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts
    SET balance = balance - p_amount
    WHERE id = p_from_account;

    UPDATE accounts
    SET balance = balance + p_amount
    WHERE id = p_to_account;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$;

Use RAISE to surface the original failure unless you have a clear reason to translate it into a custom error. In real systems, teams often replace WHEN OTHERS with narrower exceptions once the routine is stable.

Common CREATE PROCEDURE mistakes

Example grant:

GRANT EXECUTE ON PROCEDURE update_salary(INT, NUMERIC) TO app_user;

Creating a procedure in DbSchema

DbSchema is useful when procedures are part of a broader database model rather than isolated SQL snippets.

A common workflow looks like this:

1. connect through the PostgreSQL JDBC driver
2. reverse-engineer tables, views, keys, and existing routines into one project
3. create or edit the procedure from the schema tree
4. keep the routine near the related tables in the diagram editor
5. generate schema documentation so teammates can review the routine together with the data model
6. compare and deploy changes through DbSchema instead of losing track of routine versions in ad-hoc scripts

DbSchema is especially useful when routines live alongside a larger model, because you can keep procedures, tables, and relationships documented in one place. Pair it with List All Schemas in PostgreSQL, Show Tables in PostgreSQL, and PostgreSQL Triggers when the routine depends on a bigger workflow.

FAQ

How do I execute a PostgreSQL procedure?

Use the CALL statement, for example CALL update_salary(1, 500);.

What is the difference between CREATE FUNCTION and CREATE PROCEDURE in PostgreSQL?

Functions are meant to return values and participate in SQL expressions. Procedures are called with CALL and fit action-oriented workflows better, especially when transaction control is involved.

When should I use INOUT parameters?

Use INOUT parameters when the caller passes a value in and expects the same parameter to be updated and returned by the procedure.

Can a PostgreSQL procedure return rows like a query?

Not in the same way a set-returning function does. If your main goal is to return query results into SELECT, a function is usually the better fit.

Which PostgreSQL version added CREATE PROCEDURE?

PostgreSQL introduced CREATE PROCEDURE in version 11.

Conclusion

PostgreSQL procedures are best for command-style database logic: multi-step actions, parameter-driven workflows, and routines that are clearer with CALL than with SELECT. Once you understand parameter modes, transaction rules, and EXCEPTION handling, procedures become much easier to design safely.

DbSchema helps by keeping those procedures connected to the rest of the PostgreSQL schema, documentation, and deployment workflow instead of leaving them scattered across scripts.

References

1. PostgreSQL Documentation: CREATE PROCEDURE
2. PostgreSQL Documentation: PL/pgSQL errors and messages
3. DbSchema Documentation: Schema documentation
4. DbSchema: PostgreSQL JDBC driver