--PostgreSQL 的存储过程怎么写 与 质疑
--PostgreSQL 的存储过程怎么写 与 质疑
PostgreSQL 的存储过程在POSTGRESQL 11 有了改变，从统一的 create function 到 create procedure 到底能从中获得什么
1 支持了commit 和 rollback
2 帮助怎么从oracle 迁移到 postgresql 的便利性
3 符合更多传统数据库的用户（ORACLE SQL SERVER MYSQL）
最近一段反思了一下为什么MYSQL中在大型应用程序没有存储过程这个词，总结有三
1 MYSQL 本身不支持复杂的查询语句 （我没有说 mysql 8）
2 存储过程是一段SQL语句的集合，处理一些程序处理比较困难和麻烦的功能，修改程序更加灵活
3 业务比较简单，复杂的可以上移到程序层
那不支持存储过程到底好不好，这的见仁见智了，在说下去估计就有人不欢喜了。
Ok 我们的回到PG的存储过程，在PG11 之前也是没有存储过程的，但又一种变通的方式 create function 。PG 11 支持了比较完整的存储过程，但网上的资料如果是中文的，属于残壁断垣，英文的其实也没有多少，估计大部分PG的使用环境还在 PG 9.4 , PG 10这个level。
关于存储过程的优点：PG 官方也给了以下内容（其实这是存储过程的优点）
客户端和服务端之间额外的传输将被取消. 客户端不需要也不用客户端和服务端之间进行封装或者传输的中间结果。 可以避免命令的多轮解析在目前的PG11 中如果你要返回一个表的查询内容，从现在11到PG12，目前存储过程 是无法满足这个需求的。目前如果要通过函数来完成在一段PLPGSQL中输出某个表的集合。
CREATE OR REPLACE function insert_data(type varchar(10), content text)
RETURNS TABLE( id int, log_type varchar(10), log_content text, insert_date timestamp)
LANGUAGE plpgsql
AS $$
BEGIN
if( to_regclass('public.log_save') is null ) then
CREATE TABLE public.log_save
(
id serial,
log_type character varying(10),
log_content text,
insert_date timestamp without time zone,
PRIMARY KEY (id)
)
WITH (
OIDS = FALSE,
FILLFACTOR = 80,
autovacuum_enabled = TRUE
);
ALTER TABLE public.log_save OWNER to admin;
end if;
insert into public.log_save (log_type,log_content,insert_date) value
(type,content,now());
RETURN QUERY select * from public.log_save;
END;
$$;
select insert_data ('simple','this is test log');函数是通过 select 的方式来调用的。 上面是目前可以通过函数来完成调用表结果集的方式。 通过查阅资料，目前postgresql 的存储过程和函数之间的区别可以总结成1 存储过程中可以包含commit rollback2 函数可以有return 返回值输出3 存储过程支持 savepoint 功能
CREATE OR REPLACE PROCEDURE insert_D (type varchar(10),content text)
LANGUAGE plpgsql
AS $$
BEGIN
if( to_regclass('public.log_save') is null ) then
CREATE TABLE public.log_save
(
id serial,
log_type character varying(10),
log_content text,
insert_date timestamp without time zone,
PRIMARY KEY (id)
)
WITH (
OIDS = FALSE,
FILLFACTOR = 80,
autovacuum_enabled = TRUE
);
ALTER TABLE public.log_save OWNER to admin;
end if;
insert into public.log_save (log_type,log_content,insert_date) values (type,content,now());
rollback;
insert into public.log_save (log_type,log_content,insert_date) values (type,content,now());
commit;
--Drop table public.log_save
END;
$$;
call insert_D('a','b');
select * from log_save;
当然在POSTGRESQL 11 开始有的存储过程，在查看一些建议和资料的时候，有以下一些需要注意的地方
1 在创建trigger 中目前如果想在触发后，如果执行函数或存储过程，建议延续之前版本，继续使用函数的方式
2 在有事务断点，或者需要设置回滚或者保存点的大型的SQL 程序集的情况下，建议使用 PROCEDURE
当然也有部分人对POSTGRESQL 的存储过程提出了异议，说他根本没有像其他的数据库有事务性，我们来看下面一段存储过程
CREATE OR REPLACE PROCEDURE check_now()
AS $$
DECLARE
i int;
BEGIN
FOR i in 1..5 LOOP
RAISE NOTICE 'It is now: %', now();
PERFORM txid_current();
COMMIT;
PERFORM pg_sleep(0.1);
END LOOP;
END;
$$
LANGUAGE plpgsql;结果如下，质疑在哪里，有些人认为在一个事务中表达的时间是一样的，而不应该是下面结果中的每运行一次就会更新一个时间。
个人觉得肯能是把 MYSQL 中的 NOW() 和 SYSDATE() 之间的事情搬弄到
PG中了。
我们在来一个存储过程
CREATE OR REPLACE PROCEDURE check_now1()
AS $$
DECLARE
i int;
BEGIN
FOR i in 1..5 LOOP
RAISE NOTICE 'It is now: %', now();
PERFORM txid_current();
RAISE NOTICE 'YES:i%',transaction_timestamp();
PERFORM pg_sleep(0.1);
RAISE NOTICE 'NO:i%',statement_timestamp();
COMMIT;
END LOOP;
END;
$$
LANGUAGE plpgsql;
你可以看到类似 MYSQL 中的 NOW() 与 SYSDATE() 之间的不同
目前PG的存储过程，如果算上函数的情况下，其实PG的存储过程和函数在使用中和 ORACLE , SQL SERVER 在功能上是不相上下的，但其实不然，PG的存储过程可能是使用 C 语言， PYTHON, 或其他可以接入的语言撰写，我们可以发现很多的PG的系统函数都是使用 C语言撰写的，那这样的语言执行的速度一定是要比传统的存储过程要快，所以PG 在存储过程上其实对程序员更友好，撰写存储过程可能是程序员的苦恼的地方，但如果换成他熟悉的语言，相比会轻松许多。
本文分享自 AustinDatabases 微信公众号，前往查看
如有侵权，请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体分享计划 ，欢迎热爱写作的你一起参与！