9个PostgreSQL存储过程示例

存储过程是一组结构化的查询和语句，例如控制语句和声明。这里介绍9个在不同情况下很有用的存储过程示例。

创建测试表：

create table public.test1(Genreid int,name varchar(10));

1. 使用存储过程插入数据

CREATE OR REPLACE PROCEDURE genre_insert_data(GenreId integer, Name character varying)
LANGUAGE SQL
AS 
$$
 INSERT INTO public.test1 VALUES (GenreId, Name);
$$;

测试：

pgtest=# CREATE OR REPLACE PROCEDURE genre_insert_data(GenreId integer, Name character varying)
pgtest-# LANGUAGE SQL
pgtest-# AS 
pgtest-# $$
pgtest$#  INSERT INTO public.test1 VALUES (GenreId, Name);
pgtest$# $$;
CREATE PROCEDURE
pgtest=# call genre_insert_data(1,'qqq');
CALL
pgtest=# select * from test1;
 genreid | name 
---------+------
       1 | qqq
(1 row)

2. 在屏幕上显示消息

 CREATE OR REPLACE PROCEDURE display_message (INOUT msg TEXT)
 AS $$
 BEGIN
 RAISE NOTICE 'Procedure Parameter: %', msg ;
 END ;
 $$
 LANGUAGE plpgsql ;

测试：

pgtest=#  CREATE OR REPLACE PROCEDURE display_message (INOUT msg TEXT)
pgtest-#  AS $$
pgtest$#  BEGIN
pgtest$#  RAISE NOTICE 'Procedure Parameter: %', msg ;
pgtest$#  END ;
pgtest$# $$
pgtest-#  LANGUAGE plpgsql ;
CREATE PROCEDURE
pgtest=# call display_message('test_message!!!!');
NOTICE:  Procedure Parameter: test_message!!!!
       msg        
------------------
 test_message!!!!
(1 row)

3.使用事务控制

 CREATE OR REPLACE PROCEDURE control_transaction()
 LANGUAGE plpgsql
 AS $$
 DECLARE
 BEGIN
   CREATE TABLE test2 (id int);
   INSERT INTO test2 VALUES (1);
   COMMIT;
   CREATE TABLE test3 (id int);
   INSERT INTO test3 VALUES (1);
   ROLLBACK;
 END $$;

测试：

pgtest=#  CREATE OR REPLACE PROCEDURE control_transaction()
pgtest-#  LANGUAGE plpgsql
pgtest-#  AS $$
pgtest$#  DECLARE
pgtest$#  BEGIN
pgtest$#    CREATE TABLE test2 (id int);
pgtest$#    INSERT INTO test2 VALUES (1);
pgtest$#    COMMIT;
pgtest$#    CREATE TABLE test3 (id int);
pgtest$#    INSERT INTO test3 VALUES (1);
pgtest$#    ROLLBACK;
pgtest$#  END $$;
CREATE PROCEDURE
pgtest=# select * from test2;
ERROR:  relation "test2" does not exist
LINE 1: select * from test2;
                      ^
pgtest=# call control_transaction();
CALL
pgtest=# select * from test2;
 id 
----
  1
(1 row)
pgtest=# select * from test3;
ERROR:  relation "test3" does not exist
LINE 1: select * from test3;
                      ^

在这里我们可以看到提交之前的数据是可用的，但是没有提交和回滚的数据会从数据库中删除。

4.使用列数据类型

 CREATE OR REPLACE PROCEDURE genre_id_max() LANGUAGE plpgsql AS $$
 DECLARE
 id test1.GenreId%type;
 BEGIN
 select max(GenreId) into id from public.test1;
 RAISE NOTICE 'Maximum of GenreId is : %', id ;
 END;
 $$ ;

测试：

