PostgreSQL Functions

PostgreSQL Functions is a named block that accepts some input, performs task and returns a value. REATE FUNCTION defines a new function. CREATE OR REPLACE FUNCTION will either create a new function, or replace an existing definition.

Following steps will be performed in this tutorial.

1. PostgreSQL Functions Syntax.
2. PostgreSQL Function Examples.
3. PostgreSQL Function Calling in psql tool and execute in Linux shell.

PostgreSQL Functions Syntax:

CREATE [ OR REPLACE ] FUNCTION name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] ) [ RETURNS rettype | RETURNS TABLE ( column_name column_type [, ...] ) ] { LANGUAGE lang_name | WINDOW | IMMUTABLE | STABLE | VOLATILE | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | COST Calling_cost | ROWS result_rows | SET configuration_parameter { TO value | = value | FROM CURRENT } | AS 'definition' | AS 'obj_file', 'link_symbol' } ... [ WITH ( attribute [, ...] ) ]

PostgreSQL Function Examples:

Table used for below examples:

1. Create function to return employee name(ename), salary and hiredate by passing employee number.

CREATE OR REPLACE FUNCTION empinfo(i int) RETURNS TABLE( date_id numeric, date_desc varchar(100), sal numeric,date_value date) as $$ BEGIN RETURN QUERY (select a.empno, a.ename,a.sal,a.hiredate from emp a where a.empno=i); END; $$ LANGUAGE plpgsql;

PostgreSQL Function Calling:

select empinfo(7698);

Visual Representation of output:

PostgreSQL Function

2. Create a function to accept deptno and return employee details.

CREATE OR REPLACE FUNCTION dept(i int) RETURNS TABLE( emp_no numeric, emp_desc varchar(100), emp_sasal numeric,emp_value date) as $$ BEGIN RETURN QUERY (select e.empno,e.ename,e.sal,e.hiredate from emp e where e.deptno=i); END; $$ LANGUAGE plpgsql;

PostgreSQL Function Calling:

select dept(10); select dept(20);

Visual Representation of output:

3. Create a function to add two numbers.

CREATE FUNCTION addition(integer, integer) RETURNS integer AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT;

PostgreSQL Function Calling:

select addition(20,10); select addition(110,10);

Visual Representation of output:

4. Calling PostgreSQL function from Linux shell:

psql -U postgres r2schools -c "select empinfo(7698);" psql -U postgres r2schools -c "select dept(20);" psql -U postgres r2schools -c "select addition(10,20);"

So, in this article we have explained PostgreSQL Function with Synax, examples and function calling from psql tool and from Linux shell.