How to Create a Function in PostgreSQL: Syntax and Examples

In PostgreSQL, you create a function using the CREATE FUNCTION statement followed by the function name, parameters, return type, and function body. The function body is written in a supported language like plpgsql and defines the logic the function performs.

Syntax

The basic syntax to create a function in PostgreSQL includes the function name, input parameters, return type, language, and the function body enclosed in $$. The AS keyword introduces the function code.

• CREATE FUNCTION: starts the function definition.
• function_name(params): name and parameters with types.
• RETURNS: specifies the return data type.
• LANGUAGE: the programming language used (commonly plpgsql).
• AS $$ ... $$: the function body code.
• BEGIN ... END;: block containing the function logic.

sql

CREATE FUNCTION function_name(parameter_name parameter_type) RETURNS return_type AS $$
BEGIN
    -- function logic here
    RETURN some_value;
END;
$$ LANGUAGE plpgsql;

Example

This example creates a simple function named add_numbers that takes two integers and returns their sum.

sql

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

-- To use the function:
SELECT add_numbers(5, 3);

Output

 add_numbers 
-------------
           8
(1 row)

Common Pitfalls

Common mistakes when creating functions in PostgreSQL include:

• Forgetting to specify the LANGUAGE clause, which causes errors.
• Not using RETURN statement when the function expects a return value.
• Incorrect parameter or return types mismatch.
• Missing semicolon ; inside the function body.

Always test your function after creation to ensure it behaves as expected.

sql

/* Wrong: Missing LANGUAGE clause */
CREATE FUNCTION wrong_func() RETURNS integer AS $$
BEGIN
    RETURN 1;
END;
$$;

/* Right: Include LANGUAGE clause */
CREATE FUNCTION correct_func() RETURNS integer AS $$
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Quick Reference

Clause	Description
CREATE FUNCTION	Starts the function definition
function_name(params)	Name and input parameters with types
RETURNS	Specifies the return data type
LANGUAGE	Programming language used (e.g., plpgsql)
AS $$ ... $$	Function body code enclosed in dollar quotes
BEGIN ... END;	Block containing the function logic
RETURN	Returns a value from the function

Key Takeaways

Use CREATE FUNCTION with parameters, return type, and language to define a function.

Always include the LANGUAGE clause, commonly plpgsql, to avoid errors.

The function body must have a RETURN statement if a return type is specified.

Test your function after creation to ensure it works as expected.

Use dollar quotes $$ to enclose the function body for easier syntax handling.