PostgreSQL User Defined Function
14 min readFeb 29, 2024
When we move to learn advance SQL then first we need to face SQL Windows Functions, Control Statements, Function, Procedure …
In this article we will understand how to write PL/pgSQL (or PostgreSQL) user define function. In PL/pgSQL, a user-defined function (UDF) can be quite versatile, it handling simple to complex processing tasks. Understanding the components of a PL/pgSQL function is crucial for effective database management and application development.
Also we will understand PL/pgSQL Controls Statements at the same article.
Locally I’m using in pgAdmin-4 Tools . Syntax may vary little bit for other PL/pgSQL tools.
You may use Online Compiler or db<>fiddle to verify your objective.
Basic PostgreSQL Data Types:
We will use variable under function. So, there are some basic PL/pgSQL data types for reference.
-- DataType: Integer
SMALLINT - 2 bytes,
INT or INTEGER - 4 bytes,
-- Decimal Value
FLOAT(n) - 8 bytes upto n precision
FLOAT8 or REAL - 4 bytes
NUMERIC(i,d) - i: integer digits, d: decimal digits
-- DataType: String
CHAR(n) - Fixed Length n,
VARCHAR(n)- Fixed Length n,
TEXT - Unlimited length
-- DataType: Boolean
BOOL or BOOLEAN
-- DataType: Date (yyyy-mm-dd)
DATE
-- DataType: Time (hh:mm:ss)
TIME, TIMETZ
-- DataType: DateTime (yyyy-mm-dd hh:mm:ss.msmsms)
TIMESTAMP, TIMESTAMPTZThis is details of all PL/pgSQL data types: postgresql data types
PostgreSQL Control Statements:
Before describe user define function we need to know the PL/pgSQL control statements structure, because we will use the control statements within user define function.
IF Statement
IF (condition) then
statement;
END IF;IF-ELSE Statement
IF (condition) THEN
statement;
ELSE
statement;
END IF;
-- Check Example:04Nested-IF Statement
IF (condition1) then
statement;
ELSEIF (condition2)
statement;
... ... ...
ELSE
statement;
END IF;
-- OR --
IF (condition1) then
IF (condition11) then
statement;
ELSEIF (condition12) then
statement;
... ... ...
ELSE
statement;
END IF;
ELSEIF (condition2) then
statement;
... ... ...
ELSE
statement;
END IF;
-- Check Example-06CASE Statement
CASE (expression)
WHEN (condition1) then
statement;
WHEN (condition2) then
statement;
... ... ...
ELSE
statement;
END CASE;
-- check Example-09LOOP with BREAK Statement
LOOP
statement;
if(condition) then
EXIT;
end if;
END LOOP;
-- OR ---
LOOP
statement;
EXIT when (condition);
END LOOP;
-- Check Example-07WHILE LOOP with BREAK Statement
WHILE (condition_with_break_logic)
LOOP
statement;
END LOOP;
-- OR ---
WHILE (condition1)
LOOP
statement;
EXIT WHEN(condition2)
END LOOP;
-- OR ---
WHILE (condition1)
LOOP
statement;
if(condition2) then
EXIT;
end if;
END LOOP;
-- Check Example-10FOR LOOP Statement
FOR counter IN initial_Value .. final_Value LOOP
statement;
END LOOP;
-- OR ---
FOR counter IN initial_Value .. final_Value LOOP
statement;
-- For Loop With Break Statement
EXIT WHEN(condition);
END LOOP;
-- OR ---
FOR counter IN initial_Value .. final_Value LOOP
statement;
-- For Loop With Break Statement
IF(condition) then
EXIT
END IF;
END LOOP;Continue Statement
-- Generally used with in any loop to skip some statements
LOOP
if(condition1) then
CONTINUE;
end if;
statement;
if(condition) then
exit;
end if;
END LOOP;
-- OR ---
WHILE (condition_with_break_logic)
LOOP
CONTINUE when(condition);
statement;
END LOOP;
-- OR ---
FOR counter IN initial_Value .. final_Value LOOP
CONTINUE when(condition);
statement;
END LOOP;
-- Check Example:08Now we are familiar with PL/pgSQL control statement structure. Don’t worry the uses of Controls Statements, I will explain it next.
go ahead … … 😊
PostgreSQL User Define Function
(i). Create a User Define Function
When we define a PL/pgSQL Userdefine funciton, basically it has 3 parts.
These are as follows:
- Function Header :
● Create [or Replace] Function: This command begin the definition of a function.
● Function Name: Name of Function required. It can be used with schema name (schema.function_name)
● Parameters: need to pass parameters with it’s data type. SQL function have an other parameter typein(input)/out(output)/inout(both input & output)CREATE FUNCTION Function_Name ( params_1 datatype, params_2 data_type, ...)
orCREATE or REPLACE FUNCTION Function_Name ( params_1 datatype, params_2 data_type, ...)or
CREATE or REPLACE FUNCTION Function_Name ( in params_1 datatype, in params_2 data_type, ..., out params_4 data_type,)● Return Type: need to specify the datatype of return varible asreturns variable_datatype - Function Body :
● AS : Need to enclose the function body using dollar quoting to avaoid conficts. It’s as follows:AS $$ <function main body> $$;
orAS $BODY$ <function main body> $BODY$;● DECLARE : This is an optional section, used to declare local variables, which used within function body. You may assign a varibale directly here with some default value.DECLARE● BEGIN … END; : This part is core part of function body. This block used the controls statements and your logic. As SQL function have a return option, so, you must have to return a varibale before END statement.
a TEXT; b INT; c INT := 12; d bool := true; - Language Declaration:
Need to specify the function is written in PL/pgSQL language.LANGUAGE plpgsql
You may stitches this in two area at PostgreSQL user defined function. Before or After “Function Body”. This is necessary because PostgreSQL supports multiple procedural languages.
So, this is basic structure how to define a PL/pgSQL (PostgreSQL) function.
CREATE FUNCTION function_name
(parameter_name DATATYPE, parameter_name DATATYPE)
RETURNS RETURN_DATATYPE
AS $$
DECLARE
-- Declare Variables (if required)
BEGIN
-- Write your function body
... ... ...
... ... ...
... ... ...
-- return the variable and the end of function body
return return_parameter;
END;
$$
LANGUAGE plpgsql;
-- Alternative Way ---
CREATE [OR REPLACE] FUNCTION function_name
(parameter_name DATATYPE, parameter_name DATATYPE)
RETURNS RETURN_DATATYPE
LANGUAGE plpgsql
AS $$
DECLARE
-- Declare Variables (if required)
BEGIN
-- Write your function body
... ... ...
... ... ...
... ... ...
-- return the variable and the end of function body
return return_parameter;
END;
$$;(iI). Delete a User Define Function
To delete a UDF, you need use the DROP FUNCTION statement.
DROP FUNCTION [IF EXISTS] function_name
(parameter_name DATATYPE, parameter_name DATATYPE, ...);When we drop or create a function must need to check the number of parameters and there data types.
Please check : “PL/pgSQL Function Overloading”
There are few Example of PL/pgSQL UDF
Example-01: Welcome Message
-- Delete function if it's exists in DB
drop function if exists welcome();
-- Create a function with function name as 'welcome'
create function welcome ()
-- Mention function return type
returns text as
-- start function body
$BODY$
BEGIN
return 'Welcome to PostgerSQL Custom Function';
END;
-- end of function body
$BODY$
-- mention the language end of function body
LANGUAGE plpgsql;
-- Call To Function
select welcome() as sms;
-- Output: Welcome to PostgerSQL Custom Functionsome other way
-- Delete function if it's exists in DB
drop function if exists welcome1();
-- Create function with function name as 'welcome'
create function welcome1()
-- Mention function return type
returns text
-- mention the language before function body
LANGUAGE plpgsql as
-- start function body
$BODY$
BEGIN
return 'Welcome to PostgerSQL Custom Function';
END;
-- end function body
$BODY$;
-- Call To Function
select welcome1() as sms;
-- Output: Welcome to PostgerSQL Custom Functionway through declared variable
-- delete function if it's exists in DB
drop function if exists welcome2();
-- Create or replace function with function name as 'welcome'
create or replace function welcome2()
-- Mention function return type
returns TEXT
-- mention the language
LANGUAGE plpgsql as
-- start function body
$BIGBOSS$
-- declare variable with data type
DECLARE
txt TEXT;
BEGIN
txt := 'Welcome to PostgerSQL Custom Function';
return txt;
END;
-- end function body
$BIGBOSS$;
-- Call To Function
select welcome2() as sms;
-- Output: Welcome to PostgerSQL Custom Functionway through passing parameter
-- Delete function if it's exists in DB
drop function if exists welcome3(TEXT);
-- Create or replace function with function name as 'welcome'
create or replace function welcome3(st TEXT)
-- Mention function return type
returns TEXT
-- mention the language
LANGUAGE plpgsql as
-- start function body
$FunctionBody$
-- declare & assign variable with data type
DECLARE
txt TEXT := 'Welcome to ' || st;
BEGIN
return txt;
END;
-- end function body
$FunctionBody$;
-- Call To Function
select welcome3('PostgerSQL Custom Function') as sms;
-- Output: Welcome to PostgerSQL Custom FunctionExample-02: Sum of two number
drop function if exists addition2(int, int);
create or replace function addition2(a int, b int)
returns int
LANGUAGE plpgsql AS
$BODY$
DECLARE
c int;
BEGIN
c := a + b;
return c;
END;
$BODY$;
-- Call to PL/pgSQL Function
select addition1(2, 3);
select addition2(2, 3);Example-03: Temperature Conversion ≡ Fahrenheit ⇄ Celsius
-- Hints: C = (F - 32) * 5/9;
-- Hints: F = (C * 9 / 5) + 32
-- Celsius to Fahrenheit
drop function if exists CtoF(real);
create or replace function CtoF(real)
returns real
language plpgsql AS
$C2F$
BEGIN
return (($1 * 9/5)+32)::real;
END;
$C2F$;
-- Fahrenheit to Celsius
drop function if exists FtoC(real);
create or replace function FtoC(real)
returns real
language plpgsql AS
$F2C$
BEGIN
return (($1 - 32) * 5/9)::real;
END;
$F2C$;
select CtoF(38);
-- Output: 100.4
select FtoC(100.4);
-- Output: 38Example-04: Check Even or Odd Number : **IF-ELSE STATEMENT**
drop function if exists checkEven(int);
create function checkEven(a int)
returns text as $$
BEGIN
if(mod(a,2) = 0) then return 'Even Number';
else return 'Odd Number';
end if;
END
$$ LANGUAGE plpgsql;
select checkEven(2);
select checkEven(3);Example-05: Calculate Age
-- Return Full Age with Year, Month, Day & Time
drop function if exists getAge(date);
create function getAge(dob date)
returns interval as $$
BEGIN
-- Return Full Age with Year, Month, Day & Time
-- return AGE(now(), dob::date);
-- Return only Year & Month
return REGEXP_REPLACE((AGE(now(), dob::date)::text), '\d+ days .*', '')::interval;
END
$$ LANGUAGE plpgsql;
select getAge('2022-12-01');
-- output: 1 year 2 monsExample-06: School Grade System: **NESTED-IF STATEMENT**
drop function if exists grade(int);
create or replace function grade(int)
returns text
language plpgsql
as
$BODY$
declare
grd text;
BEGIN
if($1 <= 55) then grd := 'Below Agerage';
elseif($1 > 55 and $1 <= 75) then grd := 'Agerage';
elseif($1 > 75 and $1 <= 90) then grd := 'Good';
else grd := 'Excellent';
end if;
return grd;
END;
$BODY$;
select grade(5), grade(55), grade(60), grade(75), grade(90), grade(99);
-- Output: "Below Agerage" "Below Agerage" "Agerage" "Agerage" "Good" "Excellent"Example-07: Sum of all numbers between an interval: **LOOP STATEMENT**
drop function if exists sumInRange(int, int);
create or replace function sumInRange( i1 int, i2 int)
returns int
Language plpgsql AS
$BODY$
DECLARE
s int := 0;
BEGIN
if(i2 < i1) then
s := i1; i1 := i2; i2 := s;
s := 0;
end if;
LOOP
s := s + i1;
i1 := i1 + 1;
EXIT when(i1 > i2);
END Loop;
return s;
END;
$BODY$;
select sumInRange(2, 5),sumInRange(5, 2);
-- Output: 14 14Example-08: Check Prime Number: **FOR LOOP with BREAK**
drop function if exists isPrime(int);
create or replace function isPrime(n int)
returns text
Language plpgsql
as
$BODY$
DECLARE
res text := 'Prime Number';
i int;
BEGIN
if(n <= 0) then res := 'Invalid Input';
elseif(n = 1) then res := 'Not a Prime or Composite';
elseif(n = 2 or n = 3) then res := res;
else
for i in 2 .. (sqrt(n)+1)::int Loop
raise notice 'div by: mod(%, %)', n, i;
if(mod(n, i) = 0) then res := 'Composite Number';
end if;
-- for loop break statement
exit when res like 'Composite Number';
end loop;
end if;
return res;
END;
$BODY$;
select isPrime(-1), isPrime(0), isPrime(1),
isPrime(2), isPrime(3), isPrime(4);
-- Output: "Invalid Input" "Invalid Input" "Not a Prime or Composite" "Prime Number" "Prime Number" "Composite Number"
select isPrime(117), isPrime(131);
-- Output: "Composite Number" "Prime Number"Example-09: print n FIBONACCI Numbers : **For Loop & IF Condition**
drop function if exists print_n_FibonacciNumber(int);
create or replace function print_n_FibonacciNumber(int)
returns text
Language plpgsql AS
$body$
DECLARE
f0 int := 0; f1 int := 1;
f int; i int;
res text := (f0::text || ', ' || f1::text);
BEGIN
CASE
WHEN($1 = 0) then return f0::text;
WHEN($1 = 1) then return res;
ELSE
for i in 2 .. $1 loop
f := f1 + f0;
f0 := f1; f1 := f;
res := (res || ', ' || f);
end loop;
return res;
END CASE;
END;
$body$;
select print_n_FibonacciNumber(10);
-- output: "0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55"Example-10: Find FIBONACCI Numbers within an interval: **While Loop**
drop function if exists find_FibonacciNumber(int, int);
create or replace function find_FibonacciNumber(int, int)
returns text
Language plpgsql
AS
$body$
DECLARE
f0 int := 0; f1 int := 1;
f int;
res text := '';
BEGIN
-- interval validation
if($1 > $2) then
f := $1; $1 := $2; $2 := f;
end if;
if(f0 >= $1 and f0 <= $2)
then res := (res || ', ' || f0);
end if;
if(f1 >= $1 and f1 <= $2)
then res := (res || ', ' || f1);
end if;
-- While Loop
while (f1 < $2) loop
f := f1 + f0;
f0 := f1; f1 := f;
if(f >= $1 and f <= $2) then
if length(res) = 0 then res := f::text;
else res := (res || ', ' || f);
end if;
end if;
end loop;
return res;
END;
$body$;
select find_FibonacciNumber(1000, 100);
-- output: "144, 233, 377, 610, 987"Example-11: Find GCD of two integer
drop function if exists gcdFun(int, int);
create or replace function gcdFun(m int, n int)
returns text
Language plpgsql AS
$body$
DECLARE
t int;
BEGIN
if(m < n) then
t := m; m := n; n := t;
end if;
while (mod(m, n) > 0) loop
t := m%n; m := n; n := t;
end loop;
return n;
END;
$body$;
-- User Define Function, SQL Default Function
select gcdFun(255, 775), gcd(19, 113);
-- output: 5 1Example-12: Find all primes between interval
-- Prime Number Validation
drop function if exists checkPrime(int);
create or replace function checkPrime(n int)
returns boolean
language plpgsql AS
$CHECKPRIME$
DECLARE
i int; _flag boolean := true;
BEGIN
if(n<=0 or n=1) then return false;
elseif(n=2 or n=3) then return true;
else
for i in 2 .. (sqrt(n)+1)::int LOOP
if(mod(n, i) = 0) then
_flag := false;
end if;
exit when _flag = false;
end LOOP;
return _flag;
end if;
END;
$CHECKPRIME$;
-- Pass Interval and return all Primes within that interval
drop function if exists findPrimesInInterval(int, int);
create or replace function findPrimesInInterval(int, int)
returns text
language plpgsql
AS
$findPrimesInInterval$
DECLARE
t int;
res text := '';
BEGIN
-- interval validation
if($1 > $2) then
t := $1; $1 := $2; $2 := t;
end if;
-- for loop
for t in $1 .. $2 LOOP
if(checkPrime(t) = true)
then res := res || ' ' || t::text;
end if;
end LOOP;
-- https://medium.com/@artbindu/how-to-use-regex-in-mysql-5ab60a43a883
-- start pos: 1 (default), occurance: 0 (match globally)
return REGEXP_REPLACE(trim(res), ' ', ', ', 1, 0);
END;
$findPrimesInInterval$;
select findPrimesInInterval(3, 16);
-- Output: "3, 5, 7, 11, 13"Example-13: Count number of weekdays between two date
drop function if exists getWorkingDays(timestamp, timestamp);
create or replace function getWorkingDays(timestamp, timestamp)
returns INT
language plpgsql AS
$FunctionBody$
DECLARE
tcount INT := 0; tWeekDays INT := 0;
_temp timestamp; i INT; dayName TEXT := '';
BEGIN
-- date validation
if($1 > $2) then
_temp := $1; $1 := $2; $2 := _temp;
end if;
-- total days
tcount := (to_date(date_trunc('day', $2::timestamp)::TEXT, 'YYYY-MM-DD') -
to_date(date_trunc('day', $1::timestamp)::TEXT, 'YYYY-MM-DD')) + 1;
-- verify working days
for i in 1 .. tcount LOOP
if (i = 1) then _temp := $1;
else _temp := (_temp + INTERVAL '1 day');
end if;
-- get day name
dayName := to_char(_temp::timestamptz, 'Day');
-- check weekend
if NOT REGEXP_LIKE(dayName, '(Saturday|Sunday)')
then tWeekDays := tWeekDays + 1;
end if;
end LOOP;
return tWeekDays;
END;
$FunctionBody$;
select getWorkingDays('2024-02-01 14:00:00'::timestamp, '2024-03-01 13:00:00'::timestamp)
as working_days;
-- output: 22Example-14: Leap Year Validation
drop function if exists isLeapYear(INT);
create or replace function isLeapYear(INT)
returns boolean
language plpgsql AS
$BODY$
BEGIN
if(mod($1, 100) = 0 and mod($1, 400) = 0)
then return true;
elseif(mod($1, 100) <> 0 and mod($1, 4) = 0)
then return true;
else return false;
end if;
END;
$BODY$;
select isLeapYear(2023), isLeapYear(2024),
isLeapYear(1900), isLeapYear(2000);
-- output: false true false trueExample-15: Factorial n
drop function if exists fact(INT);
create or replace function fact(INT)
returns INT
language PLPGSQL AS
$BODY$
DECLARE
res INTEGER := 1; i INT;
BEGIN
if(mod($1, 2) = 1) then res:= (($1/2)::INT + 1);
end if;
for i in 1 .. ($1/2)::INT LOOP
res := (res * i * ($1-i+1));
end LOOP;
return res;
END;
$BODY$;
select fact(5);
-- output: 120Example-16: Calculate nCr & nPr
-- Used previous Fact(n) here
drop function if exists nCr(INT, INT);
create or replace function nCr(INT, INT)
returns INTEGER
language plpgsql AS
$BODY$
DECLARE
res INTEGER := 1;
BEGIN
return (fact($1)/(fact($2) * fact($1-$2)));
END;
$BODY$;
drop function if exists nPr(INT, INT);
create or replace function nPr(INT, INT)
returns INTEGER
language plpgsql AS
$BODY$
DECLARE
res INTEGER := 1;
BEGIN
return (fact($1)/fact($1-$2));
END;
$BODY$;
select nCr(5,2);
-- output: 10
select nPr(5,2);
-- output: 20PL/pgSQL Recursive Function
Here explain the Recursive Function in PL/pgSQL. For Recursive Function need to create two functions.
First one is normal function and next one use for recursive call.
There are few examples how to use recursive function in PL/pgSQL.
Example-01: Compute F(x, y):
F(x, y) = F(x-y, y) + 1; if y <= x
otherwise F(x, y) = 0;
-- second function: used for recursive call
drop function if exists F(real, real);
create or replace function F(real, real)
returns real
language plpgsql AS
$BODY$
BEGIN
if($2 <= $1) then
return (F($1-$2, $2) + 1);
else return 0;
end if;
END;
$BODY$;
-- first function where we call to recursive function
drop function if exists recFun(real, real);
create or replace function recFun(real, real)
returns real
language plpgsql AS
$BODY$
BEGIN
return F($1, $2);
END;
$BODY$;
select recFun(5, 3);
-- output: 1Example-02: Compute Lambda(n):
lambda(n) = lambda(n/2) + 1; if n > 1
else lambda(n) = 0;
-- second function: used for recursive call
drop function if exists lambda(INT);
create or replace function lambda(INT)
returns real
language plpgsql AS
$BODY$
BEGIN
if($1>1) then
return (lambda(($1/2)::INT) + 1);
else return 0;
end if;
END;
$BODY$;
-- first function where we call to recursive function
drop function if exists recFun(INT);
create or replace function recFun(INT)
returns real
language plpgsql AS
$BODY$
BEGIN
return lambda($1);
END;
$BODY$;
select recFun(18);
-- output: 4Example-03: Fibonacci series using Recursive Function
drop function if exists recFibonacciCall(INT, INT, INT);
create or replace function recFibonacciCall(f0 INT, f1 INT, c INT)
returns TEXT
language plpgsql AS
$BODY$
BEGIN
if(c = 0) then return '';
else
return (', ' || (f1+f0)::TEXT || recFibonacciCall(f1, (f0+f1), (c-1)));
end if;
END;
$BODY$;
drop function if exists rFibonacci(INT);
create or replace function rFibonacci(INT)
returns TEXT
language plpgsql AS
$BODY$
DECLARE
f0 INT :=0; f1 INT := 1;
BEGIN
if($1 = 0) then return f0::TEXT;
else
return (f0::TEXT || ', ' || f1::TEXT || recFibonacciCall(f0, f1, ($1-1)));
end if;
END;
$BODY$;
select rFibonacci(7);
-- output: "0, 1, 1, 2, 3, 5, 8, 13"Example-04: Factorial of a natural number using Recursive function
drop function if exists recFactCall(INT);
create or replace function recFactCall(n INT)
returns INT
language plpgsql AS
$BODY$
DECLARE
m INT := (n-1)::INT;
BEGIN
if(n = 1 or n=0) then return 1;
else return n * recFactCall(m);
end if;
END;
$BODY$;
drop function if exists rFact(INT);
create or replace function rFact(n INT)
returns INT
language plpgsql AS
$BODY$
BEGIN
return recFactCall(n);
END;
$BODY$;
select rFact(6), rFact(5);
-- output: 720 120Example-05: GCD(a,b) using Recursive Function
drop function if exists recGCDCall(INT, INT);
create or replace function recGCDCall(INT, INT)
returns INT
language plpgsql AS
$BODY$
DECLARE
t INT;
BEGIN
t := mod($1, $2);
if(t=0) then return $2;
else return recGCDCall($2, t);
end if;
END;
$BODY$;
drop function if exists rGCD(INT, INT);
create or replace function rGCD(m INT, n INT)
returns INT
language plpgsql AS
$BODY$
DECLARE
t INT;
BEGIN
if(n>m) then
t := n; n := m; m := t;
end if;
return recGCDCall(m,n);
END;
$BODY$;
select rGCD(16, 18);
-- output: 2Example-06: PostgreSQL recursive call validation 😄
This is a funny method 😄 to check number of recursive call in PL/pgSQL
📌On my end 731 Recursive call is happening.
📌Check your end and see output is coming, must replay here.
drop function if exists recInitialPos(INT, INT);
create or replace function recInitialPos(INT, INT)
returns INT
language plpgsql AS
$BODY$
BEGIN
-- raise notice 'Pos: %', $1;
$2 := $2 + 1;
if($1 = 1 or $1 = 0) then return $2;
else return recInitialPos($1-1, $2);
end if;
END;
$BODY$;
drop function if exists rInitial(INT);
create or replace function rInitial(INT)
returns INT
language plpgsql AS
$BODY$
DECLARE
counter INT := 0;
BEGIN
return recInitialPos($1, counter);
END;
$BODY$;
-- Check Number of recursive call
select rInitial(731);
-- output: 731
-- Maximum 731 Plpgsql recursive call is happening my EndPL/pgSQL User Define Function with Optional Parameter
Some times we write function with some optional parameter. Let’s understand, how to pass an optional parameter in PostgreSQL UDF.
We need to change in Function Header. We have to pass as:param datatype [DEFAULT | =] default_value
There is an example :
drop function if exists testFunction(_age int, _name varchar);
create or replace function testFunction(_age int, _name varchar(30) default '')
returns text
language plpgsql AS
$$
DECLARE
_header text := '';
BEGIN
if(_name like '') then
_header := 'Hi! guys,';
else _header := 'Hello ' || _name || ',';
end if;
return _header || ' You are ' || _age || ' year old.';
END;
$$;
-- --------- Call to Function with Optional Parameter -----------
select testFunction(12), testFunction(12, 'Jon');
-- "Hi! guys, You are 12 year old." "Hello Jon, You are 12 year old."You may pass multiple default parameters as
CREATE OR REPLACE FUNCTION your_function_name (
param1 datatype,
param1 datatype,
param3 datatype DEFAULT default_value,
param4 datatype DEFAULT default_value
) RETURNS return_typePL/pgSQL Function Overloading
As PL/pgSQL is a procedural language, it have ability to create multiple functions with the same name with different input parameters,
i.e “Function Overloading”.
So, when you updating any function or change order of input parameters must careful about that. Also, be careful at drop scenario.
Thank You … Come again …
Reference:
1. www.pgadmin.org
2. www.techstrikers.com
