Overview

In this tutorial we will learn about PostgreSQL user defined functions. The topics covered would be what is a user defined function? How to create and use user defined functions via queries and PgAdmin GUI, their advantages and some examples.

What is a user defined function?

A user defined PostgreSQL function is a group of arbitrary SQL statements designated to perform some task. These functions do not come out of the box and are typically created to handle specific scenarios. It is possible to perform select, insert, update, delete operations within a function. A PostgreSQL function can be created in any language such as SQL, C, PL/pgSQL, Python etc.

One very important point to note about a  user defined function is they can't handle a transaction which means a COMMIT or ROLLBACK isn't possible.

How to create a user defined function?

A function can be created in two ways, either with the help of PostgreSQL code or via PgAdmin GUI. Let us explore both the options.

Basic Syntax

In this syntax, the function_name along with the number of arguments or parameter list is specified after the CREATE OR REPLACE clause. Next, the return_datatype is declared after the RETURNS keyword. The return_datatype is apparently the type of data, which is returned from the function. It could be one from any of the PostgreSQL datatypes such as character, integer, double etc. It is also possible to return a table from a PostgreSQL function.

In this syntax, the function_name along with the number of arguments or parameter list is specified after the CREATE OR REPLACE clause. Next, the return_datatype is declared after the RETURNS keyword. The return_datatype is apparently the type of data which is returned from the function. It could be one from any of the PostgreSQL datatypes such as character, integer, double etc. It is also possible to return a table from a PostgreSQL function.

Next, after the DECLARE keyword, the IN,OUT variables used in the function are declared. Next, within the BEGIN-END block the function_body is defined. The function_body apparently holds the business logic of the function. Next after the RETURN keyword the variable_name which holds the return value from the function is specified.

Finally, after LANGUAGE keyword the language(language_name) in which the function is created is specified.

CREATE [OR REPLACE] FUNCTION function_name (arguments) 
RETURNS return_datatype AS $variable_name$
   DECLARE
      declaration;
      [...]
   BEGIN
      < function_body >
      [.. logic]
      RETURN { variable_name | value }
   END; 
LANGUAGE language_name;

The queries can be run either in PostgreSQL shell(PSQL) or in PgAdmin query tool.

Example

Here we create a function that returns the current date and time from the server.

CREATE FUNCTION getTimestamp() RETURNS timestamp AS $$
BEGIN
RETURN CURRENT_TIMESTAMP;
END; $$
LANGUAGE PLPGSQL;

To list all functions within a database's schema:

df <schema>.*

Let us execute the above function:

PgAdmin GUI

Let us create the same function in the PgAdmin program.

Step 1-> Navigate to servers->Database->Schema->Function->Create->Function as shown below:

Step 2 ->Specify the name, owner, schema, comment if any.

Step 3->Provide the function arguments, return type and language as explained in the earlier section.

Step 4 -> Provide the business logic under the tab 'code'

Step 5 ->  Next, provide the parameters in the 'Parameters' tab and save to create the function.

User Defined Function Examples

Let us now go through some sample functions to better understand the concept.

Note: Either PgAdmins query tool or Psql terminal can be used to execute queries. In this tutorial we have used PgAdmin query tool to serve the purpose. Both these editors come bundled in the latest versions of PgAdmin.

Example 1: Add 2 numbers

Here one function, named 'addNumbers', got created. This function takes 2 integers parameters as input and returns one integer as output.

CREATE FUNCTION addNumbers(val1 integer, val2 integer) RETURNS integer AS $$
BEGIN
RETURN val1 + val2;
END; $$
LANGUAGE PLPGSQL;

Here is the function created in PgAdmin:

The function appears on the left side under Functions.

Let us now execute the function as shown below:

Example 2: In this example we will see how to perform conditional select operation within a function

This function is used to perform select operation on a table named 'Stocks'. The function returns all those stocks whose price is less than the input parameter 'price_cap'. The variable declared under the section declare holds the output value.

create function get_stocks(price_cap int)
returns int
language plpgsql
as
$$
declare
   stock_count integer;
begin
   select count(*) 
   into stock_count
   from public."Stocks"
   where stock_price < price_cap;
   
   return stock_count;
end;
$$;

Let's create this in PgAdmin.

Let us have a look at the table data:

Let us now execute the function to verify its behaviour:

As per our table, there are 3 stocks whose price is less than 1000 and the function retrieved the same.

Example 3: Returns a table.

A function can also be used to return a table if required. We will use the following table to demonstrate this functionality.

The following function returns all stocks whose price is less than the input price. The output would be in the form of a table.

CREATE OR REPLACE FUNCTION get_allStocks (price_cap int) 
    RETURNS TABLE (
        stock_serial_no int,
        stock varchar,
        price_of_stock bigint
) 
AS $$
BEGIN
    RETURN QUERY SELECT
        stock_id,
        stock_name,
        stock_price
    FROM
        public."Stocks"
    WHERE
        stock_price < price_cap;
END; $$ 
LANGUAGE 'plpgsql';

Let us now execute the function to verify the output. PostgreSQL returns a table with one column that holds the array of stocks.

User Defined Function Advantages

There are a number of reasons to use functions:

1. Easy to invoke
2. Can be used at multiples places without restrictions as compare to stored procedures.
3. Code can
4. Function overloading is allowed, functions that have different parameters can share the same name

Conclusion

In this article we learnt about the generic characteristics of a PostgreSQL user defined function and what are the different ways to create them along with some examples. We hope this article will help you get started on your PostgreSQL's user defined function journey.