1 前言
Postgresql的plpgsql提供了一套钩子函数支持运行时动态加载，非常便于调试plpgsql。本文总结使用方法和实例。
plpgsql的钩子变量plpgsql_plugin_ptr：
代码语言：javascript复制// pl_handler.c
void
_PG_init(void)
{
...
...
plpgsql_plugin_ptr = (PLpgSQL_plugin **) find_rendezvous_variable("PLpgSQL_plugin");
}2 效果
使用load加载so，enable钩子函数。然后执行函数就可以看到回显了，在函数的setup、启动、结束都有函数钩子，可以查看estate、stmt结构的任何变量，非常方便。也可以做一些stmt或datums变量的调整、修改，影响执行过程。
代码语言：javascript复制$ psql
psql (16devel)
Type "help" for help.
postgres=# load 'plpgsql_callback';
LOAD
postgres=# DO LANGUAGE plpgsql $$
DECLARE
cnt int;
BEGIN
cnt := 1;
raise notice 'cnt: %', cnt;
END;
$$;
NOTICE: function setup: "inline_code_block", function has 2 datums
NOTICE: function begin: "inline_code_block"
NOTICE: statement [ statement block] begin - [ln: 4]
NOTICE: statement [ assignment] begin - [ln: 5]
NOTICE: statement [ assignment] end - [ln: 5]
NOTICE: statement [ RAISE] begin - [ln: 6]
NOTICE: cnt: 1
NOTICE: statement [ RAISE] end - [ln: 6]
NOTICE: statement [ RETURN] begin - [ln: 0]
NOTICE: statement [ RETURN] end - [ln: 0]
NOTICE: statement [ statement block] end - [ln: 4]
NOTICE: function end: "inline_code_block"
DO
postgres=# 3 源码
plpgsql_callback.c
代码语言：javascript复制#include "postgres.h"
#include "plpgsql.h"
PG_MODULE_MAGIC;
void _PG_init(void);
void _PG_fini(void);
static void plpgsql_cb_func_setup(PLpgSQL_execstate *estate, PLpgSQL_function *func);
static void plpgsql_cb_func_beg(PLpgSQL_execstate *estate, PLpgSQL_function *func);
static void plpgsql_cb_func_end(PLpgSQL_execstate *estate, PLpgSQL_function *func);
static void plpgsql_cb_stmt_beg(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt);
static void plpgsql_cb_stmt_end(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt);
const char *
plpgsql_stmt_typename(PLpgSQL_stmt *stmt)
{
switch (stmt->cmd_type)
{
case PLPGSQL_STMT_BLOCK:
return _("statement block");
case PLPGSQL_STMT_ASSIGN:
return _("assignment");
case PLPGSQL_STMT_IF:
return "IF";
case PLPGSQL_STMT_CASE:
return "CASE";
case PLPGSQL_STMT_LOOP:
return "LOOP";
case PLPGSQL_STMT_WHILE:
return "WHILE";
case PLPGSQL_STMT_FORI:
return _("FOR with integer loop variable");
case PLPGSQL_STMT_FORS:
return _("FOR over SELECT rows");
case PLPGSQL_STMT_FORC:
return _("FOR over cursor");
case PLPGSQL_STMT_FOREACH_A:
return _("FOREACH over array");
case PLPGSQL_STMT_EXIT:
return ((PLpgSQL_stmt_exit *) stmt)->is_exit ? "EXIT" : "CONTINUE";
case PLPGSQL_STMT_RETURN:
return "RETURN";
case PLPGSQL_STMT_RETURN_NEXT:
return "RETURN NEXT";
case PLPGSQL_STMT_RETURN_QUERY:
return "RETURN QUERY";
case PLPGSQL_STMT_RAISE:
return "RAISE";
case PLPGSQL_STMT_ASSERT:
return "ASSERT";
case PLPGSQL_STMT_EXECSQL:
return _("SQL statement");
case PLPGSQL_STMT_DYNEXECUTE:
return "EXECUTE";
case PLPGSQL_STMT_DYNFORS:
return _("FOR over EXECUTE statement");
case PLPGSQL_STMT_GETDIAG:
return ((PLpgSQL_stmt_getdiag *) stmt)->is_stacked ?
"GET STACKED DIAGNOSTICS" : "GET DIAGNOSTICS";
case PLPGSQL_STMT_OPEN:
return "OPEN";
case PLPGSQL_STMT_FETCH:
return ((PLpgSQL_stmt_fetch *) stmt)->is_move ? "MOVE" : "FETCH";
case PLPGSQL_STMT_CLOSE:
return "CLOSE";
case PLPGSQL_STMT_PERFORM:
return "PERFORM";
case PLPGSQL_STMT_CALL:
return ((PLpgSQL_stmt_call *) stmt)->is_call ? "CALL" : "DO";
case PLPGSQL_STMT_COMMIT:
return "COMMIT";
case PLPGSQL_STMT_ROLLBACK:
return "ROLLBACK";
}
return "unknown";
}
static PLpgSQL_plugin plugin_funcs = {
plpgsql_cb_func_setup,
plpgsql_cb_func_beg,
plpgsql_cb_func_end,
plpgsql_cb_stmt_beg,
plpgsql_cb_stmt_end,
NULL,
NULL
};
static void
plpgsql_cb_func_setup(PLpgSQL_execstate *estate, PLpgSQL_function *func)
{
elog(NOTICE, "function setup: "%s", function has %d datums", func->fn_signature, func->ndatums);
}
static void
plpgsql_cb_func_beg(PLpgSQL_execstate *estate, PLpgSQL_function *func)
{
elog(NOTICE, "function begin: "%s"", func->fn_signature);
}
static void
plpgsql_cb_func_end(PLpgSQL_execstate *estate, PLpgSQL_function *func)
{
elog(NOTICE, "function end: "%s"", func->fn_signature);
}
static void
plpgsql_cb_stmt_beg(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
{
elog(NOTICE, "statement [ s] begin - [ln: %d]", plpgsql_stmt_typename(stmt), stmt->lineno);
}
static void
plpgsql_cb_stmt_end(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
{
elog(NOTICE, "statement [ s] end - [ln: %d]", plpgsql_stmt_typename(stmt), stmt->lineno);
}
void
_PG_init(void)
{
PLpgSQL_plugin **var_ptr = (PLpgSQL_plugin **) find_rendezvous_variable("PLpgSQL_plugin");
*var_ptr = &plugin_funcs;
}
void
_PG_fini(void)
{
}Makefile
代码语言：javascript复制MODULES = plpgsql_callback
REGRESS = plpgsql_callback
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