pgtest=#  CREATE OR REPLACE PROCEDURE genre_id_max() LANGUAGE plpgsql AS $$
pgtest$#  DECLARE
pgtest$#  id test1.GenreId%type;
pgtest$#  BEGIN
pgtest$#  select max(GenreId) into id from public.test1;
pgtest$#  RAISE NOTICE 'Maximum of GenreId is : %', id ;
pgtest$#  END;
pgtest$#  $$ ;
CREATE PROCEDURE
pgtest=# call genre_id_max();
NOTICE:  Maximum of GenreId is : 1
CALL

5. 发出NOTICE、WARING和 INFO 消息

 CREATE OR REPLACE PROCEDURE raise_warning() AS $$
 DECLARE
 warn INT := 10;
 BEGIN
 RAISE NOTICE 'value of warn : % at %: ', warn, now();
 warn := warn + 10;
 RAISE WARNING 'value of warn : % at %: ', warn, now();
 warn := warn + 10;
 RAISE INFO 'value of warn : % at %: ', warn, now();
 END;
 $$
 LANGUAGE plpgsql;

测试：

pgtest=#  CREATE OR REPLACE PROCEDURE raise_warning() AS $$
pgtest$#  DECLARE
pgtest$#  warn INT := 10;
pgtest$#  BEGIN
pgtest$#  RAISE NOTICE 'value of warn : % at %: ', warn, now();
pgtest$#  warn := warn + 10;
pgtest$#  RAISE WARNING 'value of warn : % at %: ', warn, now();
pgtest$#  warn := warn + 10;
pgtest$#  RAISE INFO 'value of warn : % at %: ', warn, now();
pgtest$#  END;
pgtest$#  $$
pgtest-#  LANGUAGE plpgsql;
CREATE PROCEDURE
pgtest=# call raise_warning();
NOTICE:  value of warn : 10 at 2022-05-07 18:54:41.138976+08: 
WARNING:  value of warn : 20 at 2022-05-07 18:54:41.138976+08: 
INFO:  value of warn : 30 at 2022-05-07 18:54:41.138976+08: 
CALL

6. 引发异常

 CREATE OR REPLACE PROCEDURE genre_id_exception() LANGUAGE plpgsql AS $$
 DECLARE
 id test1.GenreId%type;
 BEGIN
 select max(GenreId) into id from public.test1;
 RAISE EXCEPTION 'Maximum of GenreId is : %', id  USING HINT = 'Test For Raising exception.';
 END;
 $$ ;

测试：

pgtest=# CREATE OR REPLACE PROCEDURE genre_id_exception() LANGUAGE plpgsql AS $$
pgtest$#  DECLARE
pgtest$#  id test1.GenreId%type;
pgtest$#  BEGIN
pgtest$#  select max(GenreId) into id from public.test1;
pgtest$#  RAISE EXCEPTION 'Maximum of GenreId is : %', id  USING HINT = 'Test For Raising exception.';
pgtest$#  END;
pgtest$#  $$ ;
CREATE PROCEDURE
pgtest=# call genre_id_exception();
ERROR:  Maximum of GenreId is : 1
HINT:  Test For Raising exception.
CONTEXT:  PL/pgSQL function genre_id_exception() line 6 at RAISE

7. 使用 FOR 循环遍历表中的数据

 CREATE OR REPLACE PROCEDURE genre_traverse() LANGUAGE plpgsql AS $$
 DECLARE
 genre_rec record;
 BEGIN
 for genre_rec in (select GenreId,Name from public.test1 order by GenreId)
   loop
 RAISE NOTICE 'Genre Id is : % , Name is : %', genre_rec.GenreId,genre_rec.Name;
   end loop;
 END;
 $$ ;

测试：

