PostgreSQL 函数与存储过程及调用

Apr 10, 2024 --- · 8 min read ·

Share on:

PostgreSQL 随着云服务的盛行，越发被广泛的应用，免费开源且有丰富的特性支持，加上性能也很不错，因而备受青睐。PostgreSQL 的函数与存储过程区别并不太大，不像某些数据库的函数与存储过程必须是无副作用或有副作用，在 PostgreSQL 的函数和存储过程中可以进行任何的 SQL 操作。简单列举下 PostgreSQL 的函数与存储过程的区别主要如下：

函数

return 或 out 参数返回值，return 可返回单个值或一系列值(return setof 或 return table), 或返回光标(cursor). 函数 return void 就和存储过程差不多了
函数因其有返回值，所以可通过 select, insert, updata 或 delete 语句来调用，如 select fn1(), delete * from test1 where fn2(c1)=0
可以用 execute 执行动态 sql, 如 execute 'delete * from ' || 't1'

存储过程

IN, OUT 或 INOUT 参数，但不直接返回值
不能用 select, insert 等语句使用
不能用 execute 执行动态 sql

接下来我们来体验一下 PostgreSQL 的函数与存储过程

先用 docker 启动一个 PostgreSQL 数据库，我们使用下面的命令

$ mkdir pgdata
$ docker run -it -v $(PWD)/pgdata:/var/lib/postgresql/data -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 postgres:15

然后在有 psql 命令的终端执行 $ PGPASSWORD=mysecretpassword psql -h localhost -U postgres 就能连接到该数据库

$ PGPASSWORD=mysecretpassword psql -h localhost -U postgres
psql (16.2, server 15.6 (Debian 15.6-1.pgdg120+2))
Type "help" for help. postgres=# \c
psql (16.2, server 15.6 (Debian 15.6-1.pgdg120+2))
You are now connected to database "postgres" as user "postgres".
postgres=#

或者用数据库客户端，如 DBeaver 来连接并操作

PostgreSQL 函数

我们不叙述它的完整定义语法，只举几个例子

带 IN 参数和返回单个值

 1create or replace function fn1(a1 integer, a2 numeric)
 2    returns numeric
 3    language plpgsql
 4    as $$
 5declare var_count integer;
 6begin
 7    select count(*) into var_count from pg_database;
 8    if(a1<5) then
 9        return a1 + a2;
10    else
11        return var_count + a2;
12    end if;
13end;
14$$;

调用

1select fn1(1,5);  -- 6
2select fn1(2,5);  -- 7
3select fn1(6,5);  -- 8
4select fn1(9,5);  -- 8

不能通过 call 调用

1postgres=# call fn1(1,3);
2ERROR:  fn1(integer, integer) is not a procedure
3LINE 1: call fn1(1,3);
4             ^
5HINT:  To call a function, use SELECT.

函数返回一个游标

 1create or replace function fn2()
 2    returns refcursor
 3    language plpgsql
 4    as $$
 5declare ref_cursor refcursor;
 6begin
 7    open ref_cursor for
 8      select oid, datname from pg_database;
 9    return ref_cursor;
10end;
11$$;

现在用 select fn2() 得到的是一个

 1postgres=# select fn2();
 2fn2
 3--------------------
 4<unnamed portal 1>
 5(1 row)
 6
 7postgres=# select fn2();
 8fn2
 9--------------------
10<unnamed portal 2>
11(1 row)

无法看到游标所代表的结果，需要用 fetch all in "<unnamed portal 1>" 来获得数据，但在 select fn2() 后之后得到游标名称后直接 fetch all 是不行的，如我们再 select fn2();

1postgres=# select fn2();
2fn2
3--------------------
4<unnamed portal 3>
5(1 row)
6
7postgres=# fetch all in "<unnamed portal 3>" ;
8ERROR: cursor "<unnamed portal 3>" does not exist

