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