pgtest=# CREATE OR REPLACE PROCEDURE genre_traverse() LANGUAGE plpgsql AS $$
pgtest$#  DECLARE
pgtest$#  genre_rec record;
pgtest$#  BEGIN
pgtest$#  for genre_rec in (select GenreId,Name from public.test1 order by GenreId)
pgtest$#    loop
pgtest$#  RAISE NOTICE 'Genre Id is : % , Name is : %', genre_rec.GenreId,genre_rec.Name;
pgtest$#    end loop;
pgtest$#  END;
pgtest$#  $$ ;
CREATE PROCEDURE
pgtest=# call genre_traverse();
NOTICE:  Genre Id is : 1 , Name is : qqq
CALL

8. 使用 SECURITY INVOKER

SECURITY INVOKER 指示该过程将以调用它的用户的权限执行。这是默认设置。

 CREATE OR REPLACE PROCEDURE genre_traverse() LANGUAGE plpgsql SECURITY INVOKER
 AS $$
 DECLARE
 genre_rec record;
 BEGIN
 for genre_rec in (select GenreId,Name from public.test1 order by GenreId)
   loop
 RAISE NOTICE 'Genre Id is : % , Name is : %', genre_rec.GenreId,genre_rec.Name;
   end loop;
 END;
 $$ ;

测试：

pgtest=# create user test;
CREATE ROLE
pgtest=# revoke all on schema public from test;
REVOKE
pgtest=#  CREATE OR REPLACE PROCEDURE genre_traverse() LANGUAGE plpgsql SECURITY INVOKER
pgtest-#  AS $$
pgtest$#  DECLARE
pgtest$#  genre_rec record;
pgtest$#  BEGIN
pgtest$#  for genre_rec in (select GenreId,Name from public.test1 order by GenreId)
pgtest$#    loop
pgtest$#  RAISE NOTICE 'Genre Id is : % , Name is : %', genre_rec.GenreId,genre_rec.Name;
pgtest$#    end loop;
pgtest$#  END;
pgtest$#  $$ ;
CREATE PROCEDURE
pgtest=# \c - test
You are now connected to database "pgtest" as user "test".
pgtest=> call genre_traverse();
ERROR:  permission denied for table test1
CONTEXT:  SQL statement "(select GenreId,Name from public.test1 order by GenreId)"
PL/pgSQL function genre_traverse() line 5 at FOR over SELECT rows

9. 使用SECURITY DEFINER

SECURITY DEFINER 指定该过程将以拥有它的用户的权限执行。SECURITY DEFINER 过程不能执行事务控制语句（例如，COMMIT 和 ROLLBACK，取决于语言）。

在此示例中，我们使用用户“postgres”创建了一个存储过程，并使用无权访问该表的“test”用户调用它。

 CREATE OR REPLACE PROCEDURE genre_traverse() LANGUAGE plpgsql SECURITY DEFINER
 AS $$
 DECLARE
 genre_rec record;
 BEGIN
 for genre_rec in (select GenreId,Name from public.test1 order by GenreId)
   loop
 RAISE NOTICE 'Genre Id is : % , Name is : %', genre_rec.GenreId,genre_rec.Name;
   end loop;
 END;
 $$ ;

测试：

pgtest=# CREATE OR REPLACE PROCEDURE genre_traverse() LANGUAGE plpgsql SECURITY DEFINER
pgtest-#  AS $$
pgtest$#  DECLARE
pgtest$#  genre_rec record;
pgtest$#  BEGIN
pgtest$#  for genre_rec in (select GenreId,Name from public.test1 order by GenreId)
pgtest$#    loop
pgtest$#  RAISE NOTICE 'Genre Id is : % , Name is : %', genre_rec.GenreId,genre_rec.Name;
pgtest$#    end loop;
pgtest$#  END;
pgtest$#  $$ ;
CREATE PROCEDURE
pgtest=# \c - test
You are now connected to database "pgtest" as user "test".
pgtest=> call genre_traverse();
NOTICE:  Genre Id is : 1 , Name is : qqq
CALL

参考文章：

https://www.enterprisedb.com/postgres-tutorials/10-examples-postgresql-stored-procedures