PostgreSQL provides support for user defined functions that allow you to extend the functionality of the database server. User defined functions are important when you need to execute the same SQL statements multiple times. By encapsulating the logic in a function, you can simply call the function whenever you need to execute the SQL statements. This improves code reusability, organization and efficiency.

In this comprehensive guide, you will learn:

  • What are PostgreSQL user defined functions and why are they used
  • Syntax for creating PostgreSQL functions
  • Different types of arguments supported in PostgreSQL functions
  • Examples of PostgreSQL functions using IN, OUT, INOUT arguments
  • Using control flow logic, variables, exceptions in functions
  • Calling aggregate and window functions from PostgreSQL UDFs

What are PostgreSQL User Defined Functions

PostgreSQL user defined functions (aka UDFs) are custom functions that can be created by developers to extend the database‘s base functionality.

Some reasons you may want to use PostgreSQL UDFs:

  • Encapsulate common database logic in reusable routines – Rather than writing the same SQL queries over and over in different database clients, you can create a function to abstract that logic away.

  • Improve organization of database code – Functions allow you to modularize tasks rather than having monolithic SQL scripts doing many things.

  • Secure access to data – By moving logic into functions rather than exposing base tables, you can add an access control layer.

  • Easy parallelization – Functions help break down tasks into isolated units of work that can leverage parallel querying.

  • Optimize performance – Functions are pre-parsed and cached by the PostgreSQL query planner, allowing faster execution.

Overall, functions help organize your SQL, reduce duplicate logic, improve security and offer performance benefits.

Syntax for Creating PostgreSQL Functions

The syntax for creating a function in PostgreSQL is:

CREATE [OR REPLACE] FUNCTION function_name
    ( [argument_list] ) 
RETURNS return_type
AS 
$function_body$
DECLARE
    declarations
BEGIN
    function_body
    RETURN {value | variable}
END;
$function_body$ 
LANGUAGE language_name;

Let‘s break down what each part of the PostgreSQL function syntax means:

  • CREATE FUNCTION – Creates a new function.
  • OR REPLACE – Optionally replaces existing function (to update).
  • function_name – Specifies name for the function.
  • argument_list – Parameters that can be passed into the function (optional).
  • RETURNS return_type – Data type of value returned by the function.
  • $function_body$ – Start and end delimiter for the function logic.
  • DECLARE – Used for declaring variables (optional).
  • BEGIN...END – Block with the main function logic.
  • RETURN – Returns value from function.
  • LANGUAGE language_name – Implementation language name (usually plpgsql).

Some key points:

  • Function names must be unique in a database.
  • Functions can optionally take in parameters and return a value.
  • Variable declarations and business logic sits between the BEGIN and END blocks.
  • The return keyword is used to return a value from the function.

Now let‘s look at examples of PostgreSQL functions using different parameter types.

IN, OUT and INOUT Parameter Types

PostgreSQL functions can take different parameter types:

  • IN – Read-only input parameter (default)
  • OUT – Write-only output parameter
  • INOUT – Read-write parameter

The parameter type changes the behavior of how values are passed into and out of PostgreSQL functions.

IN Parameter Functions

IN parameters allow passing input values into a PostgreSQL function. Any changes made to IN parameters within the function are not persisted after the function exits.

Here is an example of a function with two IN parameters that inserts a new product into a products table:

CREATE FUNCTION add_product(name text, price numeric) 
RETURNS void
AS $$
BEGIN
  INSERT INTO products (product_name, unit_price)
  VALUES (name, price); 
END;
$$ LANGUAGE plpgsql;

To call this function:

SELECT add_product(‘Television‘, 499.99);

The IN parameters name and price are used inside the function to insert a new record.

OUT Parameter Functions

OUT parameters serve as output parameters that return values from a PostgreSQL function. They allow a function to return multiple values of potentially different data types.

Here is an example using an OUT parameter price to return the expensive product price:

CREATE FUNCTION get_max_price(OUT price numeric)  
AS $$
BEGIN
  SELECT MAX(unit_price) INTO price FROM products;
END; 
$$ LANGUAGE plpgsql;

To call this function:

SELECT get_max_price();

This queries the products table and returns the highest price value found.

INOUT Parameter Functions

INOUT parameters allow passing input values into a function as well as returning updated values out of the function. Any changes made to the INOUT parameter within the function is returned.

Here is an example that calculates the discounted price for a product:

CREATE FUNCTION apply_discount(percent numeric, INOUT price numeric) 
AS $$
BEGIN
  price := price * (1 - percent / 100);
END;
$$ LANGUAGE plpgsql;

To call this function:

