PostgreSQL plpgsql_check_function 插件 - plpgsql函数体内容CHECK

背景

PostgreSQL的plpgsql存储过程语言是解释性语言，支持语法型的CHECK但是无法发现一些运行时才可能触发的报错，比如表不存在等报错。当然这种报错实际上也不应该PLPGSQL来做，不过我们现在有plpgsql check extension来帮助发现这些问题了。

postgres=# create or replace function f(int) returns void as $$  
declare  
begin  
  if $1 =1 then
    perform 1 from t12333333;  
  else
    perform 1 from test;
  end if;
end;  
$$ language plpgsql strict;  
CREATE FUNCTION  


postgres=# \d t12333333  
Did not find any relation named "t12333333".  

例如以上函数，t12333333表实际上是不存在的，但是创建plpgsql时可以通过。而且在某些查询下也不会报错，只有输入某些条件时才会报错。

通过plpgsql_check可以发现类似的问题。

postgres=# create extension plpgsql_check;
CREATE EXTENSION
postgres=# select * from plpgsql_check_function_tb('f(int)');
 functionid | lineno | statement | sqlstate |               message               | detail | hint | level | position |          query          | context 
------------+--------+-----------+----------+-------------------------------------+--------+------+-------+----------+-------------------------+---------
 public.f   |      5 | PERFORM   | 42P01    | relation "t12333333" does not exist |        |      | error |       15 | SELECT 1 from t12333333 | 
(1 row)

plpgsql_check插件还有更多的功能，主动检查、被动检查等等，具体请参考plpgsql_check的手册。

https://pgxn.org/dist/plpgsql_check/

https://github.com/okbob/plpgsql_check

old content

早上看到一篇关于PostgreSQL plpgsql函数检查的文章, 说的是PostgreSQL函数关于变量这块, 在创建时不会检查变量的模糊定义, 如表中和函数中定义了同名的变量, 在定义函数时不会报错, 但是在使用中如果出现模糊概念了, 将或报错.

PostgreSQL 9.3 将会引入一个plpgsql_check_function函数, 用来检测诸如此类的错误.

实际上还没有引入到主分支，但是这个插件可以在PGXN或GITHUB下载。

https://pgxn.org/dist/plpgsql_check/

https://github.com/okbob/plpgsql_check

请以安装插件的形式安装，下面的例子是未合并的PATCH，不要使用，可以参考用法和效果。

例子

下面来以9.2的源码为例子, 打上这个补丁

1. 首先下载PostgreSQL 9.2的源码, 以及补丁

wget http://ftp.postgresql.org/pub/source/v9.2.0beta2/postgresql-9.2beta2.tar.bz2  
wget http://archives.postgresql.org/pgsql-hackers/2012-06/binooyMWTCfxi.bin  

2. 打上补丁

# tar -jxvf postgresql-9.2beta2.tar.bz2  
# cd postgresql-9.2beta2  
# patch -p1 < ../binooyMWTCfxi.bin   
patching file doc/src/sgml/plpgsql.sgml  
patching file src/pl/plpgsql/src/Makefile  
patching file src/pl/plpgsql/src/pl_check.c  
patching file src/pl/plpgsql/src/pl_comp.c  
patching file src/pl/plpgsql/src/pl_exec.c  
patching file src/pl/plpgsql/src/pl_handler.c  
patching file src/pl/plpgsql/src/plpgsql--1.0--1.1.sql  
patching file src/pl/plpgsql/src/plpgsql--1.1.sql  
patching file src/pl/plpgsql/src/plpgsql.control  
patching file src/pl/plpgsql/src/plpgsql.h  
patching file src/test/regress/expected/plpgsql.out  
patching file src/test/regress/sql/plpgsql.sql  

3. 编译安装

cd postgresql-9.2beta2  
./configure --prefix=/home/pg92/pgsql --with-pgport=9999 --with-perl --with-python --with-tcl --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety --with-wal-blocksize=16 --enable-debug && gmake  
sudo gmake install  

4. 测试

创建测试表

postgres=# create table test (id int,info text);  
CREATE TABLE  

创建测试函数, 这个函数创建正常, 但是调用时将会报模糊错误, 因为id既是表字段, 又是函数中的变量.

postgres=# create or replace function f_test () returns int as $$  
postgres$# declare  
postgres$# id int;  
postgres$# begin  
postgres$# select id into id from test limit 1;  
postgres$# return id;  
postgres$# end;  
postgres$# $$ language plpgsql;  
CREATE FUNCTION  

调用这个函数时将报错

postgres=# \set VERBOSITY verbose  
postgres=# select * from f_test();  
ERROR:  42702: column reference "id" is ambiguous  
LINE 1: select id         from test limit 1  
               ^  
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.  
QUERY:  select id         from test limit 1  
CONTEXT:  PL/pgSQL function f_test() line 5 at SQL statement  
LOCATION:  plpgsql_post_column_ref, pl_comp.c:1046  

使用plpgsql_check_function这个函数来检测函数定义中的错误.

postgres=# select oid,* from pg_language ;  
  oid  | lanname  | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl   
-------+----------+----------+---------+--------------+---------------+-----------+--------------+--------  
    12 | internal |       10 | f       | f            |             0 |         0 |         2246 |   
    13 | c        |       10 | f       | f            |             0 |         0 |         2247 |   
    14 | sql      |       10 | f       | t            |             0 |         0 |         2248 |   
 12517 | plpgsql  |       10 | t       | t            |         12514 |     12515 |        12516 |   
(4 rows)  
  
postgres=# select proname,plpgsql_check_function(oid) from pg_proc where prolang=12517;  
 proname |                         plpgsql_check_function                            
---------+-------------------------------------------------------------------------  
 f_test  | error:42702:5:SQL statement:column reference "id" is ambiguous  
 f_test  | Query: select id         from test limit 1  
 f_test  | --            ^  
 f_test  | Detail: It could refer to either a PL/pgSQL variable or a table column.  
(4 rows)  

参考

https://pgxn.org/dist/plpgsql_check/

https://github.com/okbob/plpgsql_check

digoal’s 大量PostgreSQL文章入口