Re: psql: Allow editing query results with \gedit

Re: David G. Johnston
> Building off the other comments, I'd suggest trying to get rid of the
> intermediate JSOn format and also just focus on a single row at any given
> time.

We need *some* machine-readable format. It doesn't have to be JSON,
but JSON is actually pretty nice to read - and if values are too long,
or there are too many values, switch to extended mode:

select * from messages \gedit (expanded)

[{
"id": "1",
"language": "en",
"message": "This is a very long test text with little actual meaning."
},{
"id": "2",
"language": "en",
"message": "Another one, a bit shorter."
}]

I tweaked the indentation in the psql JSON output patch specifically
to make it readable.

Restricting to a single line might make sense if it helps editing, but
I don't think it does.

> For an update the first argument to the metacommand could be the unique key
> value present in the previous result. The resultant UPDATE would just put
> that into the where clause and every other column in the result would be a
> SET clause column with the thing being set the current value, ready to be
> edited.

Hmm, then you would still have to cut-and-paste the PK value. If that
that's a multi-column non-numeric key, you are basically back to the
original problem.

Re: Tom Lane
> Yeah, that's something that was also bothering me, but I failed to
> put my finger on it. "Here's some JSON, edit it, and don't forget
> to keep the quoting correct" does not strike me as a user-friendly
> way to adjust data content. A spreadsheet-like display where you
> can change data within cells seems like a far better API, although
> I don't want to write that either.

Right. I wouldn't want a form editing system in there either. But
perhaps this middle ground of using a well-established format that is
easy to generate and to parse (it's using the JSON parser from
pgcommon) makes it fit into psql.

If parsing the editor result fails, the user is asked if they want to
re-edit with a parser error message, and if they go to the editor
again, the cursor is placed in the line where the error is. (Also,
what's wrong with having to strictly adhere to some syntax, we are
talking about SQL here.)

It's admittedly larger than the average \backslash command, but it
does fit into psql's interactive usage. \crosstabview is perhaps a
similar thing - it doesn't really fit into a simple "send query and
display result" client, but since it solves an actual problem, it
makes well sense to spend the extra code on it.

> This kind of API would not readily support INSERT or DELETE cases, but
> TBH I think that's better anyway --- you're adding too much ambiguity
> in pursuit of a very secondary use-case. The stated complaint was
> "it's too hard to build UPDATE commands", which I can sympathize with.

I've been using the feature already for some time, and it's a real
relief. In my actual use case here, I use it on my ham radio logbook:

=# select start, call, qrg, name from log where cty = 'CE9' order by start;
start │ call │ qrg │ name
────────────────────────┼────────┼─────────────┼───────
2019-03-12 20:34:00+00 │ RI1ANL │ 7.076253 │ ∅
2021-03-16 21:24:00+00 │ DP0GVN │ 2400.395 │ Felix
2022-01-15 17:19:00+00 │ DP0GVN │ 2400.01 │ Felix
2022-10-23 19:17:15+00 │ DP0GVN │ 2400.041597 │ ∅
2023-10-01 14:05:00+00 │ 8J1RL │ 28.182575 │ ∅
2024-01-22 21:15:15+00 │ DP1POL │ 10.138821 │ ∅
(6 Zeilen)

The primary key is (start, call).

If I now want to note that the last contact with Antarctica there was
also with Felix, I'd have to transform that into

update log set name = 'Felix' where start = '2024-01-22 21:15:15+00' and call = 'DP1POL';

\gedit is just so much easier.

UPDATE is the core feature. If we want to say INSERT and DELETE aren't
supported, but UPDATE support can go in, that'd be fine with me.

> (BTW, I wonder how much of this already exists in pgAdmin.)

pgadmin seems to support it. (Most other clients don't.)

Obviously, I would want to do the updating using the client I also use
for querying.

Christoph