快捷導(dǎo)航

Postgresql自定義函數(shù)詳解

更新時(shí)間：2020年12月28日 14:57:28 作者：孫霸天

這篇文章主要介紹了Postgresql自定義函數(shù)詳解，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧

PostgreSQL函數(shù)也稱為PostgreSQL存儲(chǔ)過程。 PostgreSQL函數(shù)或存儲(chǔ)過程是存儲(chǔ)在數(shù)據(jù)庫服務(wù)器上并可以使用SQL界面調(diào)用的一組SQL和過程語句(聲明，分配，循環(huán)，控制流程等)。

語法：

CREATE [OR REPLACE] FUNCTION function_name (arguments)  
RETURNS return_datatype AS $variable_name$ 
 DECLARE 
  declaration; 
  [...] 
 BEGIN 
  < function_body > 
  [...] 
  RETURN { variable_name | value } 
 END; LANGUAGE plpgsql;

參數(shù)說明

function_name:指定函數(shù)的名稱。

[OR REPLACE]:是可選的，它允許修改/替換現(xiàn)有函數(shù)。

DECLARE:定義參數(shù)(參數(shù)名寫在前面類型寫在后面)。

BEGIN~END: 在中間寫方法主體。

RETURN:指定要從函數(shù)返回的數(shù)據(jù)類型(它可以是基礎(chǔ)，復(fù)合或域類型，或者也可以引用表列的類型)。

LANGUAGE:它指定實(shí)現(xiàn)該函數(shù)的語言的名稱。

下面我將創(chuàng)建一個(gè)簡(jiǎn)單的函數(shù)：

CREATE OR REPLACE FUNCTION test(id uuid)
 RETURNS INTEGER
 LANGUAGE plpgsql
AS
$$
declare
 count integer;
begin
 count = (SELECT
       json_array_length(A.json::json -> 'features')
      FROM "Json" A
      WHERE A.uid = id);
 return count;
end;
 
$$

該函數(shù)的功能是用來返回json中數(shù)組的長(zhǎng)度的

定義好該函數(shù)后，我們可以像調(diào)用其他的函數(shù)一樣調(diào)用它

SELECT test(id)

返回?cái)?shù)組長(zhǎng)度(integer)

補(bǔ)充：PostgreSQL中自定義函數(shù)（function）返回?cái)?shù)據(jù)集

1.準(zhǔn)備數(shù)據(jù)

建表

 create table city (
 cityId int, 
 cityName varchar(20)
 );

插入數(shù)據(jù)

insert into city values(1,'BeiJing'),(2,'NewYork'),(3,'Hong kong'),(4,'ShaingHai');

2. 函數(shù)實(shí)例

2.1 setof 表名

創(chuàng)建函數(shù)getCity()，它返回city表中所有的數(shù)據(jù)，返回值聲明為 setof 表名。

create or replace function getCity() returns setof city as 
$$
begin
return query select * from city;
end;
$$
language plpgsql;

執(zhí)行函數(shù)。

mydb=# select getCity();
   getcity
-----------------
 (1,BeiJing)
 (2,NewYork)
 (3,"Hong kong")
 (4,ShaingHai)
(4 行記錄)

也可以用類似查詢表的方式。

mydb=# select * from getCity();
 cityid | cityname
--------+-----------
   1 | BeiJing
   2 | NewYork
   3 | Hong kong
   4 | ShaingHai
(4 行記錄)

PostgreSQL還支持對(duì)函數(shù)執(zhí)行結(jié)果進(jìn)行條件判斷并過濾。

mydb=# select * from getCity() where cityId > 3;
 cityid | cityname
--------+-----------
   4 | ShaingHai
(1 行記錄)

2.2 setof record

為了使函數(shù)更加通用，以解決動(dòng)態(tài)返回?cái)?shù)據(jù)集的問題。創(chuàng)建函數(shù)getRows(text)，將表名city作為函數(shù)參數(shù)，它返回表中所有的數(shù)據(jù)，返回值聲明為 setof record。

create or replace function getRows(text) returns setof record as
$$
declare
rec record;
begin
for rec in EXECUTE 'select * from ' || $1 loop
return next rec;
end loop;
return;
end
$$
language 'plpgsql';

執(zhí)行函數(shù)。

mydb=# select * from getRows('city') as city(id int, name varchar(20));
 id |  name
----+-----------
 1 | BeiJing
 2 | NewYork
 3 | Hong kong
 4 | ShaingHai
(4 行記錄)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。

您可能感興趣的文章: