How to debug postgresql stored procedures?

Question

I realize that there is nothing similar to SQL Server Management Studio, so I am mentally prepared to use the good old printf debugging.

The only question is how to do "printf" from a stored procedure?

`raise notice` (http://www.postgresql.org/docs/current/static/plpgsql-errors-and-messages.html) would be the equivalent. But pgAdmin has a debugger for PL/pgSQL: http://www.pgadmin.org/docs/1.18/debugger.html — , Nov 25 '13 at 11:08
I am wondering how this question could be almost 6 years old when "true" [stored procedures were introduced in PostgreSQL 11](https://stackoverflow.com/a/50514083/5070879) — Lukasz Szozda, Aug 09 '19 at 16:31

score 51 · Accepted Answer · answered Nov 25 '13 at 11:48

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).

score 17 · Answer 2 · answered Jan 21 '16 at 22:10

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

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.

Do you know if is possible to evaluate or figure out a var value that was not declared? Like from a select into... — Diogo Alves, Apr 18 '17 at 18:55

score 7 · Answer 3 · answered Nov 25 '13 at 15:36

7

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.

answered Nov 25 '13 at 15:36

bma

9,424
2
33
22

Postgres Plus Advanced Server seems like a really cool thing, but it is off scope for me, since it requires a license to be purchased and that license is quite pricey for an individual. – mark Nov 26 '13 at 10:13

score 3 · Answer 4 · answered Aug 06 '21 at 12:10

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

install this package

apt-get install postgresql-12-pldebugger
Run this command in the database where resides your Procedure

CREATE EXTENSION pldbgapi;
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

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

score 0 · Answer 5 · answered Aug 08 '23 at 01:43

I know this answer comes a bit late but I had the exactly same problem many years ago. I needed to write several very complex stored procedure. I looked around many programs and only one had the perfect, user friendly features for it. I am using the "SQL Manager for PostgreSQL" https://www.sqlmanager.net/products/postgresql/manager

I could fully debug my functions with step by step debugging, breakpoints and editable input variables. Until now I didn't find any other product to do it. It does cost a bit (But it pays off quickly for the time you saved...), but if you need to write complex functions, this is your friend ;-)

How to debug postgresql stored procedures?

5 Answers5

Linked