Re: Simple, - Mailing list pgsql-general

From John Sidney-Woollett
Subject Re: Simple,
Date
Msg-id 2870.192.168.0.64.1077891047.squirrel@mercury.wardbrook.com
Whole thread Raw
In response to Re: Simple, but VERYuseful enhancement for psql command - or am I  (Nick Barr <nicky@chuckie.co.uk>)
List pgsql-general
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:


pgsql-general by date:

Previous
From: Bill Moran
Date:
Subject: Re: PostgreSQL insert speed tests
Next
From: Michael Chaney
Date:
Subject: Re: correlated delete with "in" and "left outer join"