原因是 select fn2() 之后事物便结束了，<unnamed portal 3> 只存在当前事物中，再次用 fetch all in "<unnamed portal 3>" 它已不存在，需要把 select 和 fetch 包裹在同一个事物当中

 1postgres=# begin;
 2BEGIN
 3postgres=*# select fn2();
 4        fn2
 5--------------------
 6 <unnamed portal 6>
 7(1 row)
 8
 9postgres=*# fetch all in "<unnamed portal 6>";
10 oid |  datname
11-----+-----------
12   5 | postgres
13   1 | template1
14   4 | template0
15(3 rows)
16
17postgres=*# rollback;
18ROLLBACK

begin; 能启动一个事物，在同一个事物中便可获得游标的记录，至于落后用 rollback 或 commit 结束事物都行

如果是用 DB 客户端，如 DBeaver 的话，可设置 Database/Transaction Mode 为 Manual Commit 可以执行 select fn2() 得到游标名后，接着 fetch 结果

1select fn2();
2fetch all in "<unnamed portal 3>"

命名返回游标

前面的函数返回 refcursor 时，在 select fn2() 时游标名称是依次递增的，如 <unnamed portal 1>, <unnamed portal 2>...., 在 fetch 时必须复制产生的游标名称。我们也可以让函数返回可预知的名称，比如通过参数

 1create or replace function fn3(cursor_name character varying)
 2    returns refcursor
 3    language plpgsql
 4    as $$
 5declare ref_cursor refcursor:= cursor_name;
 6begin
 7    open ref_cursor for
 8      select oid, datname from pg_database;
 9    return ref_cursor;
10end;
11$$;

那么现在执行 fn3 的游标名称就是明确的，因此可以果断的连续执行下面两句

 1postgres=# begin; select fn3('mycur'); fetch all in mycur;
 2BEGIN
 3  fn3
 4-------
 5 mycur
 6(1 row)
 7
 8 oid |  datname
 9-----+-----------
10   5 | postgres
11   1 | template1
12   4 | template0
13(3 rows)

另一种方式，或者通过 $1 来引用参数

 1create or replace function fn4(refcursor)
 2    returns refcursor
 3    language plpgsql
 4    as $$
 5begin
 6    open $1 for
 7      select oid, datname from pg_database;
 8    return $1;
 9end;
10$$;

同样的

 1postgres=# begin; select fn4('mycur1'); fetch all in mycur1;
 2BEGIN
 3  fn4
 4--------
 5 mycur1
 6(1 row)
 7
 8 oid |  datname
 9-----+-----------
10   5 | postgres
11   1 | template1
12   4 | template0
13(3 rows)

返回 table 的函数

1create or replace function fn5(p_datname varchar = 'temp')
2  returns table(col1 integer, col2 varchar)
3  language plpgsql as
4$func$
5begin
6   return query execute 
7     format($f$select cast(oid as integer) as col1, cast(datname as varchar) as col2 from pg_database where datname like '%s%%' $f$, p_datname);
8end
9$func$

调用返回 table 的函数无需开启事物

 1postgres=# select fn5();
 2      fn5
 3---------------
 4 (1,template1)
 5 (4,template0)
 6(2 rows)
 7
 8postgres=# select fn5('p');
 9     fn5
10--------------
11 (5,postgres)
12(1 row)

return setof 可以是 setof 任何类型或记录，如 setof varchar, setof record 或 setof refcursor. 像下面的例子

 1drop table t1;
 2create table t1(c1 integer, c2 varchar(16));
 3insert into t1 values (1, 'a'), (2, 'b');
 4
 5create or replace function fn6()
 6  returns setof record language sql as
 7$$
 8  insert into t1 values(3, 'c');
 9  select * from t1;
10$$

我们创建一个表 t1，然后在 fn6() 函数中返回 record, 并演示函数中的副作用操作(insert)

1postgres=# select fn6();
2  fn6
3-------
4 (1,a)
5 (2,b)
6 (3,c)
7(3 rows)

