Re: Feature proposal for psql - Mailing list pgsql-hackers

From Corey Huinker
Subject Re: Feature proposal for psql
Date
Msg-id CADkLM=em8EEJgJ+Do1i_ykuDw5YCU7v80FqaFFwdg0L0ddt8WA@mail.gmail.com
Whole thread Raw
In response to Re: Feature proposal for psql  (Denis Gantsev <gantsevdenis@gmail.com>)
List pgsql-hackers



One limitation of this approach is that \set can't span lines, so
writing complex queries would be kinda painful.  But that would
be a good limitation to address separately; \set isn't the only
metacommand where can't-span-lines is a problem sometimes.
If you seriously want to pursue adding a feature like this,
probably the -hackers list is a more appropriate discussion
forum than -novice.

                        regards, tom lane

 The ability to save and retrieve multi-line queries would be quite nice though, often I would like to save a query too large to type.

I think I don't know psql well enough to propose a viable syntax, so I guess that would be up to experts here...
But I would be pretty happy to implement it.

Regards
Denis


Well, if you want to do it right now, you can do this:

db=> select * from foo;
 x  | y  
----+----
  1 |  1
  2 |  2
  3 |  3
  4 |  4
  5 |  5
  6 |  6
  7 |  7
  8 |  8
  9 |  9
 10 | 10
(10 rows)
db=> select * from foo where x = :xval \w query1.sql
db=> \set xval 4
db=> \i query1.sql
 x | y
---+---
 4 | 4
(1 row)

Granted, that involves adding files to the filesystem, setting variables rather than passing parameters, remembering what those variables were, and having the discipline to not have overlapping uses for variable names across multiple files.

So the key shortcomings right now seem to be:
* no way to pass in values to an \i or \ir and no way to locally scope them
* one file per query

Setting variables locally in a \ir would need to somehow push and pop existing variable values because those vars are scoped at the session level, and that might confuse the user when they set the var inside the included file expecting the calling session to keep the value.

Perhaps we could add a notion of a "bag of tricks" dir in each user's home directory, and a slash command \wbag (better name suggestions welcome) that behaves like \w but assumes the file will go in ~/.psql-bag-of-tricks/  and \ibag which includes a file from the same dir. 

pgsql-hackers by date:

Previous
From: Nikita Glukhov
Date:
Subject: Fix inconsistency in jsonpath .datetime()
Next
From: Andrew Dunstan
Date:
Subject: Re: XversionUpgrade tests broken by postfix operator removal