How to debug PostgreSQL function with PGADMINIII

How to debug Plpgsql with PGADMINIII

[[email protected] soft_bak]# git clone git://git.postgresql.org/git/pldebugger.git

Initialized Empty Git repository in/opt/soft_bak/pldebugger/.git/

Remote:counting objects:445, done.

Remote:compressing objects:100% (341/341), done.

Remote:total 445 (Delta 285), reused 171 (Delta 104)

Receiving objects:100% (445/445), 170.50 KiB | KIB/S, done.

Resolving deltas:100% (285/285), done.

[Email protected] soft_bak]# CD pldebugger/

[[email protected] pldebugger]# ls

DBGCOMM.C Makefile pldbgapi.control pldebugger.proj plugin_debugger.def uninstall_pldbgapi. Sql

Dbgcomm.h pldbgapi--1.0.sql pldbgapi--unpackaged--1.0.sql plpgsql_debugger.c Readme.pldebugger

Globalbp.h pldbgapi.c pldebugger.h plugin_debugger.c settings.projinc

[Email protected] soft_bak]# CD postgresql-9.4.5/contrib/

[Email protected] contrib]# cp-r/opt/soft_bak/pldebugger/.

[Email protected] contrib]# CD pldebugger/

[[email protected] pldebugger]# make

Gcc-wall-wmissing-prototypes-wpointer-arith-wdeclaration-after-statement-wendif-labels- Wmissing-format-attribute-wformat-security-fno-strict-aliasing-fwrapv-fexcess-precision=standard-o2-fpic-i. /.. /src/pl/plpgsql/src-i. I.-I.. /.. /src/include-d_gnu_source-c-O plpgsql_debugger.o plpgsql_debugger.c

Gcc-wall-wmissing-prototypes-wpointer-arith-wdeclaration-after-statement-wendif-labels- Wmissing-format-attribute-wformat-security-fno-strict-aliasing-fwrapv-fexcess-precision=standard-o2-fpic-i.-I. -I.. /.. /src/include-d_gnu_source-c-O plugin_debugger.o plugin_debugger.c

Gcc-wall-wmissing-prototypes-wpointer-arith-wdeclaration-after-statement-wendif-labels- Wmissing-format-attribute-wformat-security-fno-strict-aliasing-fwrapv-fexcess-precision=standard-o2-fpic-i.-I. -I.. /.. /src/include-d_gnu_source-c-O dbgcomm.o dbgcomm.c

Gcc-wall-wmissing-prototypes-wpointer-arith-wdeclaration-after-statement-wendif-labels- Wmissing-format-attribute-wformat-security-fno-strict-aliasing-fwrapv-fexcess-precision=standard-o2-fpic-i.-I. -I.. /.. /src/include-d_gnu_source-c-O pldbgapi.o pldbgapi.c

Gcc-wall-wmissing-prototypes-wpointer-arith-wdeclaration-after-statement-wendif-labels- Wmissing-format-attribute-wformat-security-fno-strict-aliasing-fwrapv-fexcess-precision=standard-o2-fpic- Shared-o plugin_debugger.so plpgsql_debugger.o plugin_debugger.o dbgcomm.o pldbgapi.o-l. /.. /src/port-l. /.. /SRC/COMMON-WL,--as-needed-wl,-rpath, '/ext4/pgdefaultgcc530/lib ',--enable-new-dtags

[[email protected] pldebugger]# make install

/bin/mkdir-p '/ext4/pgdefaultgcc530/lib/postgresql '

/bin/mkdir-p '/ext4/pgdefaultgcc530/share/postgresql/extension '

/bin/mkdir-p '/ext4/pgdefaultgcc530/share/postgresql/extension '

/bin/mkdir-p '/ext4/pgdefaultgcc530/share/doc//postgresql/extension '

/usr/bin/install-c-M 755 plugin_debugger.so '/ext4/pgdefaultgcc530/lib/postgresql/plugin_debugger.so '

/usr/bin/install-c-M 644 Pldbgapi.control '/ext4/pgdefaultgcc530/share/postgresql/extension/'

/usr/bin/install-c-M 644 pldbgapi--1.0.sql Pldbgapi--unpackaged--1.0.sql '/ext4/pgdefaultgcc530/share/postgresql/ extension/'

/usr/bin/install-c-M 644 Readme.pldebugger '/ext4/pgdefaultgcc530/share/doc//postgresql/extension/'

[Email protected] ~]# vim/ext4/pgdefaultgcc530/data/postgresql.conf

shared_preload_libraries = ' $libdir/plugin_debugger '

[Email protected] ~]# Su-postgres