也是不需要开启事物。

多个 return 返回一系列值的情况

1create or replace function fn7()
2  returns setof varchar
3  language plpgsql as $$
4begin 
5    return next 'hello';
6    return next 'world';
7    return next '!';
8end;
9$$

查询

1postgres=# select fn7();
2  fn7
3-------
4 hello
5 world
6 !
7(3 rows)

函数的 OUT 参数

函数除了 return 返回值外，可以像存储过程那样用 OUT 参数返回值。其实从最前面的 PostgreSQL 的函数与存储过程来看，功能上存储过程完全没有存在的必要，存储过程能做的函数都能干，函数还能有返回值，可用于 select 等操作，而且还能 execute 动态语句。如果说 PostgreSQL 还有存储过程的一席之地的话，那么只能按照数据库函数与存储过程的约定来选择创建函数还是存储过程

函数：有返回值，不应产生副作用，不推荐用 OUT 参数返回值
存储过程：应让它具有副作用，必要时用 OUT 参数返回值

函数的 OUT 参数与 returns 类型是有关联的

一个 OUT 参数时，returns 类型必须与 OUT 参数类型一致

1create or replace function f8(x out integer)
2  returns varchar
3  language plpgsql as $$
4begin
5  return 'abc';
6end
7$$

执行时会出错

SQL Error [42P13]: ERROR: function result type must be integer because of OUT parameters

但改成 o out varchar 也不行

1create or replace function fn8(x out varchar)
2  returns varchar
3  language plpgsql as $$
4begin
5   return 'abc';
6end
7$$

仍然出错

SQL Error [42804]: ERROR: RETURN cannot have a parameter in function with OUT parameters
Position: 104

解决的办法是去掉 OUT 参数或者保留 OUT 参数时，在函数体中只能给 OUT 参数赋值

1create or replace function fn8()
2  returns varchar
3  language plpgsql as $$
4begin
5    return 'abc';
6end
7$$

或者

1create or replace function fn8(x out varchar)
2  returns varchar
3  language plpgsql as $$
4begin
5  x := 'abc';
6end
7$$

执行效果上都是一样的

1postgres=# select fn8();
2 fn8
3-----
4 abc
5(1 row)

是不是感觉 PostgreSQL 的函数与存储过程越发统一起来的; 从某种意义来说，函数可以认为是第一个参数为 OUT 类型的存储过程。函数中返回值即是一个 OUT 参数，函数用 return 语句，存储过程则是为 OUT 类型参数赋值来返回值。

如果是多个 OUT 参数的函数，创建时 PostgreSQL 会要求函数的返回类型为 record, 由此可推断存储过程的 OUT 参数相当于转换成函数后的返回值

带 OUT 参数的存储过程

1create or replace procedure pr1(x out varchar, y out varchar)
2  language plpgsql as $$
3begin
4   x := 'aa';
5   y := 'bb';
6end
7$$

对存储过程不能用 select, insert 等语句调用了，需用 call

1postgres=# call pr1('k1', 'k2');
2 x  | y
3----+----
4 aa | bb
5(1 row)

返回每一个 OUT 参数值，OUT 参数可以任何类型，比如 refcursor 游标类型，看下例

OUT 游标类型的参数存储过程

1create or replace procedure pr2(out p_cursor1 refcursor, out p_cursor2 refcursor)
2   language plpgsql as $$
3begin
4    open p_cursor1 for select oid, datname from pg_database;
5    open p_cursor2 for select * from pg_database;
6end;
7$$

和函数的使用方式差不多，只是改成了 call

 1postgres=# begin;
 2BEGIN
 3postgres=*# call pr2('k1', 'k2');
 4     p_cursor1      |     p_cursor2
 5--------------------+--------------------
 6 <unnamed portal 3> | <unnamed portal 4>
 7(1 row)
 8
 9postgres=*# fetch all in "<unnamed portal 3>";
