Introduction to Create Function Statement

Thecreate function statement allows you to define a new user-defined function.

Here’s the syntax of thecreate function statement:

create [or replace]function function_name(param_list)   returns return_type   language plpgsql  as$$declare   -- variable declarationbegin   -- logicend;$$;

In this syntax:

• First, specify the name of the function after thecreate function keywords. To replace the existing function, use theor replace option.
• Then, list out parameters surrounded by parentheses after the function name. A function can have zero or more parameters.
• Next, define the datatype of the returned value after thereturns keyword.
• After that, use thelanguage plpgsql to define the procedural language of the function. Note that PostgreSQL supports many languages includingplpgsql.
• Finally, place ablock in thedollar-quoted string constant to define the function body.

PostgreSQL Create Function statement examples

We’ll use thefilm table from thesample database.

The following statement creates a function that returns the number films whose length between thelen_from andlen_to parameters:

create function get_film_count(len_fromint, len_toint)returns intlanguage plpgsqlas$$declare   film_countinteger;begin   select count(*)   into film_count   from film   where length between len_fromand len_to;   return film_count;end;$$;

Output:

CREATEFUNCTION

The functionget_film_count has two main sections:

• header
• body

In the header section:

• First, the name of the function isget_film_count that follows thecreate function keywords.
• Second, theget_film_count() function accepts two parameterslen_from andlen_to with theinteger type.
• Third, theget_film_count function returns an integer specified by thereturns int clause.
• Finally, the language of the function isplpgsql indicated by thelanguage plpgsql.

In the function body:

• Use thedollar-quoted string constant syntax that starts with$$ and ends with$$. Between these$$, you can place ablock containing the declaration and logic of the function.
• In the declaration section, declare a variablefilm_count that stores the number of films from thefilm table.
• In the body of the block, use theselect into statement to select the number of films whose lengths are betweenlen_from andlen_to and assign it to thefilm_count variable. At the end of the block, use thereturn statement to return thefilm_count.

To execute the create function statement, you can use any PostgreSQL client tool including psql and pgAdmin

1) Creating a function using pgAdmin

First, launch the pgAdmin tool and connect to thesample database.

Second, open the query tool by selectingTools > Query Tool.

Third, enter the above code in the query tool and click theExecute button to create theget_film_count function.

If everything is fine, you will see the following message:

CREATE FUNCTIONQuery returned successfully in 44 msec.

It means that the functionget_film_count is created successfully.

Finally, view the functionget_film_count in theFunctions list:

If you cannot find the function name, right-click the Functions node and selectRefresh… menu item to refresh the function list.

2) Creating a function using psql

First, launch the psql interactive tool and connect to thedvdrental database:

psql -U postgresql -d dvdrental

Second, enter the above code in the psql to create the function. Note that you can copy & paste on Windows, macOS, and Ubuntu desktop.

You will see the following message if the function is created successfully:

CREATE FUNCTION

Third, use the\df command to list all user-defined in the current database:

dvdrental=# \df

Calling a user-defined function

PostgreSQL provides you with three ways to call a user-defined function:

• Using positional notation
• Using named notation
• Using the mixed notation.

1) Using positional notation

When invoking a function using the positional notation, you need to supply the arguments in the exact order as the parameters are defined within the function signature:

select get_film_count(40,90);

Output:

get_film_count----------------            325(1 row)

In this example, the arguments of theget_film_count() are40 and90 that corresponds to thefrom_len andto_len parameters.

You call a function using the positional notation when the function has few parameters.

If the function has many parameters, you should call it using the named notation because it will make the function call more obvious.

2) Using named notation

The following shows how to call theget_film_count function using the named notation:

select get_film_count(    len_from=> 40,     len_to=> 90);

Output:

get_film_count----------------            325(1 row)

In the named notation, you use the=> to separate the argument’s name and its value.

For backward compatibility, PostgreSQL supports the older syntax based on:= as follows:

select get_film_count(    len_from := 40,    len_to := 90);

3) Using mixed notation

The mixed notation is the combination of positional and named notations. For example:

select get_film_count(40, len_to=> 90);

Note that you cannot use the named arguments before positional arguments like this:

select get_film_count(len_from=> 40,90);

Error:

ERROR:  positional argument cannot follow named argumentLINE 1: select get_film_count(len_from => 40, 90);

Summary

• Use theCREATE FUNCTION statement to create a user-defined function.

Last updated onMarch 22, 2024

On this page

• Introduction to Create Function Statement
• PostgreSQL Create Function statement examples
• Calling a user-defined function
• Summary