PostgreSQL自定義函數(shù)并且調(diào)用方式
一、背景
在PostgreSQL關(guān)系型數(shù)據(jù)庫中，我們經(jīng)常是調(diào)用系統(tǒng)默認(rèn)的函數(shù)，例如lower() ,arry_to_string()等等，但有時候特殊的需求，默認(rèn)的函數(shù)無法實現(xiàn)轉(zhuǎn)換，那么就需要通過自定義函數(shù)，并且調(diào)用我們自定義的函數(shù)實現(xiàn)數(shù)據(jù)的轉(zhuǎn)換。
二、函數(shù)語法
CREATE [OR REPLACE] FUNCTION function_name (arguments)
RETURNS return_datatype AS $variable_name$
DECLARE
declaration;
[...]
BEGIN
< function_body >
[...]
RETURN { variable_name | value }
END; $variable_name$ LANGUAGE plpgsql;說明
function_name：指定函數(shù)的名稱。
[OR REPLACE]：是可選的，它允許您修改/替換現(xiàn)有函數(shù)。
arguments:函數(shù)參數(shù)[[argmode]argname argtype [default value],[[argmode]argname argtype],[…]];argmode參數(shù)的模式有IN、OUT或則INOUT，缺省值是IN。argname參數(shù)名字。argtype參數(shù)的數(shù)據(jù)類型。default默認(rèn)參數(shù)。
RETURNS：它指定要從函數(shù)返回的數(shù)據(jù)類型。它可以是基礎(chǔ)，復(fù)合或域類型，或者也可以引用表列的類型，比如return int、varchar，返回結(jié)果集時就需要setof來表示，無返回值使用void。如果存在OUT或則INOUT參數(shù)，則可以省略RETURNS子句。
return_datatype：表示返回值類型。
function_body：function_body包含可執(zhí)行部分。
LANGUAGE：它指定實現(xiàn)該函數(shù)的語言的名稱。
三、異常處理語法
在PostgreSQL中可以利用RAISE語句報告信息和拋出錯誤，其聲明形式為：
RAISE LEVEL 'format' [,expression,[...]]
說明：
LEVEL：包含的級別有DEBUG(向服務(wù)器日志寫信息)、LOG(向服務(wù)器日志寫信息，優(yōu)先級更高)、INFO、NOTICE和WARNING(把信息寫到服務(wù)器日志以及轉(zhuǎn)發(fā)到客戶端應(yīng)用，優(yōu)先級逐步升高)和EXCEPTION拋出一個錯誤(通常退出當(dāng)前事務(wù))。某個優(yōu)先級別的信息是報告給客戶端還是寫到服務(wù)器日志，還是兩個均有，是由log_min_messages和client_min_messages這兩個系統(tǒng)初始化參數(shù)控制的。
四、自定義函數(shù)示例
1、format函數(shù)
用于根據(jù)格式字符串設(shè)置參數(shù)的FORMAT()函數(shù)格式：
FORMAT(format_string [, format_arg [, ...] ])
示例：
SELECT FORMAT('Hello, %s', 'world!!');執(zhí)行結(jié)果：Hello, world!!
2、使用default參數(shù)
create or replace function ftest(in fname VARCHAR default '編程語言', cname VARCHAR DEFAULT 'java')
RETURNS VARCHAR as $$
begin
return format('%s_%s',fname,cname);
end; $$ LANGUAGE plpgsql;調(diào)用方式：
select ftest('語言'); --語言_java
select ftest(cname=>'java'); --編程語言_java3、根據(jù)輸入的數(shù)字，返回兩個數(shù)的商，若除數(shù)為0，則拋出自定義異常
CREATE OR REPLACE FUNCTION testdivision ( js1 INT, js2 INT ) RETURNS INT AS $$ DECLARE v_re INT; BEGIN raise notice'% 除以 %', js1, js2; IF js2 = 0 THEN raise EXCEPTION '不能除0'; ELSE v_re := js1 / js2; RETURN v_re; END IF; EXCEPTION --捕獲異常 WHEN OTHERS THEN RETURN 0; END; $$ LANGUAGE plpgsql;
調(diào)用方式：
select testdivision(8,4);--2 select testdivision(3,0);--0
4、多個OUT或者INOUT參數(shù)時，返回是一個元組；可以通過將函數(shù)調(diào)用放在 FROM 子句中來返回元組的各個成員
create or replace FUNCTION test1(in js1 int,inout res1 int,out res2 int) as $$ begin res1:=js1*2; res2:=res1*3; end; $$ LANGUAGE plpgsql;
調(diào)用方式：
select test1(3,2); --test1
--(6,18)
select * from test1(3,2);--res1|res2
-- 6| 18五、動態(tài)執(zhí)行語句
1、數(shù)據(jù)準(zhǔn)備
create table tmp(
bsm VARCHAR(100),
name VARCHAR(100),
num int
);
insert into tmp(bsm,name,num) VALUES('a','蘋果',21);
insert into tmp(bsm,name,num) VALUES('b','香蕉',11);2、示例：動態(tài)查詢某張表的記錄數(shù)
create or replace function getsum(in talename VARCHAR)
RETURNS int as $$
DECLARE
stmt VARCHAR;
count int;
begin
stmt:=format('select count(1) from %s', talename);
raise notice '%',stmt;
EXECUTE stmt into count;
return count;
EXCEPTION --捕獲異常
WHEN OTHERS THEN
RETURN 0;
end; $$ LANGUAGE plpgsql;調(diào)用方式：
select getsum('tmp');--23、動態(tài)創(chuàng)建表
create or replace function copytable(tablename varchar,times int)
RETURNS INT as $$
DECLARE
stmt VARCHAR='create table %s_%s (like tmp including all);';--
begin
for i in 1..times loop
raise notice 'd當(dāng)前次數(shù)%',i;
raise notice '%',format(stmt,tablename,i,tablename);
EXECUTE format('drop table if EXISTS %s_%s;',tablename,i) ;
EXECUTE format(stmt,tablename,i,tablename) ;
end loop;
return 0;
END; $$ LANGUAGE plpgsql;調(diào)用方式：
select copytable('tmp',2);即復(fù)制tmp表2次到此這篇關(guān)于PostgreSQL如何自定義函數(shù)并且調(diào)用的文章就介紹到這了,更多相關(guān)PostgreSQL自定義函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了淺談PostgreSQL和SQLServer的一些差異，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL是一種開源的關(guān)系型數(shù)據(jù)庫管理系統(tǒng),它支持SQL語言以及許多高級功能,如事務(wù)、外鍵、觸發(fā)器等,下面這篇文章主要給大家介紹了關(guān)于Postgresql使用update語句的相關(guān)資料,需要的朋友可以參考下2024-04-04
關(guān)于PostgreSQL JSONB的匹配和交集問題
這篇文章主要介紹了PostgreSQL JSONB的匹配和交集問題，本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-09-09
PostgreSQL 序列(Sequence) 與 Oracle 序列對比差異分
PostgreSQL 和 Oracle 都提供了序列(Sequence)功能,但在實現(xiàn)細(xì)節(jié)和使用方式上存在一些重要差異,下面給大家介紹PostgreSQL 序列(Sequence) 與 Oracle 序列對比差異分析,感興趣的朋友一起看看吧2025-05-05
PostgreSQL數(shù)據(jù)庫命令行執(zhí)行SQL腳本的三種方式
生成環(huán)境中,出于安全性等原因,往往不提供數(shù)據(jù)庫連接工具,所以對數(shù)據(jù)庫的更新和升級就得通過命令行來實現(xiàn),本文總結(jié)了三種命令行執(zhí)行sql腳本的方式,需要的朋友可以參考下2024-02-02
windows PostgreSQL 9.1 安裝詳細(xì)步驟
這篇文章主要介紹了windows PostgreSQL 9.1 安裝詳細(xì)步驟,需要的朋友可以參考下2016-11-11
Linux 上 定時備份postgresql 數(shù)據(jù)庫的方法
這篇文章主要介紹了Linux 上 定時備份postgresql 數(shù)據(jù)庫的方法,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-02-02
本文將深入剖析PostgreSQL表分區(qū)與繼承的實現(xiàn)機(jī)理,結(jié)合最新版本（16版本）的特性演進(jìn),通過大量生產(chǎn)級代碼示例,揭示如何設(shè)計高效的分區(qū)方案、優(yōu)化分區(qū)查詢性能,并巧妙運用繼承特性構(gòu)建靈活的數(shù)據(jù)模型,感興趣的朋友一起看看吧2025-04-04
PostgreSQL向量檢索之pgvector入門實戰(zhàn)指南
pgvector是PostgreSQL的開源擴(kuò)展,用于在數(shù)據(jù)庫中存儲和處理向量數(shù)據(jù),特別是高維嵌入向量（embedding）,本文介紹PostgreSQL向量檢索：pgvector入門指南,感興趣的朋友一起看看吧2026-01-01
本文主要介紹了PostgreSQL查看用戶（角色）的三種方法：SQL查詢系統(tǒng)表（pg_roles/pg_user）、元命令（\du）及pg_shadow視圖,適用于不同場景,如日常管理或腳本處理,并有詳細(xì)的代碼示例供大家參考,需要的朋友可以參考下2025-09-09