Debugging SQL in PGAdmin3 when sql contains variables

Question

In SQL Server I could copy sql code out of an application and paste it into SSMS, declare & assign vars that exist in the sql 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 pgadmin3 (or another postgres tool, any recommendations?) where I can just drop my sql (params & all) into something that will run again postgresql db.

I realise you can create pgscript, but it doesn't appear to be very good, for example, if I do the equlivent 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 peice of sql someone has written that has 3 unique varibles in it which are used around 6 times each...

So the question is how do other people debug sql this sql EFFICIENTLY, preferably in a simular fashion to my sql server days.

Answer 1

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.

Answer 2

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.

Answer 3

The closest I can suggest is to use a linked server query from SQL Server.

Answer 4

or if you have a budget... SQL Assistant, unfortunately, I don't.