How to debug postgresql stored procedures?

Along with the trusty ol' RAISE commands, there are also a couple 3rd-party tools that I have heard of, though I've never used them.

  • PLPGSQL Lint: https://github.com/okbob/plpgsql_lint
  • EDB Debugger: http://www.enterprisedb.com/docs/en/9.0/asguide/Postgres_Plus_Advanced_Server_Guide-17.htm. My understanding is that the EnterpriseDB Debugger comes bundled with their Postgres Studio package.

It sounds like you're looking for actual debugging capability. PostgreSQL actually introduced this functionality starting with PostgreSQL 8.3.

Debugger

It's fantastic and totally makes PostgreSQL live up to it's tagline "the world's most advanced open source database". It's kind of a hassle to get running but these links might help get you started. Once enabled it allows you to set breakpoints or define inputs & evaluate functions all through a handy right-click menu in PGAdmin. Right click menu


There is a debugger for PGAdmin: I tried this in ubuntu environment with Postgres 12 and it worked for me:

  1. install this package

    apt-get install postgresql-12-pldebugger

  2. Run this command in the database where resides your Procedure

    CREATE EXTENSION pldbgapi;

  3. In your Postgresql installation folder and precisely in the data folder change this parameter in the postgresql.conf file

    shared_preload_libraries = 'plugin_debugger'

NB: you need to restart your Postgres after making this change

  1. in pgAdmin right click on the procedure present in the list of the procedures in your schema, put the values for parameters and choose debug

To "print" a message, you can use raise notice from within a PL/pgSQL function:
http://www.postgresql.org/docs/current/static/plpgsql-errors-and-messages.html

Note that the client must have set the value of "client_min_messages" to the appropriate level in order to receive the "notice".

pgAdmin has a debugger for functions: http://www.pgadmin.org/docs/1.18/debugger.html

(But I have never use it as I don't use pgAdmin).