PostgreSQL 如何打印函数调用栈信息

增加了调用堆信息的输出. 可以用于plpgsql debug等.
测试 :
pg94@db-192-168-100-216-> psql
psql (9.4devel)
Type "help" for help.

digoal=# -- access to call stack
digoal=# create or replace function inner_func(int)
digoal-# returns int as $$
digoal$# declare _context text;
digoal$# begin
digoal$#   get diagnostics _context = pg_context;
digoal$#   raise notice '***%***', _context;
digoal$#   return 2 * $1;
digoal$# end;
digoal$# $$ language plpgsql;
CREATE FUNCTION
digoal=#
digoal=# create or replace function outer_func(int)
digoal-# returns int as $$
digoal$# begin
digoal$#   return inner_func($1);
digoal$# end;
digoal$# $$ language plpgsql;
CREATE FUNCTION
digoal=#
digoal=# create or replace function outer_outer_func(int)
digoal-# returns int as $$
digoal$# begin
digoal$#   return outer_func($1);
digoal$# end;
digoal$# $$ language plpgsql;
CREATE FUNCTION
digoal=#
digoal=# select outer_outer_func(10);
NOTICE:  ***PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS
PL/pgSQL function outer_func(integer) line 3 at RETURN
PL/pgSQL function outer_outer_func(integer) line 3 at RETURN***
CONTEXT:  PL/pgSQL function outer_func(integer) line 3 at RETURN
PL/pgSQL function outer_outer_func(integer) line 3 at RETURN
 outer_outer_func
------------------
               20
(1 row)

以下SQL :
digoal$#   get diagnostics _context = pg_context;
digoal$#   raise notice '***%***', _context;
将call stack的信息打印出来如下 :
NOTICE:  ***PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS
PL/pgSQL function outer_func(integer) line 3 at RETURN
PL/pgSQL function outer_outer_func(integer) line 3 at RETURN***
CONTEXT:  PL/pgSQL function outer_func(integer) line 3 at RETURN
PL/pgSQL function outer_outer_func(integer) line 3 at RETURN
对于一些敏感函数, 如果要跟踪被调用或者间接调用的情况, 可以把stack的信息输出到表中 例如.
digoal=#  create table if not exists rec_inner_func_called (id serial8 primary key, info text, crt_time timestamp default clock_timestamp());
digoal=#
create or replace function inner_func(int)
returns int as $$
declare _context text;
begin
  get diagnostics _context = pg_context;
  insert into rec_inner_func_called(info) values (_context);
  return 2 * $1;
end;
$$ language plpgsql;
CREATE FUNCTION

digoal=# select outer_outer_func(10);
 outer_outer_func
------------------
               20
(1 row)

digoal=# select * from rec_inner_func_called;
 id |                              info                               |          crt_time
----+-----------------------------------------------------------------+----------------------------
  1 | PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS+| 2013-07-26 09:19:32.588016
    | PL/pgSQL function outer_func(integer) line 3 at RETURN         +|
    | PL/pgSQL function outer_outer_func(integer) line 3 at RETURN    |
(1 row)
digoal=# select outer_outer_func(100);
 outer_outer_func
------------------
              200
(1 row)

digoal=# select outer_outer_func(100);
 outer_outer_func
------------------
              200
(1 row)

digoal=#
digoal=#
digoal=# select * from rec_inner_func_called;
 id |                              info                               |          crt_time
----+-----------------------------------------------------------------+----------------------------
  1 | PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS+| 2013-07-26 09:19:32.588016
    | PL/pgSQL function outer_func(integer) line 3 at RETURN         +|
    | PL/pgSQL function outer_outer_func(integer) line 3 at RETURN    |
  2 | PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS+| 2013-07-26 09:19:46.031669
    | PL/pgSQL function outer_func(integer) line 3 at RETURN         +|
    | PL/pgSQL function outer_outer_func(integer) line 3 at RETURN    |
  3 | PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS+| 2013-07-26 09:20:30.25935
    | PL/pgSQL function outer_func(integer) line 3 at RETURN         +|
    | PL/pgSQL function outer_outer_func(integer) line 3 at RETURN    |
  4 | PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS+| 2013-07-26 09:20:32.665713
    | PL/pgSQL function outer_func(integer) line 3 at RETURN         +|
    | PL/pgSQL function outer_outer_func(integer) line 3 at RETURN    |
(4 rows)

[参考]
1. http://www.postgresql.org/docs/devel/static/plpgsql-control-structures.html#PLPGSQL-DIAGNOSTICS