Debug SQL in pgAdmin when SQL contains variables

You can achieve this using the PREPARE, EXECUTE, DEALLOCATE commands for handling statements, which is really what we are talking about here.

For example:

PREPARE test AS SELECT * FROM users WHERE first_name = $1;EXECUTE test ('paul');DEALLOCATE test;

Perhaps not as graphical as some may like, but certainly workable.

I would give a shot at writing a SQL function that wraps your query. It can be something as simple as

CREATE OR REPLACE FUNCTION my_function(integer, integer) RETURNS integerAS$$    SELECT $1 + $2;$$ LANGUAGE SQL;SELECT my_function(1, 2);

I would do this instead of a PREPARE since it will be simpler to update it. Depending on how complex the function is, you might want to also look at some of the other PL's in Postgres.

SQL procs are notoriously hard to debug. My lame but practical solution has been to write log messages to a log table, like this (please excuse syntax issues):

create table log_message (  log_timestamp timestamp not null default current_timestamp,  message varchar(1000));

then add lines to your stored proc like:

insert into log_message (message) values ("The value of x is " || @x);

Then after a run:

select * from log_message order by 1;

It's not pretty, but works in every DB.