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

--- 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