POSTGRESQL 存储过程--如何写出新版本PG的存储过程的小案例

随着问问题的同学越来越多，公众号内部私信回答问题已经很困难了，所以建立了一个群，关于各种数据库的问题都可以，目前主要是 POSTGRESQL, MYSQL ,MONGODB ,POLARDB ,REDIS，SQL SERVER 等，期待你的加入，

最近在开始研究POSTGRESQL 的存储过程，主要的原因有以下几个

1 因为要开发适合目前公司中的基于POSTGRESQL 的运行维护产品，同时基于POSTGRESQL 的数据库有云数据库，基于程序的安全性和部署的便利性，一部分维护的程序应该以存储过程的方式，被部署在数据库中，方便外部程序调用。

2  基于POSTGRESQL 大部分的存储过程的教学内容还是在create function部分 ，在POSTGRESQL  11 后的版本的数据库的存储过程已经不再使用create function,而采用 create procedure 的方式撰写，功能和扩展性提高了

所以需要针对POSTGRESQL 的存储过程进行一个详细的研究，看看怎么更好的为以后的工作服务。

CREATE [ OR REPLACE ] PROCEDURE
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
    | sql_body

} ...

这是官方的procedure 的固定语法这里需要注意第一个地方

1  POSTGRESQL 的存储过程和函数可以是一个名字，只要后面的给定的参数不一致即可，也就是有一部分可能性存储过程和函数的名字是一样的。但我们强烈建议不要这样做。

2   存储过程中的 argmode 部分可以选择的参数有 in ,out, inout 或者  variadic 默认是in, 后面我们通过一些案例来看看如何进行这些模式的使用。

3  argname  argtype  这是存储过程中的参数名和参数的类型设置的

4  SECURITY INVOKER 为设置执行存储过程的用户的权限来调用存储过程

案例  1  

create or replace procedure dba_insert_data("id" int,"name" varchar(20))
language sql 
as $$
insert into public.insert_d (id,name) values ("id","name"); 
$$;


call dba_insert_data(1,'pop');

select * from insert_d;

存储过程中将参数输入的部分，很简单，默认就是输入，将输入的参数和参数的类型标注即可，并且注意参数用双引号标志即可。

案例2

带有输出参数的信息和如何将信息展示在存储过程运行期间，如何将输入的参数在进行输出

create or replace procedure dba_insert_data("id" int,"name" varchar(20),INOUT msg text)
language plpgsql 
as $$
BEGIN
insert into public.insert_d (id,name) values ("id","name");

RAISE NOTICE 'insert data process is done:%',msg;
END;
$$;


call dba_insert_data(1,'pop');

这里语法 RAISE NOTICE 是输出消息的语句 ，而通过百分号 % 来代表输入变量，然后在输出时标名变量即可。

案例 3

将存储过程中的表的字段值输出到存储过程的外部，这就需要在定义存储过程中先定义这个表的这个字段。

create or replace procedure dba_insert_data("id_in" int,"name" varchar(20))
language plpgsql 
as $$

declare exid "insert_d"."id"%type;

BEGIN
insert into public.insert_d (id,name) values ("id_in","name");

select id into exid from public.insert_d ;

RAISE NOTICE 'insert data id is:%',exid;

END;
$$;

truncate table insert_d;

call dba_insert_data(1,'pop');

在这个案例中，我们定义了接受表中字段值的变量 exid ，同时将这个值输出到 message中。这里需要注意几个地方

1  请不要将变量名和字段名一致，否则会报无法定位的问题

2   查询的值必须是一个值，如果出现多行值也会报错，无法赋值的问题

案例 4  需要将表中的查询的多个值进行展示

这个问题与上面的问题类似，上面的第三个案例只能展示一行值，并且出现多行结果，会报错，而大概率的情况下，怎么处理多行值的问题就在第四个案例中展示。

create or replace procedure dba_insert_data("id_in" int,"name_in" varchar(20))
language plpgsql 
as $$

declare table_record record;

BEGIN
insert into public.insert_d (id,name) values ("id_in","name_in");
     
     for table_record in (select id,name from public.insert_d)
         
         loop 
             RAISE INFO 'insert data id is:%,%',table_record."id",table_record."name";
             
         end loop;

END;
$$;

案例 5  权限问题，一般在建立存储过程的时候，创建者都拥有操作这个存储过程中的OBJECT 的权限，而执行者一般不见得有所有这个存储过程中需要的OBJECT的权限，就会产生一个问题，执行者执行存储过程无法通过。所以我们就需要通过  security definer 来指定拥有存储过程的特权用户来操作这个存储过程。

下面我们通过两个不同的security 方式来进行测试

1  使用security definer 这里是采用建立这个存储过程的用户的权限来调用这个存储过程，而不会使用执行者的权限来操作这个存储过程。

而如果我们通过其  security invoker 的方式来定义存储过程，并且使用一个没有权限操作存储过程中OBJECT 的权限的用户来操作存储过程，则问题就会像下面的结果一样报错，并告知没有相关的权限来操作存储过程。

以上仅仅是一些简单的例子和一些比较常见的存储过程的用法，其实有点好笑的是，在现在云原生和分布式数据库爆发的年代，我们还在继续使用存储过程来操作数据库，好像也没有什么错。

以上