PostgreSQL – User Defined Functions
PostgreSQL uses the CREATE FUNCTION statement to develop user-defined functions.
Syntax: CREATE FUNCTION function_name(p1 type, p2 type) RETURNS type AS BEGIN -- logic END; LANGUAGE language_name;
Let’s analyze the above syntax:
- First, specify the name of the function after the CREATE FUNCTION keywords.
- Then, put a comma-separated list of parameters inside the parentheses following the function name.
- Next, specify the return type of the function after the RETURNS keyword.
- After that, place the code inside the BEGIN and END block. The function always ends with a semicolon (;) followed by the END keyword.
- Finally, indicate the procedural language of the function e.g., plpgsql in case PL/pgSQL is used.
In this example, we will develop a very simple function named inc that increases an integer by 1 and returns the result.
First, launch pgAdmin and connect to the dvdrental sample database.
Second, enter the following commands to create the inc function.
CREATE FUNCTION inc(val integer) RETURNS integer AS $$ BEGIN RETURN val + 1; END; $$ LANGUAGE PLPGSQL;
Third, click the Execute button to create the function:
The entire function definition that you provide to the CREATE FUNCTION must be a single quoted string. It means that if the function has any single quote (‘), you have to escape it.
Fortunately, from version 8.0, PostgreSQL provides a feature called dollar quoting that allows you to choose a suitable string that does not appear in the function so that you don’t have to escape it. A dollar quote is a string of characters between $ characters.
If the function is valid, PostgreSQL will create the function and return the CREATE FUNCTION statement as shown above.
Let’s test the inc function.
You can call the inc function like any built-in functions as follows:
It worked as expected.
Creating a function using pgAdmin:
n PostgreSQL, functions that have different parameters can share the same name. This is called function overloading, which is similar to function overloading in C++ or Java.
We can create a new function named inc that accepts two arguments. In the function, we will increase the value of the first argument by the second argument.
The following steps show you how to create a function from the pgAdmin.
First, launch pgAdmin and connect to the dvdrental database.
Second, right-click on the Functions and select Create > Function… menu item. A new window will display.
Third, enter inc in the name of the function:
Fourth, in the Definition tab, click the + button to add two arguments i and val with bigint as the data type.
Fifth in the same tab change language to plpgsql:
Now it time to define the function and to do so shift to Code tab and define it as shown below:
Sixth, click the SQL tab to see the generated code and click the Save button to create the function:
Seventh, there are more options in the like Security and Options. However, we just need the basic options for now. The function may not display in the function list. To see the new inc function, right-click the Functions and click Refresh… menu item:
Here is the new inc function:
Now let’s test the function with the below query:
SELECT inc(10, 20);