PostgreSQL CREATE FUNCTION
Introduction
Functions in PostgreSQL allow you to encapsulate reusable SQL code, making your database operations more modular, maintainable, and efficient. By defining your own functions, you can extend PostgreSQL's capabilities to suit your specific needs.
In this tutorial, we'll explore how to create custom functions in PostgreSQL, starting with basic syntax and progressing to more complex examples.
Basic Syntax
The basic syntax for creating a function in PostgreSQL is:
CREATE [OR REPLACE] FUNCTION function_name(parameter_list)
RETURNS return_type
LANGUAGE language_name
AS $$
-- function body
$$;
Let's break down the components:
CREATE [OR REPLACE] FUNCTION
: This statement creates a new function or replaces an existing one.function_name
: The name you give to your function.parameter_list
: Input parameters the function accepts (optional).RETURNS return_type
: The data type that the function will return.LANGUAGE language_name
: The language used to write the function body (commonly SQL, PL/pgSQL, or PL/Python).AS $$ ... $$
: The function body enclosed in dollar-quoted string literals.
Creating a Simple Function
Let's start with a simple example: a function that adds two numbers.
CREATE OR REPLACE FUNCTION add_numbers(a integer, b integer)
RETURNS integer
LANGUAGE SQL
AS $$
SELECT a + b;
$$;
Testing the Function
To call this function:
SELECT add_numbers(5, 3);
Output:
add_numbers
-------------
8
Function Languages
PostgreSQL supports several programming languages for writing functions:
- SQL: Simple, single-query functions.
- PL/pgSQL: PostgreSQL's procedural language, similar to Oracle's PL/SQL.
- PL/Python, PL/Perl, PL/Tcl: External programming languages (requires extension installation).
For most beginners, SQL and PL/pgSQL are the most accessible options.
PL/pgSQL Functions
PL/pgSQL extends the SQL language with control structures like loops and conditionals. It's especially useful for more complex functions.
Here's the basic structure of a PL/pgSQL function:
CREATE OR REPLACE FUNCTION function_name(parameters)
RETURNS return_type
LANGUAGE plpgsql
AS $$
DECLARE
-- variable declarations
BEGIN
-- function body
RETURN value;
END;
$$;
Example: Calculate Discount
CREATE OR REPLACE FUNCTION calculate_discount(price numeric, discount_percent numeric)
RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
discounted_price numeric;
BEGIN
discounted_price := price - (price * discount_percent / 100);
RETURN ROUND(discounted_price, 2);
END;
$$;
Testing the Function
SELECT calculate_discount(100, 15);
Output:
calculate_discount
-------------------
85.00
Function Parameters
PostgreSQL functions can have different types of parameters:
IN Parameters (Default)
Input parameters that provide values to the function.
CREATE OR REPLACE FUNCTION greet_user(IN user_name text)
RETURNS text
LANGUAGE plpgsql
AS $$
BEGIN
RETURN 'Hello, ' || user_name || '!';
END;
$$;
OUT Parameters
Output parameters that return values from the function.
CREATE OR REPLACE FUNCTION get_circle_metrics(IN radius numeric, OUT area numeric, OUT circumference numeric)
RETURNS record
LANGUAGE plpgsql
AS $$
BEGIN
area := PI() * radius * radius;
circumference := 2 * PI() * radius;
END;
$$;
Testing:
SELECT * FROM get_circle_metrics(5);
Output:
area | circumference
----------------+---------------
78.5398163397448 | 31.4159265358979
INOUT Parameters
Parameters that serve as both input and output.
CREATE OR REPLACE FUNCTION double_value(INOUT x numeric)
RETURNS numeric
LANGUAGE plpgsql
AS $$
BEGIN
x := x * 2;
END;
$$;
Testing:
SELECT double_value(10);
Output:
double_value
-------------
20