Re: Simple, but VERYuseful enhancement for psql command - or am I

I'm designing a fairly involved database system. As part fo the process, I
use the \i [FILE] command a great deal. I set up fairly involved queries,
sometimes simply for the purpose of shortening column names so the output
is reasonable. For example:

SELECT longname AS abbr,othername as "V" FROM table WHERE how;

...a bunch of these can result in a single-line output on the console,
which is a lot easier to deal with than a dump of the actual field names
which wraps around and makes you scroll back and forth trying to line up
the names with the values.

Now, in my case, I'm dealing with specific orders. So the WHERE clause
might be:

...WHERE zorder=104788;

Which works fine. But, I have to edit the file every time I'm working with
a different order, which is repetative and annoying, something computers
are supposed to save us from. :)

However, you can't leave it out; \i [FILE] expects the query to be
complete, ready to go to the server. As far as I can tell.

So - how about a command to read a file into the input lines withOUT
sending it yet, so that its ready to type the last part, such as:

104788;

In other words, the file would end here:

...WHERE zorder=104788;
^
|
|
...then I could just type the number, hit enter, and off it would go.

Or even if it has to be complete, right now, you can use \i [FILE] and it
runs, but you can't edit the thing with the line review editing tools...
it shows the \i [FILE] command, not what the command read. That would work
too, even if it caused a dummy read the first time you used it.

Input, anyone?

--Ben

Ben wrote:
> I'm designing a fairly involved database system. As part fo the process, I
> use the \i [FILE] command a great deal. I set up fairly involved queries,
> sometimes simply for the purpose of shortening column names so the output
> is reasonable. For example:
>
> SELECT longname AS abbr,othername as "V" FROM table WHERE how;
>
> ...a bunch of these can result in a single-line output on the console,
> which is a lot easier to deal with than a dump of the actual field names
> which wraps around and makes you scroll back and forth trying to line up
> the names with the values.
>
> Now, in my case, I'm dealing with specific orders. So the WHERE clause
> might be:
>
> ...WHERE zorder=104788;
>
> Which works fine. But, I have to edit the file every time I'm working with
> a different order, which is repetative and annoying, something computers
> are supposed to save us from. :)
>
> However, you can't leave it out; \i [FILE] expects the query to be
> complete, ready to go to the server. As far as I can tell.
>
> So - how about a command to read a file into the input lines withOUT
> sending it yet, so that its ready to type the last part, such as:
>
> 104788;
>
> In other words, the file would end here:
>
> ...WHERE zorder=104788;
> ^
> |
> |
> ...then I could just type the number, hit enter, and off it would go.
>
> Or even if it has to be complete, right now, you can use \i [FILE] and it
> runs, but you can't edit the thing with the line review editing tools...
> it shows the \i [FILE] command, not what the command read. That would work
> too, even if it caused a dummy read the first time you used it.
>
> Input, anyone?
>
> --Ben
>

I am not sure about this exactly, but a workaround could be using
temporary sequences. I use these a lot in some of my more involved DB
setup scripts.

So for instance in the top level file you have:

-------------------
CREATE SEQUENCE temp_zorder_num_seq;
SELECT setval('temp_zorder_num_seq', 104788);

\i Somefile.sql

DROP SEQUENCE
-------------------

The in any \i file you can just use:

-------------------
INSERT INTO some_table (zorder_num, ...) VALUES
(currval('temp_zorder_num_seq'), ...);
-------------------

All you have to change is the setval at the top of the script. Make sure
you drop the sequences though ;-).

HTH

Nick

Better would be to match Oracle's sqlPlus feature, DEFINE.

The gist of which is that you can create a SQL statement with an "&" (or
other 'defined' character) in it. If DEFINE is ON, then the interpreter
prompts you for the value when it encounters the "&". After getting the
value it then processes the SQL statement.

Here is an example using sqlPlus:

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Feb 27 14:11:18 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production

SQL> select wdresourceid from wdresource where wdresourceid = &my_res_id;
Enter value for my_res_id: 615
old 1: select wdresourceid from wdresource where wdresourceid = &my_res_id
new 1: select wdresourceid from wdresource where wdresourceid = 615

WDRESOURCEID
------------
615

SQL> select wdresourceid from wdresource where wdresourceid = &my_res_id;
Enter value for my_res_id: 1
old 1: select wdresourceid from wdresource where wdresourceid = &my_res_id
new 1: select wdresourceid from wdresource where wdresourceid = 1

no rows selected

You also need the ability to switch off the DEFINE operation in case you
are using a SQL script which contains "&" characters which you don't want
the interpreter to treat as a define.

This would be a cool and useful feature, if it could be implemented in
psql...

John Sidney-Woollett

