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