How to Create a Function in PostgreSQL
Posted By : Sanjay Saini | 10-Dec-2017
Hi Guys,
In this blog, I will be showing you how we will create a function In the Postgresql. For Creating User defined function in Postgresql we need to use CREATE FUNCTION statement. I will be going to create a function that adds two numbers and return addition result.
Basic Syntax of Create FUNCTION
CREATE [OR REPLACE] FUNCTION functionName (args..)
RETURNS return_datatype AS $variable_name$
DECLARE
declaration;
[...]
BEGIN
< function_body >
[...]
RETURN { variable_name | value }
END; LANGUAGE plpgsql;
1. CREATE [OR REPLACE] FUNCTION - It use for Create a new Or Modify Existing Function.
2.functionName - It is uses for specify the Function name that we are going to add.
3.Args.. - comma-seprated list of arguments.
3.RETURN - The function must contain a return statement that will return a value.
4. BEGIN - END -- Function Logic must be inside this Block.
4. Language specified name of the functional language. In the case of
I have going to Create a simple function that uses for adding two numbers and return addition result.This function will accept two arguments of Integer.
Adding two Number Function
CREATE OR REPLACE FUNCTION add (first integer,second integer)
RETURNS integer AS $total$
DECLARE
total integer;
BEGIN
total = first + second;
RETURN total;
END;
$total$ LANGUAGE plpgsql;
When above query will execute from command line then result would be like below :
Call this function from the Psql Client like below --
Select add(10,30);
Thanks
Sanjay Saini
Cookies are important to the proper functioning of a site. To improve your experience, we use cookies to remember log-in details and provide secure log-in, collect statistics to optimize site functionality, and deliver content tailored to your interests. Click Agree and Proceed to accept cookies and go directly to the site or click on View Cookie Settings to see detailed descriptions of the types of cookies and choose whether to accept certain cookies while on the site.
About Author
Sanjay Saini
Sanjay has been working on web application development using frameworks like Java, groovy and grails. He loves listening to music , playing games and going out with friends in free time.