Wish list for psql

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!

Conditionals.

Loops.

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:

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.

For example:

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.

17 thoughts on “Wish list for psql”

I use psql mostly to do some quick checks on some values here and there, some basic updates and such. So at least for me you have a vote for better tab completition!! I can imagine many non dba psql users would find it really, really handy.

Some of the tab completion things you mentioned–like being able to know where in the SQL statement you are and do the right thing, involve a complete reworking of how things are done. A half-way, and I believe unmaintainable way, would be to do some kind of tracking of where in the statement you are using stacks. The up side of this approach is that it could be done incrementally starting from our current tab completion system.

The way we’ll end up with, I believe, is a total rework of the SQL parser including, making it

1. Exported as a library which uses a connection to the DB for the parts that must have catalog access. It may be simpler, at least at first, to assume that every query’s parse will need such access and simply require a connection unconditionally.

2. Able to parse partial SQL, starting from the beginning of a statement.

I hope a near-future version of PostgreSQL will have this capability, as rebuilding this kind of thing for each and every external tool is a waste of those most precious and fickle of resources: developer, tester and end-user time and brainpower.

Just note – developing some scripting features in psql is relative difficult, because you have to handle a interactive mode. Now I prefer some not interactive simplified postgresql client scripting engine – that should be used in pg_bench too – and can be used alone – – moment – we have this – it is pgscript – but this is too heavy – I would to some withh \command syntax in psql. But if someone will write a scripting features to psql, I’ll not against.

I wrote a epsql as prototype and when I didn’t see any significant interest I didn’t continue – a few features I merged to core – but the main features are waiting still. And probably there is some Tom’s resistance against a new complex features to psql.

There a few issues still about macros, but basic functionality like \execute and \forc works pretty well – and I am thinking so it is designed well – (I am not sure about implementation).

— @depesz, it’s possible to do multiple result sets with RETURNS SETOF REFCURSOR

I have a patch (for psql) that will recursively expand any refcursors found in a result set. If you call a function that (for example) returns a set of three refcursors, the patch will cause psql to fetch (and display) each row in the first refcursor (including column headings), then each row in the second refcursor, then finally each row in the third refcursor.