10 oid |  datname
11-----+-----------
12   5 | postgres
13   1 | template1
14   4 | template0
15(3 rows)

返回 void 的函数

我们再由存储过程退回到返回 void 类型的函数

1create or replace function fn10(p_id integer)
2  returns void
3  language plpgsql as $$
4begin
5  insert into t1 values(3, 'c');
6end
7$$

我们要 return void 的函数作什么用呢，唯一的好处就是可以用 select/insert 等语句调用，不用 call

1postgres=# select fn10(2);
2 fn10
3------
4
5(1 row)

仅此而已。

PostgreSQL 的函数与存储过程那种你泥中有我，我泥中有你的关系，看似方便了用 select 等语句调用，其实却苦了 JDBC 来调用方式

JDBC 在使用函数时可以两种方式

用 Statement 或 PreparedStatement 像普通 SQL 语句那样 execute(), executeQuery(), executeUpdate(), 或 executeBatch(). 可以 executeBatch() 操作是胜过存储过程的地方
或用 CallableStatement, 通过 { ? = call fn(?, ?)}, 不关必返回值的话可以像调用存储过程一样 {call fn(?, ?)}

JDBC 使用存储过程要用 CallableStatement 的 call fn(?, ?) 来执行, 注意无需两边的大括号

由于 executeBatch() 是声明在 Statement 类中，executeBatch() 函数只返回受影响的行数，无法获得函数的返回值或存储过程的 OUT 参数。因些，在不关心返回值(函数的返回值或存储过程的 OUT 参数)时, 应该可以 batch 来执行函数和存储过程。

下面是 JDBC 以各种花式调用函数和存储过程的汇总代码, 请对照所调用的 PostgreSQL 函数与存储过程的代码。为完整演示，再补一个表和函数

 1create table t1(c1 integer, c2 varchar(16));
 2
 3create or replace function fn11(p_c1 integer, p_c2 varchar)
 4  returns void
 5  language plpgsql
 6  as $$
 7begin
 8    insert into t1(c1, c2) values(p_c1, p_c2);
 9end
10$$

