Berikut adalah cara membuat function pada database PostgreSQL :
Format :
create [or replace] function function_name(param_list)
returns return_type
language plpgsql
as
$$
declare
-- variable declaration
begin
-- logic
end;
$$
Contoh 1 :
Create Function loopingpenjumlahan(a integer, b integer) Returns integer Language plpgsql As $$
declare
nilai_awal integer default a;
Hasil integer default 0;
Jumlah integer default 0;
Begin
Loop
If nilai_awal > b then
Exit;
End if;
Hasil := hasil+nilai_awal;
Nilai_awal := nilai_awal+1;
End loop;
Jumlah := hasil + b;
Return Jumlah;
End; $$
Output :
Select loopingpenjumlahan (1, 5) as Jumlah;
Jumlah
----------------
21
1 (row)
Contoh 2 :
Create Function nilaiTerkecil (x integer, y integer) returns integer Language plpgsql as $$
declare
nilai integer;
begin
if x < y then
select into nilai x;
else
select into nilai y;
end if;
return nilai;
end; $$
Output :
Select nilaiTerkecil (57,50) as Nilai;
Nilai
---------
50
(1 row)
Contoh 3 :
Create Function getAngkaBulan(namabulan character varying)
RETURNS integer Language plpgsql as $$
DECLARE
bulan integer;
BEGIN
IF namabulan = 'Januari' THEN
bulan = 1;
ELSIF namabulan = 'Februari' THEN
bulan = 2;
ELSIF namabulan = 'Maret' THEN
bulan = 3;
ELSIF namabulan = 'April' THEN
bulan = 4;
ELSIF namabulan = 'Mei' THEN
bulan = 5;
ELSIF namabulan = 'Juni' THEN
bulan = 6;
ELSIF namabulan = 'Juli' THEN
bulan = 7;
ELSIF namabulan = 'Agustus' THEN
bulan = 8;
ELSIF namabulan = 'September' THEN
bulan = 9;
ELSIF namabulan = 'Oktober' THEN
bulan = 10;
ELSIF namabulan = 'November' THEN
bulan = 11;
ELSIF namabulan = 'Desember' THEN
bulan = 12;
END IF;
RETURN bulan;
END; $$
Output :
Select getAngkaBulan ( 'Desember') as Bulan;
Bulan
---------
12
(1 row)
- Menghapus fungsi :
DROP FUNCTION nama_fungsi(paramater, parameter, parameter ... );
Contoh :
DROP FUNCTION loopingpenjumlahan(integer, integer);
DROP FUNCTION nilaiTerkecil(integer, integer);
DROP FUNCTION getAngkaBulan(character varying);
