PostgreSQL Functions Explained with PL/pgSQL Examples

PostgreSQL allows you to write functions to place logic directly inside the database.
Functions help you reuse logic, simplify queries, and improve performance.

In this article, you will learn:

• What functions are in PostgreSQL
• Why functions are useful
• How to create functions using PL/pgSQL
• How to call functions
• Types of PostgreSQL functions

This guide is written for beginners.

---

What Is a Function in PostgreSQL?

A function is a reusable block of code that:

• Accepts input parameters
• Performs some logic
• Returns a value

Functions are stored inside the database and can be called from SQL queries.

---

Why Use Functions?

PostgreSQL functions help you:

• Reuse code
• Reduce complex SQL queries
• Apply business logic in the database
• Improve performance
• Keep application code clean

Functions are very powerful for database programming.

---

What Is PL/pgSQL?

PL/pgSQL is PostgreSQL’s procedural programming language.

Using PL/pgSQL, you can:

• Use variables
• Use conditions (IF, ELSE)
• Use loops
• Handle exceptions

PL/pgSQL is the most common language used for PostgreSQL functions.

---

Basic Function Syntax

CREATE OR REPLACE FUNCTION function_name(parameters)

RETURNS return_type AS $

BEGIN

-- function logic

RETURN value;

END;

$ LANGUAGE plpgsql;

---

Creating Your First Function

Example: A function that adds two numbers.

CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)

RETURNS INTEGER AS $

BEGIN

RETURN a + b;

END;

$ LANGUAGE plpgsql;

---

Calling a Function

SELECT add_numbers(10, 20);

Output: 30

---

Function That Returns Text

CREATE OR REPLACE FUNCTION greet_user(username TEXT)

RETURNS TEXT AS $

BEGIN

RETURN 'Hello, ' || username;

END;

$ LANGUAGE plpgsql;

Call the function:

SELECT greet_user('Ravi');

Output:

Hello, Ravi

---

Function Using Variables

CREATE OR REPLACE FUNCTION calculate_tax(amount NUMERIC)

RETURNS NUMERIC AS $

DECLARE

tax NUMERIC;

BEGIN

tax := amount * 0.18;

RETURN tax;

END;

$ LANGUAGE plpgsql;

---

Function with IF Condition

CREATE OR REPLACE FUNCTION check_age(age INTEGER)

RETURNS TEXT AS $

BEGIN

IF age >= 18 THEN

RETURN 'Adult';

ELSE

RETURN 'Minor';

END IF;

END;

$ LANGUAGE plpgsql;

Call:

SELECT check_age(20);

---

Functions with Table Data

Example table:

CREATE TABLE employees (

id SERIAL PRIMARY KEY,

name TEXT,

salary NUMERIC

);

---

Function Using SELECT Query

CREATE OR REPLACE FUNCTION get_salary(emp_id INTEGER)

RETURNS NUMERIC AS $

DECLARE

emp_salary NUMERIC;

BEGIN

SELECT salary INTO emp_salary

FROM employees

WHERE id = emp_id;

RETURN emp_salary;

END;

$ LANGUAGE plpgsql;

---

Types of PostgreSQL Functions

Scalar Functions

• Return a single value
• Example: number, text, date

Set‑Returning Functions

• Return multiple rows
• Often used like tables

---

Dropping a Function

DROP FUNCTION function_name;

Example:

DROP FUNCTION add_numbers;

---

Function Best Practices

• Keep functions small and focused
• Use meaningful function names
• Avoid heavy logic when possible
• Use comments for clarity
• Test functions before production use

Functions should simplify your code, not complicate it.

---

Summary

• Functions encapsulate reusable logic
• Functions are written using PL/pgSQL
• Functions can accept parameters
• Functions can return values
• Functions improve code reusability and performance

You now understand PostgreSQL functions clearly.

---

What’s Next?

Next, we will learn about procedures and how they differ from functions.

Next article:
PostgreSQL Procedures Explained (Functions vs Procedures)