Re: Feature Request: SQL parameters

On , Sat, 05 Mar 2011 20:39:53 +0100, Guillaume wrote
> Le 03/03/2011 00:03, Eric Schwarzenbach a écrit :
> >  Some years ago I worked with another DB tool, for another DB that
> >  offered support for SQL parameters in a way that was extremely handy.
> >  I've missed this feature in pgAdmin ever since. By SQL parameters, I
> >  mean the replaceable bits one puts as ? in a prepared statement.
> >
> >  When testing / debugging sql from application code, it is a major
> >  annoyance to have to constantly find and replace these ?'s. I forget
> >  exactly how the feature in the other tool worked...it may have actually
> >  detected the ?'s and prompted you for values the moment you tried to
> >  execute it. But the important thing was that it allowed you to leave the
> >  ?'s in the query, and gave you a place to fill in the parameter values
> >  to be used when you execute it. This may not sound like much on the
> >  surface, but makes an enormous difference. It:
> >
> >  * avoids having to hunt down the ? in a large complex query
> >  * keeps your query looking more as it appears in your source code (and
> >  you can more easily paste it back in after you've modified it in pgadmin)
> >  * keeps you from losing track of where the ?s were before you replaced them
> >  * is handier for testing out different values for the parameters.
> >
>
> I'm not sure I understand what you would like to have. That pgAdmin asks
> for values anytime it encounters a question mark in a query?
Sorry if I was a bit vague. I figured there might be a number of UI 
approaches to handling this, some of which might be easier or harder 
given pgadmin facilities, and didn't want to my request to get bogged 
down in Hows rather than Whats, but maybe they are necessary to convey 
the idea.

I'll sketch out  a few ways I can see it working:

The main feature I'm looking for is a way to let the user supply a list 
of values to be used to replace the ?'s when executing the query. Now if 
pgAdmin could detect the ?'s and prompt the user to use this facility, 
all the better, but this isn't strictly necessary. The user, knowing he 
has ?'s in his query could manually open the dialog or panel or whatever 
for entering parameter values (for the sake of argument I'll suppose it 
is a panel, the "parameter panel". The important thing is being able to 
enter the values in some more central location rather than hunting 
through what may be many lines of SQL. The effect should be that the SQL 
in the editor pane remains unchanged, with the ?'s in place, but when 
you execute your query PgAdmin replaces them with the values you have 
entered in the parameter panel.

The How of the interface for entering them is probably important for how 
easy this is in the use case I have in mind (debugging application code 
SQL), but may be dependent on the tools the user is using. Perhaps 
several options could be provided. For my own case, debugging Java in 
Eclipse., I can generally copy the parameters out of the list in the 
debugger in the form of a comma separated list. So the ability to simply 
paste in this list would be ideal for me. I don't know how ideal this 
would be for other users using other debuggers, but perhaps others will 
give input here.

A small feature that would be really sweet, as part of this, would be is 
pgAdmin were able to highlight a given ? in the SQL when you select a 
given parameter in the parameter panel.

Eric