SELECT apply_discount(10, 500);

The INOUT parameter price passes the value 500 in, which is discounted by 10% to 450 before returning out.

As you can see, IN, OUT and INOUT parameters provide flexibility in how values are passed between calling code and PostgreSQL functions.

Using Variables, Control Logic and Exceptions

Beyond just containing SQL statements, PostgreSQL user defined functions can utilize programming constructs for added logic and control flow:

  • Local Variables – Declare variables to store intermediate values with DECLARE.
  • Conditional Logic – Use IF/ELSE statements and loops to implement complex logic.
  • Exceptions – Throw custom exceptions using RAISE to handle errors.

Let‘s look at some examples that showcase these constructs:

Local Variable Example

This function declares a local variable total_count to store the result of a COUNT aggregate function temporarily before returning the value:

CREATE FUNCTION count_products() 
RETURNS bigint
AS $$
DECLARE
  total_count bigint;
BEGIN
  SELECT COUNT(*) INTO total_count FROM products;

  RETURN total_count;  
END;
$$ LANGUAGE plpgsql;

Conditional Logic Example

This function uses an IF check to validate the discount percentage before applying it:

CREATE FUNCTION apply_discount(percent numeric, INOUT price numeric)
AS $$  
BEGIN
    IF percent > 100 OR percent < 0 THEN
        RAISE ‘Discount percent must be between 0 and 100‘;
    END IF;

    price := price - (price * (percent / 100));   
END;
$$ LANGUAGE plpgsql;

Exception Handling Example

This function throws a custom exception using RAISE if no products are found for a category: 

CREATE FUNCTION get_category_products(category text)
RETURNS setof products /* Returns set of rows */
AS $$
DECLARE
    product record;    
BEGIN
  FOR product IN EXECUTE ‘SELECT * FROM products WHERE category = $1‘ USING category
  LOOP
      RETURN NEXT product; /* Return current row */
  END LOOP;

  IF NOT FOUND THEN
     RAISE ‘No products found for category %‘, category;
  END IF;

END;
$$ LANGUAGE plpgsql;

As you can see, PostgreSQL functions allow implementing application-style constructs like variables, loops, conditional logic and exception handling.

Next let‘s explore using aggregate and window functions within PostgreSQL UDFs.

Utilizing Aggregate & Window Functions in UDFs

One unique capability provided by PostgreSQL is the ability to leverage SQL aggregate and window functions within user defined functions.

For example, this function uses the COUNT aggregate to return the number of products for a given category:

CREATE FUNCTION product_count_by_category(category_id bigint)  
RETURNS bigint
AS $$
DECLARE 
    count bigint;
BEGIN
  SELECT COUNT(*) INTO count
  FROM products
  WHERE category_id = $1
  GROUP BY category_id;

  RETURN count;
END; 
$$ LANGUAGE plpgsql;

The window function ROW_NUMBER() is used here to return products with their sequential number within category:

CREATE FUNCTION get_ranked_products_by_cat(category_id bigint)
RETURNS setof products
AS $$
BEGIN
  RETURN QUERY 
  SELECT p.*, ROW_NUMBER() OVER(ORDER BY p.unit_price DESC) AS rank
  FROM products p
  WHERE p.category_id = category_id;    
END;
$$ LANGUAGE plpgsql;

This showcases the flexibility of PostgreSQL UDFs to leverage both SQL constructs like aggregate/window functions as well as programming constructs.

Conclusion

PostgreSQL user defined functions are extremely useful for modularizing database logic into reusable routines. They encourage separation of concerns rather than monolithic SQL scripts.

As we explored, some key advantages of using PostgreSQL UDFs are:

  • Encapsulation of business logic into single routine
  • Support for IN, OUT, INOUT parameters
  • Ability to use programming constructs like variables, loops, exceptions
  • Leveraging window functions and aggregate functions
  • Caching of execution plans for performance

The end result is simpler, more maintainable database code organized into modules that are easy to reuse anywhere functions are supported. User defined functions really showcase the unique depth and extensibility provided by PostgreSQL.

Related posts:

  1. Listing Users in PostgreSQL: A Comprehensive Guide
  2. How to Find the Length of Arrays in PostgreSQL: An Expert Guide
  3. Removing the NOT NULL Constraint in PostgreSQL
  4. Postgres Not Equal Operator: A Comprehensive Guide
  5. PostgreSQL Variables: A Comprehensive Guide
  6. Unlocking the Power of Rank() in PostgreSQL
  7. Supercharged PostgreSQL with Powerful Extensions
  8. Mastering PostgreSQL‘s Powerful XML Data Handling

Similar Posts