Re: psql: Allow editing query results with \gedit

po 22. 1. 2024 v 23:54 odesílatel Christoph Berg <myon(at)debian(dot)org> napsal:

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

\crosstabview is read only

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

It looks great for simple queries, but if somebody uses it like SELECT *
FROM pg_proc \gedit

I almost sure so \gedit is wrong name for this feature.

Can be nice if we are able:

a) export data set in some readable format

b) be possible to use more command in pipes

some like

select start, call, qrg, name from log where cty = 'CE9' order by start
\gpipexec(tsv) mypipe | bash update_pattern.sh > tmpfile; vi tmpfile; cat
tmpfile > mypipe

I understand your motivation well, but I don't like your proposal because
too many different things are pushed to one feature, and it is designed for
a single purpose.

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
>