Offensive Security Researcher & Quantum Cryptography Analyst

How to Use the CREATE FUNCTION Command in PostgreSQL

Abdul Wahab Junaid
How to Use the CREATE FUNCTION Command in PostgreSQL

One of the things that makes PostgreSQL feel more like a full application platform than just a data store is its support for user-defined functions. With CREATE FUNCTION, you can encapsulate logic directly in the database, whether that’s a simple calculation, a complex data transformation, or business rules that need to run consistently no matter which application or tool is touching the data. In this guide, I’ll walk through CREATE FUNCTION in detail, covering syntax, language options, parameter handling, practical examples, and best practices.

What Is a Function in PostgreSQL?

A function is a named, reusable block of code that accepts input parameters, performs some logic, and returns a result. PostgreSQL supports functions written in several languages, including plain SQL, PL/pgSQL (PostgreSQL’s procedural extension of SQL), and even languages like Python or Perl if the relevant procedural language extensions are installed. Functions can be called directly, used inside queries, or attached to triggers.

Basic Syntax of CREATE FUNCTION

CREATE [OR REPLACE] FUNCTION function_name (parameter_name parameter_type [, ...])
RETURNS return_type
LANGUAGE language_name
AS $$
    function_body
$$;

Let’s break this down:

A Simple SQL Function

Let’s start with the simplest kind of function, written directly in SQL:

CREATE FUNCTION add_numbers(a integer, b integer)
RETURNS integer
LANGUAGE sql
AS $$
    SELECT a + b;
$$;

You can call it just like any built-in function:

SELECT add_numbers(5, 3);

This returns 8. SQL functions are great for simple, single-expression logic, and PostgreSQL can sometimes inline them into queries for better performance, since they’re just SQL under the hood.

Writing a PL/pgSQL Function

For anything involving conditional logic, loops, or variables, you’ll want PL/pgSQL, PostgreSQL’s built-in procedural language:

CREATE OR REPLACE FUNCTION calculate_discount(price numeric, discount_percent numeric)
RETURNS numeric
LANGUAGE plpgsql
AS $$
BEGIN
    IF discount_percent < 0 OR discount_percent > 100 THEN
        RAISE EXCEPTION 'Discount percent must be between 0 and 100';
    END IF;
    RETURN price - (price * discount_percent / 100);
END;
$$;

Call it like this:

SELECT calculate_discount(200, 15);

This returns 170, since 15% of 200 is 30, and 200 minus 30 is 170.

Function Parameters in Detail

Default Parameter Values

You can give parameters default values, which makes them optional when calling the function:

CREATE OR REPLACE FUNCTION calculate_discount(price numeric, discount_percent numeric DEFAULT 10)
RETURNS numeric
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN price - (price * discount_percent / 100);
END;
$$;

Now you can call it with or without the second argument:

SELECT calculate_discount(200);        -- uses default of 10%
SELECT calculate_discount(200, 25);    -- uses explicit 25%

Named Parameters

You can also call functions using named parameter notation, which is especially helpful when a function has many parameters and you want to skip some that have defaults:

SELECT calculate_discount(price := 200, discount_percent := 15);

OUT Parameters

Instead of a single RETURNS type, you can define OUT parameters to return multiple named values:

CREATE OR REPLACE FUNCTION get_order_summary(order_id integer, OUT total numeric, OUT item_count integer)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT SUM(price * quantity), COUNT(*) INTO total, item_count
    FROM order_items
    WHERE order_items.order_id = get_order_summary.order_id;
END;
$$;

Calling this returns a row-like result:

SELECT * FROM get_order_summary(101);

Returning Different Types of Results

Returning a Scalar Value

This is the most common case, shown in the examples above, where the function returns a single value like an integer or numeric.

Returning a Table

Sometimes you want a function to return multiple rows, like a mini query. Use RETURNS TABLE:

CREATE OR REPLACE FUNCTION get_top_customers(limit_count integer)
RETURNS TABLE(customer_id integer, customer_name text, total_spent numeric)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT c.id, c.name, SUM(o.total_amount)
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    GROUP BY c.id, c.name
    ORDER BY SUM(o.total_amount) DESC
    LIMIT limit_count;
END;
$$;

You can then query it like a table:

SELECT * FROM get_top_customers(10);

Returning SETOF a Type

Alternatively, you can return a set of a previously defined composite type or an existing table’s row type:

CREATE OR REPLACE FUNCTION get_active_products()
RETURNS SETOF products
LANGUAGE sql
AS $$
    SELECT * FROM products WHERE is_active = true;
