Thread: Feature Request for Debugging SQL in PGAdmin3 when SQL contains variables

Originally from SQL Server background, there is one feature that I am
missing and would save developers hours of time.

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

It would be amazing if simular functionality could be built into in pgadmin3
(NpgSQL uses : instead of @) where I can just drop my sql (params & all)
into the query window.

I realise you can create pgscript, but it doesn't achieve the above...
Currently I have a peice of sql someone has written that has 3 unique
varibles in it which are used around 7 times each...

I originally posted this question on stack overflow, there doesn't seem to
be a (commercial) competitor that does this either:
http://stackoverflow.com/questions/2959343/debugging-sql-in-pgadmin3-when-sq
l-contains-variables

Le 16/11/2010 13:14, Dan Shoubridge a écrit :
> Originally from SQL Server background, there is one feature that I am
> missing and would save developers hours of time.
> 
> 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 - 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
> 
> It would be amazing if simular functionality could be built into in pgadmin3
> (NpgSQL uses : instead of @) where I can just drop my sql (params & all)
> into the query window.
> 
> I realise you can create pgscript, but it doesn't achieve the above...
> Currently I have a peice of sql someone has written that has 3 unique
> varibles in it which are used around 7 times each...
> 

And? I don't see why pgscript can't do that. The example you give is
certainly doable with pgscript.

> I originally posted this question on stack overflow, there doesn't seem to
> be a (commercial) competitor that does this either:
> http://stackoverflow.com/questions/2959343/debugging-sql-in-pgadmin3-when-sq
> l-contains-variables
> 
> 


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com

Le 16/11/2010 13:34, Guillaume Lelarge a écrit :
> Le 16/11/2010 13:14, Dan Shoubridge a écrit :
>> Originally from SQL Server background, there is one feature that I am
>> missing and would save developers hours of time.
>>
>> 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 - 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
>>
>> It would be amazing if simular functionality could be built into in pgadmin3
>> (NpgSQL uses : instead of @) where I can just drop my sql (params & all)
>> into the query window.
>>
>> I realise you can create pgscript, but it doesn't achieve the above...
>> Currently I have a peice of sql someone has written that has 3 unique
>> varibles in it which are used around 7 times each...
>>
> 
> And? I don't see why pgscript can't do that. The example you give is
> certainly doable with pgscript.
> 

Just for the record, the above script looks like this in pgscript:

declare @x;
set @x = 'abc';
select * from sometable where somefield = '@x';

And it works.


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com

> > Le 16/11/2010 13:14, Dan Shoubridge a écrit :
> > > Originally from SQL Server background, there is one feature that I
> > > am missing and would save developers hours of time.
> > >
> > > 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 -
> > > 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
> > >
> > > It would be amazing if simular functionality could be built into in
> > > pgadmin3 (NpgSQL uses : instead of @) where I can just drop my sql
> > > (params & all) into the query window.
> > >
> > > I realise you can create pgscript, but it doesn't achieve the above...
> > > Currently I have a peice of sql someone has written that has 3
> > > unique varibles in it which are used around 7 times each...

> > And? I don't see why pgscript can't do that. The example you give is
certainly doable with pgscript.

> > Just for the record, the above script looks like this in pgscript:

> > declare @x;
> > set @x = 'abc';
> > select * from sometable where somefield = '@x';

> > And it works.

> Ok, I tried it - I think I must have missed of the quotes in my version,
but that still defeats the point - It's easy to replace @ with :, but having
to put quotes around all the vars makes it less efficient. Is there anything
that can be done about this?

And I don't get any output in the 'Data Output tab' - this is the most
important bit.

> > > Originally from SQL Server background, there is one feature that I
> > > am missing and would save developers hours of time.
> > >
> > > 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 - 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
> > >
> > > It would be amazing if simular functionality could be built into
> > > in
> > > pgadmin3 (NpgSQL uses : instead of @) where I can just drop my sql
> > > (params & all) into the query window.
> > >
> > > I realise you can create pgscript, but it doesn't achieve the above...
> > > Currently I have a peice of sql someone has written that has 3
> > > unique varibles in it which are used around 7 times each...