若忽略该函数的返回值则可当作存储过程来调用

 1public class TestPostgres {
 2    public static void main(String[] args) throws SQLException {
 3        selectFunctionGetReturnedSingleValue(createConnection());
 4        selectFunctionGetReturnedCursor(createConnection());
 5        callFunctionGetReturnedCursor(createConnection());
 6        callProcedureGetReturnedPrimitiveValues(createConnection());
 7        callProcedureGetReturnedCursor(createConnection());
 8        callFunctionIgnoreReturn(createConnection());
 9        batchCallFunctionOrProcedure(createConnection());
10    }
11
12    private static Connection createConnection() throws SQLException {
13        return DriverManager.getConnection("jdbc:postgresql://localhost/postgres", "postgres", "mysecretpassword");
14    }
15
16    private static void selectFunctionGetReturnedSingleValue(Connection conn) throws SQLException {
17        PreparedStatement pstmt = conn.prepareStatement("select fn1(?,?)");
18        pstmt.setInt(1, 100);
19        pstmt.setBigDecimal(2,  BigDecimal.valueOf(3.5));
20        ResultSet rs = pstmt.executeQuery();
21        while (rs.next()) {
22            System.out.println(rs.getObject(1));  // 6.5
23        }
24    }
25
26    private static void selectFunctionGetReturnedCursor(Connection conn) throws SQLException {
27        conn.setAutoCommit(false);
28        PreparedStatement pstmt = conn.prepareStatement("select fn2()");
29        ResultSet rs = pstmt.executeQuery();
30        while (rs.next()) {
31            ResultSet rs1 = (ResultSet) rs.getObject(1);
32            while (rs1.next()) {
33                System.out.println(rs1.getObject(1)); // 5, 1, 4
34            }
35        }
36    }
37
38    private static void callFunctionGetReturnedCursor(Connection conn) throws SQLException {
39        conn.setAutoCommit(false);
40        CallableStatement call = conn.prepareCall("{? = call fn2()}");
41        call.registerOutParameter(1, Types.REF_CURSOR);
42        call.execute();  // or call.executeUpdate();
43        ResultSet rs = call.getObject(1, ResultSet.class);
44        while (rs.next()) {
45            System.out.println(rs.getObject(1)); // 5, 1, 4
46        }
47    }
48
49    private static void callProcedureGetReturnedPrimitiveValues(Connection conn) throws SQLException {
50        CallableStatement call = conn.prepareCall("call pr1(?, ?)");
51        //  调存储过程不能写成 {call pr1(?, ?)}, 否则出下面的错误
52        // org.postgresql.util.PSQLException: ERROR: pr1() is a procedure
53        //  Hint: To call a procedure, use CALL.
54
55        call.registerOutParameter(1, Types.VARCHAR);
56        call.registerOutParameter(2, Types.VARCHAR);
57        call.execute();
58        System.out.println(call.getObject(1)); // aa
59        System.out.println(call.getObject(2)); // bb
60    }
61
62    private static void callProcedureGetReturnedCursor(Connection conn) throws SQLException {
63        conn.setAutoCommit(false);
64        CallableStatement call = conn.prepareCall("call pr2(?, ?)");
65        call.registerOutParameter(1, Types.REF_CURSOR);
66        call.registerOutParameter(2, Types.REF_CURSOR);
67        call.execute();
68        ResultSet rs = call.getObject(1, ResultSet.class);
69        while (rs.next()) {
70            System.out.println(rs.getObject(1)); // 5, 1, 4
71        }
72    }
73
74    private static void callFunctionIgnoreReturn(Connection conn) throws SQLException {
75        CallableStatement call = conn.prepareCall("{call fn2()}");
76        call.execute();
77    }
78
79    private static void batchCallFunctionOrProcedure(Connection conn) throws SQLException {
80        CallableStatement call = conn.prepareCall("{call fn11(?, ?)}");
81
82        call.setInt(1, 101);
83        call.setString(2, "aa");
84        call.addBatch();
85
86        call.setInt(1, 102);
87        call.setString(2, "bb");
88        call.addBatch(); // 插入了 (101, 'aa'), (102, 'bb')
89
90        call.executeBatch();
91
92        call.clearBatch();
93
94        call.setInt(1, 104);
95        call.setString(2, "dd");
96        call.addBatch();
97        call.executeBatch(); // 插入了 (104, 'dd')
98    }
99}

总结几点

select fn() 调用可由 stmt.executeQuery() 得到结果，不管是函数还是存储过程
call.registerOutParameter() 注册的参数须由 call.getXxx() 获得输出值。用了 call xxx 的方式就不能由 stmt.executeQuery() 获得记录集
无论是调用函数还是存储过程，如果返回值或 OUT 参数是游标(REF_CURSOR) 的话，需开启事物(conn.setAutoCommit(false)
如果返回值或 OUT 参数不是游标(REF_CURROR)，无需开启事物即能获得结果
调用存储时不要写两边的大括号，即 call pr2(?, ?) ，不能写成 {call pr2(?,?)}
如果把函数当作存储过程来调用，忽略它的返回值的话，也可以写成 call fn2(), 在用 prepareCall() 时，看是否有 {} 来区别函数还是存储过程
update/insert/delete 可以 batchUpdate, select fn() 也可以 batchUpdate, 如果忽略函数的返回值或调用存储过程时也可以 batchUpdate

如果是使用 JdbcTemplate 的系列 execute(), query(), update(), batchUpdate() 操作的话请参考原始的 JDBC 操作。永久链接 https://yanbin.blog/postgresql-function-procedure-application/, 来自隔叶黄莺 Yanbin's Blog
[版权声明]

本文采用署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 进行许可。