$$;

Using CREATE OR REPLACE FUNCTION

If you want to update a function’s logic without dropping it first (which would also require recreating grants), use CREATE OR REPLACE FUNCTION:

CREATE OR REPLACE FUNCTION calculate_discount(price numeric, discount_percent numeric DEFAULT 10)
RETURNS numeric
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN GREATEST(price - (price * discount_percent / 100), 0);
END;
$$;

Keep in mind: you cannot change the parameter types or return type with CREATE OR REPLACE FUNCTION. If you need to do that, you must explicitly DROP FUNCTION first, then create the new version.

Function Volatility: IMMUTABLE, STABLE, VOLATILE

PostgreSQL lets you tell the query planner how “predictable” your function is, which can have a real impact on performance:

CREATE OR REPLACE FUNCTION calculate_tax(amount numeric)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT amount * 0.08;
$$;

Marking a function as IMMUTABLE when appropriate allows PostgreSQL to cache and optimize its usage, which matters especially if it’s used in indexes or called repeatedly within a query.

Using SECURITY DEFINER

By default, functions run with the privileges of the calling user (SECURITY INVOKER). Sometimes, you want a function to run with the privileges of the function’s owner instead, regardless of who calls it. That’s what SECURITY DEFINER is for:

CREATE OR REPLACE FUNCTION get_sensitive_report()
RETURNS TABLE(department text, total_salary numeric)
LANGUAGE sql
SECURITY DEFINER
AS $$
    SELECT department, SUM(salary) FROM employees GROUP BY department;
$$;

This lets a role without direct access to the employees table still call this function and get aggregated results, because the function executes with the owner’s privileges. Use this carefully, since it effectively grants elevated access through the function, and misuse can create security holes.

Common Use Cases for CREATE FUNCTION

  1. Encapsulating business logic: keeping calculations like discounts, taxes, or scoring formulas consistent across every place they’re used.
  2. Powering trigger logic: nearly every trigger relies on a function to define its behavior.
  3. Simplifying complex queries: wrapping a complicated multi-table query in a function that returns a clean table result.
  4. Data validation: writing reusable validation functions that can be called from CHECK constraints or application code.
  5. Controlled access to sensitive data: using SECURITY DEFINER functions to expose limited, aggregated views of sensitive data without granting direct table access.

Troubleshooting Common Issues

“Function Does Not Exist” When Calling

Double-check the argument types you’re passing match what the function expects. PostgreSQL functions are strongly typed, and passing a text value where an integer is expected (without an implicit cast available) will fail.

“Function Is Not Unique” Errors

This happens with overloaded functions when PostgreSQL can’t determine which version to call based on the argument types you provided. Be explicit about types, using casts if necessary, like calculate_discount(200::numeric, 15::numeric).

Function Runs But Doesn’t Return Expected Rows

If you’re using RETURNS TABLE or SETOF, make sure you’re using RETURN QUERY correctly inside the function body, and that your SELECT statement is actually returning what you expect when run standalone.

Performance Issues with Row-by-Row Function Calls

Calling a PL/pgSQL function for every row in a large query can be slow, since PL/pgSQL functions aren’t always inlined by the planner. For performance-critical bulk operations, consider whether the logic can be rewritten as pure SQL or restructured to avoid per-row function calls.

Best Practices for Using CREATE FUNCTION

Exception Handling Inside Functions

PL/pgSQL supports structured exception handling, which lets your functions gracefully deal with errors rather than letting them bubble up and abort the entire transaction unexpectedly:

CREATE OR REPLACE FUNCTION safe_divide(numerator numeric, denominator numeric)
RETURNS numeric
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN numerator / denominator;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Division by zero attempted, returning NULL';
        RETURN NULL;
END;
$$;

This pattern is especially useful in functions that process batches of data, where you might want to catch and log a problem with one record without aborting the entire batch operation.

Functions with Variadic Arguments

If you want a function to accept a flexible number of arguments, PostgreSQL supports variadic parameters:

CREATE OR REPLACE FUNCTION sum_all(VARIADIC numbers numeric[])
RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
    total numeric := 0;
    n numeric;
BEGIN
    FOREACH n IN ARRAY numbers LOOP
        total := total + n;
    END LOOP;
    RETURN total;
END;
$$;

You can then call it with any number of arguments:

SELECT sum_all(1, 2, 3, 4, 5);

Using Polymorphic Types

Sometimes you want a function to work generically across different data types without writing a separate overload for each one. PostgreSQL supports polymorphic types like anyelement for this:

