5

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

Improve this question
1
  • I wanted to add this as a feature request, but I couldn't as I'm not old school enough to work out how the pgAdmin mailing lists works :( Commented Nov 15, 2010 at 15:46

3 Answers 3

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I delete my answer suggesting to use a linked server from sql! Thanks, just a shame it they can't be named.
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.

Improve this answer

4 Comments

Not really what I was after. I don't want to have to write a function each time I need to debug SQL. Also I don't think the output would be what I require.
If all you're looking for is a way to use the variables in your query a function like that to wrap your query is really easy. What sort of output are you looking for?
the output I would get if I ran the query, or the debugger output. This definantly isn't the solution to the question - maybe there isn't an answer - I was thinking there was maybe an pgAdmin alternative I could use. I just want to paste my code into the sql window, set my vars then run it.
Ah, yeah there is no way to do that in pgAdmin that I'm aware of. Without using a PL there isnt a way to just set vars in PG.
0

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.

Improve this answer

1 Comment

I don't think their hard to debug at all. Esp in sql server. Sorry, this isn't what I'm looking for.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.