[Email protected] ~]$ cd/ext4/pgdefaultgcc530/bin/

[Email protected] bin]$/pg_ctl-d/ext4/pgdefaultgcc530/data/restart

Waiting for server to shut down ... done

Server stopped

Server starting

[[email protected] bin]$ log:redirecting LOG output to logging collector process

Hint:future log output would appear in directory "Pg_log".

[Email protected] bin]$/psql-h localhost-u postgres

Psql (9.4.5)

Type ' help ' for help.

postgres=# CREATE EXTENSION Pldbgapi;

CREATE EXTENSION

postgres=# CREATE TABLE Accounts (owner text, balance numeric, amount numeric);

CREATE TABLE

postgres=# INSERT into Accounts VALUES (' Bob ', 100);

INSERT 0 1

postgres=# INSERT into Accounts VALUES (' Mary ', 200);

INSERT 0 1

postgres=# select * from Accounts;

Owner | Balance | Amount

-------+---------+--------

Bob | 100 |

Mary | 200 |

(2 rows)

postgres=# CREATE OR REPLACE FUNCTION Transfer (

Postgres (# I_payer text,

Postgres (# i_recipient text,

Postgres (# I_amount numeric (15,2))

postgres-# RETURNS Text

postgres-# as

postgres-# $$

postgres$# DECLARE

postgres$# Payer_bal Numeric;

postgres$# BEGIN

postgres$# SELECT balance into Payer_bal

postgres$# from Accounts

postgres$# WHERE owner = I_payer for UPDATE;

postgres$# IF not FOUND Then

postgres$# RETURN ' payer account not found ';

postgres$# END IF;

postgres$# IF Payer_bal < I_amount Then

postgres$# RETURN ' not enough funds ';

postgres$# END IF;

postgres$# UPDATE Accounts

postgres$# SET balance = balance + I_amount

postgres$# WHERE owner = i_recipient;

postgres$# IF not FOUND Then

postgres$# RETURN ' Recipient does not exist ';

postgres$# END IF;

postgres$# UPDATE Accounts

postgres$# SET balance = Balance-i_amount

postgres$# WHERE owner = I_payer;

postgres$# RETURN ' OK ';

postgres$# END;

postgres$# $$ LANGUAGE Plpgsql;

CREATE FUNCTION

postgres=# \DF

List of functions

Schema | Name |                       Result Data Type |  Argument Data Types | Type

--------+-----------------------------+------------------+----------------------------------------------------- -------------+--------

Public | Pldbg_abort_target | Setof Boolean | Session integer | Normal

Public | Pldbg_attach_to_port | Integer | PortNumber Integer | Normal

Public | Pldbg_continue | Breakpoint | Session integer | Normal

Public | Pldbg_create_listener |                                                                  Integer | | Normal

Public | Pldbg_deposit_value | Boolean | Session integer, varname text, linenumber integer, Value text | Normal

Public | Pldbg_drop_breakpoint | Boolean | Session integer, Func oid, linenumber integer | Normal

Public | pldbg_get_breakpoints | Setof Breakpoint | Session integer | Normal

 public | pldbg_get_proxy_info        | proxyinfo         |                                                                     | Normal

Public | Pldbg_get_source | Text | Session integer, Func oid | Normal

Public | Pldbg_get_stack | Setof Frame | Session integer | Normal

Public | Pldbg_get_target_info | Targetinfo | Signature text, TargetType "char" | Normal

Public | Pldbg_get_variables | Setof var | Session integer | Normal

Public | Pldbg_oid_debug | Integer | Functionoid OID | Normal

Public | Pldbg_select_frame | Breakpoint | Session integer, Frame integer | Normal

Public | Pldbg_set_breakpoint | Boolean | Session integer, Func oid, linenumber integer | Normal

Public | Pldbg_set_global_breakpoint | Boolean | Session integer, Func oid, linenumber Integer, targetpid integer | Normal

Public | Pldbg_step_into | Breakpoint | Session integer | Normal

Public | Pldbg_step_over | Breakpoint | Session integer | Normal

Public | Pldbg_wait_for_breakpoint | Breakpoint | Session integer | Normal

Public | Pldbg_wait_for_target | Integer | Session integer | Normal

Public | Plpgsql_oid_debug | Integer | Functionoid OID | Normal

Public | Transfer | Text | I_payer text, i_recipient text, I_amount numeric | Normal

(rows)

Connect to PostgreSQL Server with PGADMINIII

Find to PostgreSQL function to be debuged transfer

Right click the transfer function and Input the parameter to be test

How to debug PostgreSQL function with PGADMINIII