Function creation syntax in PostgreSQL

Introduction

Functions let you save a set of instructions in the database to reuse later. This helps avoid repeating the same code.

You want to calculate a value many times without rewriting the calculation.

You need to organize complex logic inside the database for easier use.

You want to perform a task that involves multiple steps and reuse it.

You want to simplify queries by wrapping repeated code inside a function.

You want to improve performance by running code directly in the database.

Syntax

PostgreSQL

CREATE [OR REPLACE] FUNCTION function_name (parameters) 
RETURNS return_type AS $$
BEGIN
  -- function body
  RETURN value;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE lets you update the function if it exists.

The function body goes between BEGIN and END;.

Examples

This function adds two numbers and returns the sum.

PostgreSQL

CREATE FUNCTION add_two_numbers(a integer, b integer)
RETURNS integer AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

This function returns a greeting message with the given name.

PostgreSQL

CREATE OR REPLACE FUNCTION greet(name text)
RETURNS text AS $$
BEGIN
  RETURN 'Hello, ' || name || '!';
END;
$$ LANGUAGE plpgsql;

This function returns the current year as an integer.

PostgreSQL

CREATE FUNCTION get_current_year()
RETURNS integer AS $$
BEGIN
  RETURN EXTRACT(YEAR FROM CURRENT_DATE)::integer;
END;
$$ LANGUAGE plpgsql;

Sample Program

This creates a function that multiplies a number by three, then calls it with 5.

PostgreSQL

CREATE OR REPLACE FUNCTION multiply_by_three(num integer)
RETURNS integer AS $$
BEGIN
  RETURN num * 3;
END;
$$ LANGUAGE plpgsql;

SELECT multiply_by_three(5);

Important Notes

Always specify the language (usually plpgsql) at the end.

Use CREATE OR REPLACE to update functions without dropping them first.

Functions can take zero or more parameters and must specify a return type.

Summary

Functions store reusable code inside the database.

Use CREATE FUNCTION with parameters, return type, and body.

Call functions like normal queries to get results.