SQL>
Nick Barr said:
> Ben wrote:
>> I'm designing a fairly involved database system. As part fo the process,
>> I
>> use the \i [FILE] command a great deal. I set up fairly involved
>> queries,
>> sometimes simply for the purpose of shortening column names so the
>> output
>> is reasonable. For example:
>>
>> SELECT longname AS abbr,othername as "V" FROM table WHERE how;
>>
>> ...a bunch of these can result in a single-line output on the console,
>> which is a lot easier to deal with than a dump of the actual field names
>> which wraps around and makes you scroll back and forth trying to line up
>> the names with the values.
>>
>> Now, in my case, I'm dealing with specific orders. So the WHERE clause
>> might be:
>>
>> ...WHERE zorder=104788;
>>
>> Which works fine. But, I have to edit the file every time I'm working
>> with
>> a different order, which is repetative and annoying, something computers
>> are supposed to save us from. :)
>>
>> However, you can't leave it out; \i [FILE] expects the query to be
>> complete, ready to go to the server. As far as I can tell.
>>
>> So - how about a command to read a file into the input lines withOUT
>> sending it yet, so that its ready to type the last part, such as:

--- Nick Barr <nicky(at)chuckie(dot)co(dot)uk> wrote:
> Ben wrote:
> > I'm designing a fairly involved database system.
> As part fo the process, I
> > use the \i [FILE] command a great deal. I set up
> fairly involved queries,
> > sometimes simply for the purpose of shortening
> column names so the output
> > is reasonable. For example:
> >
> > SELECT longname AS abbr,othername as "V" FROM
> table WHERE how;
> >
> > ...a bunch of these can result in a single-line
> output on the console,
> > which is a lot easier to deal with than a dump of
> the actual field names
> > which wraps around and makes you scroll back and
> forth trying to line up
> > the names with the values.

"man psql" is a good thing. Especially the section on
variables, in Ben's case. In summary, you can set a
variable in a psql session by "\set variablename
value", and refer to it in a query by ":variablename".
This works for any value or identifier, i.e. psql
substitutes the variable value for the name before
sending the sql to the backend. Works when used in
script files too. I have used this a lot, and it's
handy.

Also, you may want to look at the "\x" command, and
its variations. This will output column name/value
pairs down the page, which can be handy for viewing
large records.

> >
> > Now, in my case, I'm dealing with specific orders.
> So the WHERE clause
> > might be:
> >
> > ...WHERE zorder=104788;
> >
> > Which works fine. But, I have to edit the file
> every time I'm working with
> > a different order, which is repetative and
> annoying, something computers
> > are supposed to save us from. :)
> >
> > However, you can't leave it out; \i [FILE] expects
> the query to be
> > complete, ready to go to the server. As far as I
> can tell.
> >
> > So - how about a command to read a file into the
> input lines withOUT
> > sending it yet, so that its ready to type the last
> part, such as:
> >
> > 104788;
> >
> > In other words, the file would end here:
> >
> > ...WHERE zorder=104788;
> > ^
> > |
> > |
> > ...then I could just type the number, hit enter,
> and off it would go.
> >
> > Or even if it has to be complete, right now, you
> can use \i [FILE] and it
> > runs, but you can't edit the thing with the line
> review editing tools...
> > it shows the \i [FILE] command, not what the
> command read. That would work
> > too, even if it caused a dummy read the first time
> you used it.
> >
> > Input, anyone?
> >
> > --Ben
> >
>
> I am not sure about this exactly, but a workaround
> could be using
> temporary sequences. I use these a lot in some of my
> more involved DB
> setup scripts.
>
> So for instance in the top level file you have:
>
> -------------------
> CREATE SEQUENCE temp_zorder_num_seq;
> SELECT setval('temp_zorder_num_seq', 104788);
>
> \i Somefile.sql
>
> DROP SEQUENCE
> -------------------
>
> The in any \i file you can just use:
>
> -------------------
> INSERT INTO some_table (zorder_num, ...) VALUES
> (currval('temp_zorder_num_seq'), ...);
> -------------------
>
> All you have to change is the setval at the top of
> the script. Make sure
> you drop the sequences though ;-).
>
>
> HTH
>
> Nick
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

__________________________________
Do you Yahoo!?
Get better spam protection with Yahoo! Mail.
http://antispam.yahoo.com/tools

"Ben" <reply(at)to-the-newsgroup(dot)com> writes:
> So - how about a command to read a file into the input lines withOUT
> sending it yet, so that its ready to type the last part, such as:

I think \e can be used this way.

regards, tom lane

I want to thank everyone for replying - that was really great.

My specific issue sounds like it would be best addressed using variables.
Thanks hugely for the pointer on that one.

Displaying things vertically isn't going to work for me in particular; you
lose too much display area that way. I need to be able to see multiple
results. I'll keep \x in mind for later, though.

\e allows you to edit an external file; this is essentially the problem,
rather than the solution: I don't want to edit it outside. I want it in
the query buffer where I can just hit ^ a few times, edit right there, and
let fly. I do this all the time now, the problem is I have to hand-enter
the query the first time, or I can't do it at all.

What I was suggesting was the ability to load an external file into the
query buffer (the line buffer, maybe I mean) such that (a) it is NOT
executed a'la \i, but can be picked up into the current line for editing
using the ^ arrow, changed as required, then fired off. This is a very
general solution to having things around that you can boilerplate, or
simply use because you know they're close, even if that wasn't the idea
originally.

The idea of using & seems cool enough, but since you can use SET and then
embed them in the external file, that'll do about the same thing, it seems
to me.

Thanks again for all the responses. You people rock.

--Ben