PostgreSQL Procedures Explained (Functions vs Procedures)

PostgreSQL supports procedures for executing logic inside the database.
Procedures are similar to functions but are designed for performing actions rather than returning values.

In this article, you will learn:

• What procedures are in PostgreSQL
• Why procedures are used
• How to create and call procedures
• Difference between functions and procedures
• When to use procedures

This guide is written for beginners.

---

What Is a Procedure in PostgreSQL?

A procedure is a stored block of code that:

• Performs database operations
• Can execute multiple SQL statements
• Does not return a value
• Can manage transactions

Procedures are mainly used for actions, not calculations.

---

Why Use Procedures?

PostgreSQL procedures are useful for:

• Complex business logic
• Bulk data processing
• Transaction control (COMMIT / ROLLBACK)
• Administrative and maintenance tasks
• Reducing application‑side logic

Procedures keep logic close to the data.

---

Functions vs Procedures (Important Difference)

Feature	Function	Procedure
Returns value	Yes	No
Can be used in SELECT	Yes	No
Transaction control	No	Yes
Called using	SELECT	CALL

---

Basic Procedure Syntax

SQL

CREATE OR REPLACE PROCEDURE procedure_name(parameters)

LANGUAGE plpgsql

AS $

BEGIN

-- procedure logic

END;

$;

Show more lines

---

Creating Your First Procedure

Example: Insert a user into a table.

Example Table

SQL

CREATE TABLE users (

id SERIAL PRIMARY KEY,

name TEXT,

email TEXT

);

Show more lines

---

Create Procedure

SQL

CREATE OR REPLACE PROCEDURE add_user(

user_name TEXT,

user_email TEXT

)

LANGUAGE plpgsql

AS $

BEGIN

INSERT INTO users(name, email)

VALUES (user_name, user_email);

END;

$;

Show more lines

---

Calling a Procedure

Procedures are called using CALL.

SQL

CALL add_user('Ravi', 'ravi@example.com');

Show more lines

The data is inserted into the users table.

---

Procedure with Transaction Control

One major advantage of procedures is transaction handling.

SQL

CREATE OR REPLACE PROCEDURE transfer_amount(

from_id INT,

to_id INT,

amount NUMERIC

)

LANGUAGE plpgsql

AS $

BEGIN

UPDATE accounts

SET balance = balance - amount

WHERE id = from_id;

 

UPDATE accounts

SET balance = balance + amount

WHERE id = to_id;

 

COMMIT;

END;

$;

Show more lines

Functions cannot use COMMIT or ROLLBACK, but procedures can.

---

Procedure with IF Condition

SQL

CREATE OR REPLACE PROCEDURE check_and_insert(

username TEXT

)

LANGUAGE plpgsql

AS $

BEGIN

IF username IS NOT NULL THEN

INSERT INTO users(name)

VALUES (username);

END IF;

END;

$;

Show more lines

---

Procedure with Exception Handling

SQL

CREATE OR REPLACE PROCEDURE safe_insert(

user_name TEXT

)

LANGUAGE plpgsql

AS $

BEGIN

INSERT INTO users(name)

VALUES (user_name);

 

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

END;

$;

Show more lines

This prevents partial or broken data insertion.

---

Dropping a Procedure

SQL

DROP PROCEDURE procedure_name;

Show more lines

Example:

SQL

DROP PROCEDURE add_user;

Show more lines

---

When to Use Procedures

Use procedures when:

• You don’t need to return a value
• You need transaction control
• You perform multiple database actions
• You handle batch processing
• You run administrative tasks

Use functions when:

• You need a return value
• You want to use the result in queries

---

Best Practices for Procedures

• Use procedures for logic with side effects
• Keep procedures focused and readable
• Handle exceptions properly
• Use meaningful procedure names
• Avoid heavy business logic when possible

Well‑designed procedures improve reliability.

---

Summary

• Procedures perform actions inside PostgreSQL
• Procedures do not return values
• Procedures support transaction control
• CALL is used to execute procedures
• Procedures are ideal for complex workflows

You now understand PostgreSQL procedures clearly.

---

What’s Next?

Next, we will learn about triggers in PostgreSQL.

Next article:
PostgreSQL Triggers Explained with Real‑World Examples