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.