> > Le 16/11/2010 13:34, Guillaume Lelarge a écrit :

> > And? I don't see why pgscript can't do that. The example you give is
certainly doable with pgscript.

> > Just for the record, the above script looks like this in pgscript:

> > declare @x;
> > set @x = 'abc';
> > select * from sometable where somefield = '@x';

> > And it works.

16/11/2010 13:00, Dan Shoubridge:

(Apologies for messing up my reply, I've not used mailing lists before and
they never get formatted correctly/

Ok, I tried it - I think I must have missed of the quotes in my version, but
that still defeats the point - It's easy to replace @ with :, but having to
put quotes around all the vars makes it less efficient. Is there anything
that can be done about this?

And I don't get any output in the 'Data Output tab' - this is the most
important bit. - I can copy and paste the result that I can then run, after
I've fiddled with the script a bit, but it isn't seamless. - Just a request
as I think others would find it useful too?

Le 16/11/2010 14:01, Dan Shoubridge a écrit :
>>>> Originally from SQL Server background, there is one feature that I 
>>>> am missing and would save developers hours of time.
>>>>
>>>> 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 - 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
>>>>
>>>> It would be amazing if simular functionality could be built into 
>>>> in
>>>> pgadmin3 (NpgSQL uses : instead of @) where I can just drop my sql 
>>>> (params & all) into the query window.
>>>>
>>>> I realise you can create pgscript, but it doesn't achieve the above...
>>>> Currently I have a peice of sql someone has written that has 3 
>>>> unique varibles in it which are used around 7 times each...
> 
> 
>>> Le 16/11/2010 13:34, Guillaume Lelarge a écrit :
> 
>>> And? I don't see why pgscript can't do that. The example you give is
> certainly doable with pgscript.
> 
>>> Just for the record, the above script looks like this in pgscript:
> 
>>> declare @x;
>>> set @x = 'abc';
>>> select * from sometable where somefield = '@x';
> 
>>> And it works.
> 
> 16/11/2010 13:00, Dan Shoubridge:
> 
> (Apologies for messing up my reply, I've not used mailing lists before and
> they never get formatted correctly/
> 
> Ok, I tried it - I think I must have missed of the quotes in my version, but
> that still defeats the point - It's easy to replace @ with :, but having to
> put quotes around all the vars makes it less efficient. Is there anything
> that can be done about this?
> 

I don't think this is something we want to do. Problem is that variables
are not strictly typed, so there is nothing that could tell pgscript if
it should add simple quotes (simple quotes for text, but not for integer
for example).

> And I don't get any output in the 'Data Output tab' - this is the most
> important bit. - I can copy and paste the result that I can then run, after
> I've fiddled with the script a bit, but it isn't seamless. - Just a request
> as I think others would find it useful too?
> 

Yeah, that's quite surprising. I would be useful but quite hard to do.

I think I'll add a ticket on this.


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com

Le 16/11/2010 23:26, Guillaume Lelarge a écrit :
> Le 16/11/2010 14:01, Dan Shoubridge a écrit :
>>>>> Originally from SQL Server background, there is one feature that I 
>>>>> am missing and would save developers hours of time.
>>>>>
>>>>> 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 - 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
>>>>>
>>>>> It would be amazing if simular functionality could be built into 
>>>>> in
>>>>> pgadmin3 (NpgSQL uses : instead of @) where I can just drop my sql 
>>>>> (params & all) into the query window.
>>>>>
>>>>> I realise you can create pgscript, but it doesn't achieve the above...
>>>>> Currently I have a peice of sql someone has written that has 3 
>>>>> unique varibles in it which are used around 7 times each...
>>
>>
>>>> Le 16/11/2010 13:34, Guillaume Lelarge a écrit :
>>
>>>> And? I don't see why pgscript can't do that. The example you give is
>> certainly doable with pgscript.
>>
>>>> Just for the record, the above script looks like this in pgscript:
>>
>>>> declare @x;
>>>> set @x = 'abc';
>>>> select * from sometable where somefield = '@x';
>>
>>>> And it works.
>>
>> 16/11/2010 13:00, Dan Shoubridge:
>>
>> (Apologies for messing up my reply, I've not used mailing lists before and
>> they never get formatted correctly/
>>
>> Ok, I tried it - I think I must have missed of the quotes in my version, but
>> that still defeats the point - It's easy to replace @ with :, but having to
>> put quotes around all the vars makes it less efficient. Is there anything
>> that can be done about this?
>>
> 
> I don't think this is something we want to do. Problem is that variables
> are not strictly typed, so there is nothing that could tell pgscript if
> it should add simple quotes (simple quotes for text, but not for integer
> for example).
> 
>> And I don't get any output in the 'Data Output tab' - this is the most
>> important bit. - I can copy and paste the result that I can then run, after
>> I've fiddled with the script a bit, but it isn't seamless. - Just a request
>> as I think others would find it useful too?
>>
> 
> Yeah, that's quite surprising. I would be useful but quite hard to do.
> 
> I think I'll add a ticket on this.
> 

Ticket added.


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com

>Le 16/11/2010 23:26, Guillaume Lelarge a écrit :
>> Le 16/11/2010 14:01, Dan Shoubridge a écrit :
>>>>>> Originally from SQL Server background, there is one feature that I
>>>>>> am missing and would save developers hours of time.
>>>>>>
>>>>>> 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 - 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
>>>>>>
>>>>>> It would be amazing if simular functionality could be built into
>>>>>> in
>>>>>> pgadmin3 (NpgSQL uses : instead of @) where I can just drop my sql
>>>>>> (params & all) into the query window.
>>>>>>
>>>>>> I realise you can create pgscript, but it doesn't achieve the
above...
>>>>>> Currently I have a peice of sql someone has written that has 3
>>>>>> unique varibles in it which are used around 7 times each...
>>>
>>>
>>>>> Le 16/11/2010 13:34, Guillaume Lelarge a écrit :
>>>
>>>>> And? I don't see why pgscript can't do that. The example you give
>>>>> is
>>> certainly doable with pgscript.
>>>
>>>>> Just for the record, the above script looks like this in pgscript:
>>>
>>>>> declare @x;
>>>>> set @x = 'abc';
>>>>> select * from sometable where somefield = '@x';
>>>
>>>>> And it works.
>>>
>>> 16/11/2010 13:00, Dan Shoubridge:
>>>
>>> (Apologies for messing up my reply, I've not used mailing lists
>>> before and they never get formatted correctly/
>>>
>>> Ok, I tried it - I think I must have missed of the quotes in my
>>> version, but that still defeats the point - It's easy to replace @
>>> with :, but having to put quotes around all the vars makes it less
>>> efficient. Is there anything that can be done about this?
>>>
>>
>> I don't think this is something we want to do. Problem is that
>> variables are not strictly typed, so there is nothing that could tell
>> pgscript if it should add simple quotes (simple quotes for text, but
>> not for integer for example).
>>

Ok, I understand and it’s a shame the vars aren’t strong typed. It's little
things like that that make me want SQL Server back. It doesn't seem like a
big thing to some people, but for a lot of developers the amount of work
added by this when debugging sql adds up over time.

>>> And I don't get any output in the 'Data Output tab' - this is the
>>> most important bit. - I can copy and paste the result that I can then
>>> run, after I've fiddled with the script a bit, but it isn't seamless.
>>> - Just a request as I think others would find it useful too?
>>>
>>
> Yeah, that's quite surprising. I would be useful but quite hard to do.

It's not a case of just capturing the output message and running that? :(

>
> I think I'll add a ticket on this.
>

> Ticket added.

I think when I asked this question on stack overflow there was a problem?
But since upgrading pgAdmin it seems to work now. :) Thanks for your help.


--
Guillaumehttp://www.postgresql.frhttp://dalibo.com

--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To
make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support

On Wed, Nov 17, 2010 at 9:40 AM, Dan Shoubridge
<dan.shoubridge@autovhc.co.uk> wrote:>
>
> Ok, I understand and it’s a shame the vars aren’t strong typed. It's little
> things like that that make me want SQL Server back. It doesn't seem like a
> big thing to some people, but for a lot of developers the amount of work
> added by this when debugging sql adds up over time.

If you really want T-SQL support, then SQL Server or Sybase really are
your only choices. What we have in pgAdmin is a scripting language
that we modelled loosely on T-SQL for ease of learning, that is
designed for making tasks like creating multiple table partitions
quick and easy. It's really not intended as a full-featured
replacement for T-SQL.

With PostgreSQL (9 and above), if you need a "real" procedural
language that you can use on the server, then I'd suggest looking at
the DO command, which allows you to execute anonymous blocks of code
an currently supports pl/pgsql, pl/perl and pl/python.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Dan,
> Ok, I understand and it's a shame the vars aren't strong typed. It's
little things like that that make me want SQL Server back. It doesn't seem
like a big thing to some people, but for a lot of developers the amount of 
> work added by this when debugging sql adds up over time. 

I know it's a new invention in PostgreSQL 9.0 so might not be an option for
you, but wouldn't the new 

DO command do what you are looking for?

http://wiki.postgresql.org/index.php?title=PostgreSQL_9.0#Anonymous_Function
s_.28aka_Anonymous_Blocks.29

Leo

>>Dan,
>> Ok, I understand and it's a shame the vars aren't strong typed. It's
little things like that that make me want SQL Server back. It doesn't seem
like a big thing to some people, but for a lot of developers the amount of
work added by this when debugging sql adds up over time. 

>I know it's a new invention in PostgreSQL 9.0 so might not be an option for
you, but wouldn't the new 

>DO command do what you are looking for?

pgScript looks better for me atm, as you can have @ to specify variables, it
doesn't let you in anonymous code blocks - this defeats the object of
efficient debugging entirely (I'd have to remove the : from the sql than add
it again afterwards)

I tried the following:

DO language plpgsql $$
DECLARE x integer;
BEGINx := 7; SELECT * FROM site WHERE sitecode = x;END$$;

but got the error

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "inline_code_block" line 7 at SQL statement

********** Error **********

ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Context: PL/pgSQL function "inline_code_block" line 7 at SQL statement

It looks like I need to declare the returning type, or specify to output to
the dataoutput window? But the documentation doesn't really expand on this
scenario, and there isn't any examples I could find through Google.


>http://wiki.postgresql.org/index.php?title=PostgreSQL_9.0#Anonymous_Functio
n
>s_.28aka_Anonymous_Blocks.29

>Leo



>--
>Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To
make changes to your subscription:
>http://www.postgresql.org/mailpref/pgadmin-support

On 17/11/2010 11:22, Dan Shoubridge wrote:
>>> Dan,
>>> Ok, I understand and it's a shame the vars aren't strong typed. It's
> little things like that that make me want SQL Server back. It doesn't seem
> like a big thing to some people, but for a lot of developers the amount of
> work added by this when debugging sql adds up over time.
>
>> I know it's a new invention in PostgreSQL 9.0 so might not be an option for
> you, but wouldn't the new
>
>> DO command do what you are looking for?
>
> pgScript looks better for me atm, as you can have @ to specify variables, it
> doesn't let you in anonymous code blocks - this defeats the object of
> efficient debugging entirely (I'd have to remove the : from the sql than add
> it again afterwards)
>
> I tried the following:
>
> DO language plpgsql $$
> DECLARE x integer;
> BEGIN
>     x := 7;
>     SELECT * FROM site WHERE sitecode = x;
>   END
>   $$;
>
> but got the error
>
> ERROR:  query has no destination for result data
> HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
> CONTEXT:  PL/pgSQL function "inline_code_block" line 7 at SQL statement
>
> ********** Error **********
>
> ERROR: query has no destination for result data
> SQL state: 42601
> Hint: If you want to discard the results of a SELECT, use PERFORM instead.
> Context: PL/pgSQL function "inline_code_block" line 7 at SQL statement
>
> It looks like I need to declare the returning type, or specify to output to
> the dataoutput window? But the documentation doesn't really expand on this
> scenario, and there isn't any examples I could find through Google.

I haven't been following this thread, but on the above, yes, you do have 
to declare a variable for the returned row, something like:

declare  x integer;  rec record;
begin  x := 7;  select * into rec from site where sitecode = x;
end;

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie