PostgreSQL関数呼び出しスタック情報の印刷方法

4382 ワード

 .  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