Thread: Re: Simple, but VERYuseful enhancement for psql command - or am I
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@chuckie.co.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