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.