首页 » PostgreSQL/GaussDB » Language SQL和Plpgsql在Postgresql Functions 的区别

Language SQL和Plpgsql在Postgresql Functions 的区别

  • 2022/10/14
  • PostgreSQL/GaussDB
  • 242 views
  • Language SQL和Plpgsql在Postgresql Functions 的区别已关闭评论

上在一篇中测试看到postgresql中的function相同的逻辑，仅是create function时language在sql和plpgsql不同产生了不同的执行计划， 是因为postgresql是选择了不同的 SQL query “engine”， Postgresql支持多种不同的”runtime engines”执行不同的code，在Postgresql中创建function时语言支持SQL, PL/pgSQL, C ，C，Perl, Python, Ruby, Java 等，这是好多其它数据库不具备的，本篇总结一下在create function是的language在SQL和plpgsql之间的不同。

以下情况选择SQL language更好：

  • 1, code逻辑一个纯SQL，短代码,没有查询block开销
  • 2，一个标量查询，返回查询结果
  • 3，SQL是有database server执行
  • 4，执行一个DML不返回任何数据，returns void
  • 5，会话单个调用
  • 6，在较大查询的上下文中调用，并且足够简单，可以内联。

以下情况选择PlpgSQL language更好：

  • 1， 一种过程语言，在语句块中处理更多的逻辑
  • 2， 在一个会话中使用prepared statement所有的query plans可以cached，会话中多次调用
  • 3, 需要SQL函数中没有的过程元素或变量时，如variables, loops, cursors, transaction command
  • 4, 执行动态dynamic SQL
  • 5， 记算结果被多次重用时
  • 6， 需要捕捉错误时
  • 7， trigger functions
  • 8， 当包含DDL语句时，——因为SQL函数中的所有语句都是一次性解析的，而PL/pgSQL函数则按顺序计划并执行每个语句

例如：取分区名函数

highgo=# CREATE OR REPLACE FUNCTION sales_partition_function(date)
highgo-# RETURNS TEXT AS $$
highgo$# DECLARE
highgo$#     partition_name TEXT;
highgo$# BEGIN
highgo$#     partition_name := 'sales_' || to_char($1, 'YYYY_MM');
highgo$#     RETURN partition_name;
highgo$# END;
highgo$# $$ LANGUAGE plpgsql;
CREATE FUNCTION


highgo=# CREATE OR REPLACE FUNCTION sales_partition_function1(partition_date DATE)
highgo-# RETURNS TEXT
highgo-# AS $$
highgo$# SELECT 'sales_' || to_char(partition_date, 'YYYY_MM');
highgo$# $$ LANGUAGE sql;
CREATE FUNCTION

highgo=# select sales_partition_function1(now()::date);
 sales_partition_function1
---------------------------
 sales_2024_12

highgo=#  select sales_partition_function(now()::date);
 sales_partition_function
--------------------------
 sales_2024_12

highgo=# explain (verbose) select sales_partition_function1(now()::date);
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Result  (cost=0.00..0.02 rows=1 width=32)
   Output: ('sales_'::text || to_char(((now())::date)::timestamp with time zone, 'YYYY_MM'::text))
(2 rows)

highgo=# explain (verbose) select sales_partition_function(now()::date);
                    QUERY PLAN
---------------------------------------------------
 Result  (cost=0.00..0.27 rows=1 width=32)
   Output: sales_partition_function((now())::date)
(2 rows)

SQL是个查询语言，没有过程元素，如if, while, FUNCTION是包装SQL的一种方式，又可以使用不同的query “engine”，不同混合使用SQL或过程元素，需要在执行时切换”engine”,所以在create function时可以指定不同的language.用户提前声明，服务器知道以sql, plpgsql,python,C等引擎去执行。和在oracle中以DECLARE 开始声明，表未是一个匿名PLSQL 块一样， 需要服务器以不同的runtime engine去执行，因为MySQL中没有这东西，所以要创建存储过程或function。唯一不能清楚区分过程代码和“普通SQL”的DBMS产品是SQL Server的T-SQL支持混用。

据说在PostgreSQL:同样的请求，plpgsql语言比sql慢可以使用auto_explain 插件测试,配置postgresql.conf:

shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 0
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_nested_statements = on
track_functions = 'pl'

重启数据库，查看从SQL函数和PL/pgSQL函数调用语句时的执行计划和执行时间，看看是否可以发现差异。然后比较pg_stat_user_functions中的执行时间，比较函数的执行时间。

 

— reference https://www.itcodar.com/sql/difference-between-language-sql-and-language-plpgsql-in-postgresql-functions.html

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：