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:

\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.

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.

Will it ever materialize? No idea, but I can always hope.

17 thoughts on “Wish list for psql”

  1. 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.

  2. 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.

  3. What do you mean exactly by “stored procedures” in psql? Term “stored procedures” usually describes what happens at database backend, not the client.

  4. @Filip:
    that’s exactly what I mean. But since we don’t have proper stored procedures, some tasks have to be done on client. And it would be good to have psql able to do these tasks.

  5. I would also add one minor thing to the wishlist: better handling of assignment of psql variables.

    At present, it’s ambiguous, for example:

    \set col1 ‘foo’ as thecode

    makes the variable “col1” with value “fooasthecode”.

    Maybe it should be made more strict.

  6. @depesz: are you saying that postgres user-defined functions are not “proper stored procedures”?

    In common sense, this is not true – UDF can do same, or better, as stored proc in MSSQL, stored proc in Oracle PL/SQL.

    Only notable exception is transaction control.

  7. @Filip:
    that’s *HUGE* exception. The other, perhaps even larger, is lack of multiple result sets.

  8. @depesz, it’s possible to do multiple result sets with RETURNS SETOF REFCURSOR, but the inability to control transactions is pretty fundamental.

  10. 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.

  12. @Depesz

    There was a more reasons:

    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).

    If you like it try it to push it to core

  13. — @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.

  14. Hi. I do not use psql features at all, only sending queries to server. I prefer improvements in server before psql .

  16. I really want to be able to pass arguments to scripts when called via \i. Will this variable setting help with that?

  17. @pavel
    we all would love epsql merged into core. 🙂
    Thumbs up for the good work.

Comments are closed.