CREATE OR REPLACE FUNCTION first_non_null(a anyelement, b anyelement)
RETURNS anyelement
LANGUAGE plpgsql
AS $$
BEGIN
    IF a IS NOT NULL THEN
        RETURN a;
    ELSE
        RETURN b;
    END IF;
END;
$$;

This function will work whether you pass it two integers, two text values, or two timestamps, since PostgreSQL resolves the actual type at call time based on the arguments provided.

Writing Set-Returning Functions with Generators

Beyond RETURNS TABLE, you can write functions that generate rows procedurally using RETURN NEXT inside a loop:

CREATE OR REPLACE FUNCTION generate_date_series(start_date date, end_date date)
RETURNS SETOF date
LANGUAGE plpgsql
AS $$
DECLARE
    current_date_cursor date := start_date;
BEGIN
    WHILE current_date_cursor <= end_date LOOP
        RETURN NEXT current_date_cursor;
        current_date_cursor := current_date_cursor + 1;
    END LOOP;
    RETURN;
END;
$$;

While PostgreSQL has a built-in generate_series() function that handles this specific case more efficiently, this pattern is useful for understanding how to build custom row-generating functions for more complex, non-linear generation logic.

Frequently Asked Questions

What’s the difference between a function and a stored procedure in PostgreSQL?

Functions always return a value (even if it’s just void) and can be called within SELECT statements. Procedures, created with CREATE PROCEDURE and invoked with CALL, don’t need to return a value and can manage their own transaction control (COMMIT and ROLLBACK) internally, which functions cannot do.

Can a function call another function?

Yes, functions can freely call other functions, which is a common way to build up complex logic from smaller, reusable pieces.

How do I debug a PL/pgSQL function?

The RAISE NOTICE statement is the simplest debugging tool, letting you print variable values and execution checkpoints to the client during development. For more complex debugging needs, some IDEs and tools support step-through debugging via extensions like pldbgapi.

Is there a performance cost to using PL/pgSQL over plain SQL functions?

Generally yes, for simple logic. SQL functions can sometimes be inlined directly into the calling query by the planner, while PL/pgSQL functions always involve a bit more overhead due to their procedural execution model. For simple one-liner logic, prefer SQL language functions when possible.

Can functions have side effects like inserting or updating data?

Yes, especially functions marked VOLATILE (the default). This is common for trigger functions and utility functions that need to write audit records or update related tables as part of their operation.

Can I overload a function based only on the return type?

No, PostgreSQL determines which overload to call based on the parameter types passed in a function call, not the return type. Two functions with identical parameter lists but different return types are not valid overloads and will raise an error when you try to create the second one.

Is it possible to write a function in a language other than SQL or PL/pgSQL?

Yes, PostgreSQL supports additional procedural languages through extensions, including PL/Python, PL/Perl, and PL/Tcl, among others. These need to be installed as extensions first with CREATE EXTENSION plpython3u; (or the relevant language extension) before you can write functions using LANGUAGE plpython3u in your CREATE FUNCTION statement.

Can a function have an empty body that does nothing?

Yes, though it’s unusual outside of stub implementations during development. A function returning void with an empty body is syntactically valid and sometimes used as a placeholder while planning out a larger piece of functionality, to be filled in later with CREATE OR REPLACE FUNCTION.

How do I document what a function’s parameters mean?

Beyond descriptive parameter names, you can attach a comment to the function itself with COMMENT ON FUNCTION function_name(arg_types) IS 'description here';, which is visible through \df+ in psql and useful for anyone exploring the schema later without access to your original design notes.

Can I restrict who is allowed to call a function I’ve created?

Yes, functions follow the same GRANT and REVOKE model as other database objects. By default, PostgreSQL grants EXECUTE on new functions to PUBLIC, meaning everyone can call it. If you want to restrict access, revoke that default and grant EXECUTE only to specific roles: REVOKE EXECUTE ON FUNCTION calculate_discount(numeric, numeric) FROM PUBLIC; followed by GRANT EXECUTE ON FUNCTION calculate_discount(numeric, numeric) TO app_role;.

Wrapping Up

CREATE FUNCTION is one of the most powerful tools in PostgreSQL for pushing logic closer to your data, ensuring consistency no matter what application or tool is interacting with your database. Start with simple SQL functions for straightforward calculations, move to PL/pgSQL when you need more control, and always be thoughtful about volatility settings and security implications like SECURITY DEFINER. With a solid grasp of CREATE FUNCTION, you’ll be able to build a database layer that’s both powerful and maintainable.

Exit mobile version