Debug SQL in pgAdmin when SQL contains variables

In SQL Server I could copy sql code out of an application and paste it into SSMS, declare & assign vars that exist in the sq开发者_开发知识库l and run. yay great debugging scenario.

E.g. (please note I am rusty and syntax may be incorrect):

declare @x as varchar(10)
set @x = 'abc'
select * from sometable where somefield = @x

I want to do something similar with Postgres in pgAdmin (or another postgres tool, any recommendations?) where I can just drop my SQL (params & all) into something that will run against Postgres DB.

I realise you can create pgscript, but it doesn't appear to be very good, for example, if I do the equivalent of above, it doesn't put the single quotes around the value in @x, nor does it let me by doubling them up and you don't get a table out after - only text...

Currently I have a piece of SQL someone has written that has 3 unique variables in it which are used around 6 times each...

So the question is how do other people debug SQL efficiently, preferably in a similar fashion to my SQL Server days.

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 integer
AS
$$
    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.