pgdba也是开发？pg 基于sql的function使用大法

最帅dba工作笔记 2021-04-22

884

postgresql最近真是如日中天，很多小伙伴纷纷转型，我也是其中一员，介于工作性质同oracle dba的不同，pg在走国产化的道路中，导致pg dba必须要会写function ，从而替代 oracle中的procedure，最近我也把我自己整理和系统学习的干货分享给大家。

什么是function？要注意哪些？

SQL函数包体是一些可执行的SQL语言。同时包含1条以上的查询，但是函数只返回最后一个查询(必须是SELECT)的结果。language sql !!
除非SQL函数声明为返回void，否则最后一条语句必须是SELECT
在简单情况下，返回最后一条查询结果的第一行。
如果最后一个查询不返回任何行，那么该函数将返回NULL值。
如果需要该函数返回最后一条SELECT语句的所有行，可以将函数的返回值定义为集合，即SETOF sometype。

（）中的变量是输入变量，可以定义输出变量

returns 为输出结果的返回值类型

$$内为实际的逻辑操作，符号可加可不加

language sql 为定义基于sql的function

language plpgsql 为定义基于存储过程的function

声明函数

示例：

CREATE OR REPLACE FUNCTION add(a NUMERIC, b NUMERIC)
RETURNS NUMERIC
AS $$
    SELECT a+b;
$$ LANGUAGE SQL;
复制

显示结果：

vastbase=# select add(1,3);
add
-----
   4
复制

(1 row)

未声明函数

示例

CREATE OR REPLACE FUNCTION add3(NUMERIC,NUMERIC)
RETURNS NUMERIC
AS $$
SELECT $1 + $2;
$$ LANGUAGE SQL;
复制

显示结果

select add2(1,100);
add2
------
  101
(1 row)
复制

另外一种写法(可读性不强)：

create or replace function add4 (a numeric,b numeric)
returns numeric as 'select a+b'
language sql;
复制

显示结果：

select add4(1,5);
add4
------
    6
(1 row)
复制

前面说的都是定义的输入变量，如果定义中包含输出变量，则不需要return type。

示例：

create or replace function add5(in a numeric,in b numeric,out c numeric)
as $$
select a+b;
$$ language sql;
复制

结果：

vastbase=# select add5(2,5);
add5
------
    7
(1 row)
复制

如果存在多个sql语句，则返回的就是最后一个sql语句，之前的sql语句都会执行，但是不会被返回

示例：

创建表；

vastbase=# create table t(id int);
CREATE TABLE
vastbase=#
vastbase=# insert into t select generate_series(1,10);
INSERT 0 10
vastbase=# select * from t;
id
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
10
(10 rows)
复制

创建function add_upd_del

create function add_upd_del (a numeric,b numeric)
returns numeric as $$
select a+b;
update t set id=100 where id=1;
select a-b;
$$ language sql;
或者 add_upd_del1
create or replace function add_upd_del1 (in a numeric, in b numeric, out c numeric)
as $$
select a+b;
update t set id=200 where id=2;
select a-b;
$$ language sql;
复制

add_upd_del结果

vastbase=# select add_upd_del(1,3);
add_upd_del
-------------
          -2
(1 row)
vastbase=# select * from t;
id  
-----
   2
   3
   4
   5
   6
   7
   8
   9
  10
100
(10 rows)
复制

add_upd_del1结果

vastbase=# select add_upd_del1(4,3);
add_upd_del1
--------------
            1
(1 row)
vastbase=# select * from t;
id  
-----
   3
   4
   5
   6
   7
   8
   9
  10
100
200
(10 rows)
复制

上面所述，最后一行sql function的最后一行必须是select语句，除非returns的值是void（空类型）

create or replace function del_fun()
returns void  as $$
delete from t where id = 100;
$$ language sql;
复制

结果：

vastbase=# select del_fun();
del_fun
---------
(1 row)
vastbase=# select * from t;
id  
-----
   3
   4
   5
   6
   7
   8
   9
  10
200
(9 rows)
复制

返回一个查询集合，关键字setof

create or replace function sel_fun1()
returns setof varchar as $$
select name from t1;
$$ language sql;
复制

结果

vastbase=# select sel_fun1();
sel_fun1
----------
aaa
bbb
ccc
ddd
eee
(5 rows)
复制

如果returns的类型是varchar

create or replace function sel_fun()
returns varchar as $$
select name from t1;
$$ language sql;
复制

结果：只会显示第一条表中数据

vastbase=# select sel_fun();
sel_fun
---------
aaa
复制

THAT'S ALL

BY CUI PEACE!!!

postgresql

文章转载自最帅dba工作笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

pgdba也是开发？pg 基于sql的function使用大法

评论