Yesterday I talked a bit with David Fetter about improvements in psql. We both would like to get proper stored procedures (with multiple result sets, and transaction control), but since this seems to be too complicated, I thought about what I'd like to have added to psql.
- Ability to assign values to psql variables from SQL queries. Right now it's pretty funny that in SQL client, you can assign values from SHELL commands, but not SQL commands!
Above points should be probably explained a bit more. I would like them to be able to write, in psql, without using external tools, something like:
\SET min_value ~SELECT MIN(id) FROM TABLE; \SET max_value ~SELECT MAX(id) FROM TABLE; UPDATE TABLE SET something = something WHERE id < :min_value; \FOR (i = min_value; i <= max_value; i += 1000 ) UPDATE TABLE SET something = something WHERE id BETWEEN :i AND ( :i + 999 ); \endfor UPDATE TABLE SET something = something WHERE id > :max_value;
i.e. update table in batches of 1000 rows at a time, regardless of actual values of ids, or their range.
The second to last thing is quite big – better tab completion. Don't get me wrong – tab completion in psql is really nice, but it can be definitely improved.
Make tab-completion complete also function names – like: SELECT pg_get<tab><tab> to see all functions that start with pg_get.
Make tab-completion work for columns in SELECT. I know that when writing SELECT clause, psql doesn't know which table it will deal with, but it could search through all the columns in database.
Also, make it work for 2nd (and 3rd, and so on) clause in where. Currently tab-completion can complete column name only in first where clause.
The last thing – make it possible to add our own \commands, or even reassign the preexisting ones.
I, for one, would love to be able to do: \ca, and get some snapshot of info about activity (basically call select on pg_stat_activity). On the other hand, I never used \p, and I don't quite see why I would ever need it, so I might want to remove it, and assign to something that (for me) makes more sense.
Will it ever materialize? No idea, but I can always hope.