Assuming a SELECT statement reading from a single table, it is quite an
effort to transform that statement to an UPDATE statement on that table,
perhaps to fix a typo that the user has spotted in the query result.
First, the general syntax is not the same with the order of syntax
elements changed. Then the row in question needs to be pinned down by
the primary key, requiring cut-and-paste of the PK columns. Furthermore,
the value to be updated needs to be put into the command, with proper
quoting. If the original value spans multiple line, copy-and-pasting it
for editing is especially tedious.
Suppose the following query where we spot a typo in the 2nd message:
=# select id, language, message from messages where language = 'en';
id | language | message
1 | en | Good morning
2 | en | Hello warld
The query needs to be transformed into this update:
=# update messages set message = 'Hello world' where id = 2;
This patch automates the tedious parts by opening the query result in a
editor in JSON format, where the user can edit the data. On closing the
editor, the JSON data is read back, and the differences are sent as
UPDATE commands. New rows are INSERTed, and deleted rows are DELETEd.
=# select id, language, message from messages where language = 'en' \gedit
An editor opens:
[
{ "id": 1, "language": "en", "message": "Good morning" },
{ "id": 2, "language": "en", "message": "Hello warld" }
]
Let's fix the typo and save the file:
[
{ "id": 1, "language": "en", "message": "Good morning" },
{ "id": 2, "language": "en", "message": "Hello world" }
]
UPDATE messages SET message = 'Hello world' WHERE id = '2';
UPDATE 1
In this example, typing "WHERE id = 2" would not be too hard, but the
primary key might be a composite key, with complex non-numeric values.
This is supported as well.
If expanded mode (\x) is enabled, \gedit will use the expanded JSON
format, best suitable for long values.
This patch requires the "psql JSON output format" patch.
Christoph
>From be3fef72af0adac7d3a6962f8cc78e167785a702 Mon Sep 17 00:00:00 2001
From: Christoph Berg <m...@debian.org>
Date: Mon, 22 Jan 2024 14:40:33 +0100
Subject: [PATCH] psql: Allow editing query results with \gedit
Assuming a SELECT statement reading from a single table, it is quite an
effort to transform that statement to an UPDATE statement on that table,
perhaps to fix a typo that the user has spotted in the query result.
First, the general syntax is not the same with the order of syntax
elements changed. Then the row in question needs to be pinned down by
the primary key, requiring cut-and-paste of the PK columns. Furthermore,
the value to be updated needs to be put into the command, with proper
quoting. If the original value spans multiple line, copy-and-pasting it
for editing is especially tedious.
Suppose the following query where we spot a typo in the 2nd message:
=# select id, language, message from messages where language = 'en';
id | language | message
1 | en | Good morning
2 | en | Hello warld
The query needs to be transformed into this update:
=# update messages set message = 'Hello world' where id = 2;
This patch automates the tedious parts by opening the query result in a
editor in JSON format, where the user can edit the data. On closing the
editor, the JSON data is read back, and the differences are sent as
UPDATE commands. New rows are INSERTed, and deleted rows are DELETEd.
=# select id, language, message from messages where language = 'en' \gedit
An editor opens:
[
{ "id": 1, "language": "en", "message": "Good morning" },
{ "id": 2, "language": "en", "message": "Hello warld" }
]
Let's fix the typo and save the file:
[
{ "id": 1, "language": "en", "message": "Good morning" },
{ "id": 2, "language": "en", "message": "Hello world" }
]
UPDATE messages SET message = 'Hello world' WHERE id = '2';
UPDATE 1
In this example, typing "WHERE id = 2" would not be too hard, but the
primary key might be a composite key, with complex non-numeric values.
This is supported as well.
If expanded mode (\x) is enabled, \gedit will use the expanded JSON
format, best suitable for long values:
=# \x
=# select id, language, message from messages where language = 'en' \gedit
[{
"id": 1,
"language": "en",
"message": "Good morning"
},{
"id": 2,
"language": "en",
"message": "Hello world"
}]
\gedit requires the PK (or any UNIQUE key) to be part of the select
statement:
=# select language, message from messages \gedit
\gedit: no key of table "messages" is contained in the returned query columns.
Select more columns or manually specify a key using \gedit (key=...)
If the user knows that their operation is sound even without a PK/UNIQUE
key, they can force a (set of) column(s) to be used:
=# select language, message from messages \gedit (key=message)
[
{ "language": "en", "message": "Good morning" },
{ "language": "en_US", "message": "Hello world" }
]
UPDATE messages SET language = 'en_US' WHERE message = 'Hello world';
UPDATE 1
---
doc/src/sgml/ref/psql-ref.sgml | 62 +++
src/bin/psql/Makefile | 1 +
src/bin/psql/command.c | 119 +++-
src/bin/psql/command.h | 2 +
src/bin/psql/common.c | 63 ++-
src/bin/psql/common.h | 1 +
src/bin/psql/gedit.c | 961 +++++++++++++++++++++++++++++++++
src/bin/psql/gedit.h | 17 +
src/bin/psql/help.c | 2 +
src/bin/psql/mainloop.c | 24 +-
src/bin/psql/meson.build | 2 +
src/bin/psql/settings.h | 3 +
src/bin/psql/t/030_gedit.pl | 106 ++++
src/bin/psql/tab-complete.c | 2 +-
14 files changed, 1360 insertions(+), 5 deletions(-)
create mode 100644 src/bin/psql/gedit.c
create mode 100644 src/bin/psql/gedit.h
create mode 100644 src/bin/psql/t/030_gedit.pl
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index f1f7eda082..5cc8f0d241 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -2334,6 +2334,68 @@ Tue Oct 26 21:40:57 CEST 1999
</varlistentry>
+ <varlistentry id="app-psql-meta-command-gedit">
+ <term><literal>\gedit</literal></term>
+
+ <listitem>
+ <para>
+ Execute the SELECT query, edit the result with an editor, and send back
+ the changes as UPDATE statements.
+ </para>
+
+ <para>
+ If the current query buffer is empty, the most recently sent query
+ is described instead.
+ </para>
+
+ <para>
+ The SELECT query is executed normally on the server. The result is
+ rendered as JSON and opened in external editor. The user can then edit
+ the query result to change values. On saving, the changed fields are
+ sent back to the server as UPDATE statements. Newly added rows are sent
+ as INSERTs, deleted rows are sent as DELETEs.
+<programlisting>
+=> <userinput>SELECT id, language, message FROM messages \gedit</userinput>
+-- editor opens, user edits data, saves and quits
+UPDATE messages SET message = 'Hello world' WHERE id = '2';
+</programlisting>
+ </para>
+
+ <para>
+ In non-expanded display mode, each result row is rendered as one line
+ of JSON. When expanded mode is enabled, result columns are rendered as
+ separate lines. Expanded mode can be forced for this query.
+<programlisting>
+=> <userinput>\gedit (expanded)</userinput>
+</programlisting>
+ </para>
+
+ <para>
+ The table's primary key, or any unique key, must be part of the
+ selected columns. If the table does not have any (or none are
+ selected), and the user knows that the data is sufficiently selective,
+ a key can be manually selected by providing a <literal>key</literal>
+ argument to <literal>\gedit</literal>. It is the user's responsibility
+ to ensure that the provided key selects only the rows they want to
+ change when used in a WHERE clause.
+<programlisting>
+=> <userinput>\gedit (key=language,message)</userinput>
+</programlisting>
+ </para>
+
+ <para>
+ Only SELECT statements on a single table are supported. A simple string
+ parser is used to determine the table name. If that fails, or if a
+ different base table is to be edited (perhaps the query references a
+ non-updatable view), a <literal>table</literal> argument can be provided.
+<programlisting>
+=> <userinput>\gedit (table=documents)</userinput>
+</programlisting>
+ </para>
+ </listitem>
+ </varlistentry>
+
+
<varlistentry id="app-psql-meta-command-getenv">
<term><literal>\getenv <replaceable class="parameter">psql_var</replaceable> <replaceable class="parameter">env_var</replaceable></literal></term>
diff --git a/src/bin/psql/Makefile b/src/bin/psql/Makefile
index 374c4c3ab8..896fd96b70 100644
--- a/src/bin/psql/Makefile
+++ b/src/bin/psql/Makefile
@@ -31,6 +31,7 @@ OBJS = \
copy.o \
crosstabview.o \
describe.o \
+ gedit.o \
help.o \
input.o \
large_obj.o \
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 824900819b..1834fed907 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -36,6 +36,7 @@
#include "fe_utils/print.h"
#include "fe_utils/string_utils.h"
#include "help.h"
+#include "gedit.h"
#include "input.h"
#include "large_obj.h"
#include "libpq-fe.h"
@@ -101,6 +102,8 @@ static backslashResult process_command_g_options(char *first_option,
static backslashResult exec_command_gdesc(PsqlScanState scan_state, bool active_branch);
static backslashResult exec_command_getenv(PsqlScanState scan_state, bool active_branch,
const char *cmd);
+static backslashResult exec_command_gedit(PsqlScanState scan_state, bool active_branch,
+ PQExpBuffer query_buf, PQExpBuffer previous_buf);
static backslashResult exec_command_gexec(PsqlScanState scan_state, bool active_branch);
static backslashResult exec_command_gset(PsqlScanState scan_state, bool active_branch);
static backslashResult exec_command_help(PsqlScanState scan_state, bool active_branch);
@@ -355,6 +358,9 @@ exec_command(const char *cmd,
status = exec_command_gdesc(scan_state, active_branch);
else if (strcmp(cmd, "getenv") == 0)
status = exec_command_getenv(scan_state, active_branch, cmd);
+ else if (strcmp(cmd, "gedit") == 0)
+ status = exec_command_gedit(scan_state, active_branch, query_buf,
+ previous_buf);
else if (strcmp(cmd, "gexec") == 0)
status = exec_command_gexec(scan_state, active_branch);
else if (strcmp(cmd, "gset") == 0)
@@ -1587,6 +1593,115 @@ exec_command_getenv(PsqlScanState scan_state, bool active_branch,
return success ? PSQL_CMD_SKIP_LINE : PSQL_CMD_ERROR;
}
+/*
+ * \gedit -- send query, ask user to edit the result, and transform the diff to
+ * UPDATE commands
+ */
+static backslashResult
+exec_command_gedit(PsqlScanState scan_state, bool active_branch,
+ PQExpBuffer query_buf, PQExpBuffer previous_buf)
+{
+ backslashResult status = PSQL_CMD_SKIP_LINE;
+
+ if (active_branch)
+ {
+ char fnametmp[MAXPGPATH];
+ /* make a temp file to store result */
+#ifndef WIN32
+ const char *tmpdir = getenv("TMPDIR");
+
+ if (!tmpdir)
+ tmpdir = "/tmp";
+#else
+ char tmpdir[MAXPGPATH];
+ int ret;
+
+ ret = GetTempPath(MAXPGPATH, tmpdir);
+ if (ret == 0 || ret > MAXPGPATH)
+ {
+ pg_log_error("could not locate temporary directory: %s",
+ !ret ? strerror(errno) : "");
+ return PSQL_CMD_ERROR;
+ }
+#endif
+
+ /* save settings if not done already, then force format=json */
+ if (pset.gsavepopt == NULL)
+ pset.gsavepopt = savePsetInfo(&pset.popt);
+ pset.popt.topt.format = PRINT_JSON;
+
+ /* Consume pset options through trailing ')' ... */
+ if (! process_command_gedit_options(scan_state, active_branch))
+ goto error;
+
+ /* If query_buf is empty, recall previous query */
+ (void) copy_previous_query(query_buf, previous_buf);
+
+ /* extract table name from query if not already given in options */
+ if (! pset.gedit_table)
+ pset.gedit_table = gedit_table_name(query_buf);
+ if (! pset.gedit_table)
+ {
+ pg_log_error("\\gedit: could not determine table name from query");
+ goto error;
+ }
+
+ /* get table key */
+ if (pset.gedit_key_columns)
+ {
+ if (! gedit_check_key_columns(pset.db, query_buf->data, pset.gedit_key_columns))
+ goto error;
+ }
+ else
+ pset.gedit_key_columns = gedit_table_key_columns(pset.db, query_buf->data, pset.gedit_table);
+ if (! pset.gedit_key_columns)
+ goto error;
+
+ /*
+ * No canonicalize_path() here. EDIT.EXE run from CMD.EXE prepends the
+ * current directory to the supplied path unless we use only
+ * backslashes, so we do that.
+ */
+#ifndef WIN32
+ snprintf(fnametmp, sizeof(fnametmp), "%s%spsql.gedit.%d.json", tmpdir,
+ "/", (int) getpid());
+#else
+ snprintf(fnametmp, sizeof(fnametmp), "%s%spsql.gedit.%d.json", tmpdir,
+ "" /* trailing separator already present */ , (int) getpid());
+#endif
+
+ pset.gfname = pstrdup(fnametmp);
+ pset.gedit_flag = true;
+
+ status = PSQL_CMD_SEND;
+ }
+ else
+ ignore_slash_options(scan_state);
+
+ return status;
+
+error:
+ if (pset.gedit_table)
+ {
+ free(pset.gedit_table);
+ pset.gedit_table = NULL;
+ }
+ if (pset.gedit_key_columns)
+ {
+ char **p;
+ for (p = pset.gedit_key_columns; *p; p++)
+ free(*p);
+ free(pset.gedit_key_columns);
+ pset.gedit_key_columns = NULL;
+ }
+
+ /* Reset the query buffer as though for \r */
+ resetPQExpBuffer(query_buf);
+ psql_scan_reset(scan_state);
+
+ return PSQL_CMD_ERROR;
+}
+
/*
* \gexec -- send query and execute each field of result
*/
@@ -3929,13 +4044,13 @@ UnsyncVariables(void)
/*
- * helper for do_edit(): actually invoke the editor
+ * helper for do_edit() and gedit_build_query(): actually invoke the editor
*
* Returns true on success, false if we failed to invoke the editor or
* it returned nonzero status. (An error message is printed for failed-
* to-invoke cases, but not if the editor returns nonzero status.)
*/
-static bool
+bool
editFile(const char *fname, int lineno)
{
const char *editorName;
diff --git a/src/bin/psql/command.h b/src/bin/psql/command.h
index b41065d5a9..c3ae9958c9 100644
--- a/src/bin/psql/command.h
+++ b/src/bin/psql/command.h
@@ -46,4 +46,6 @@ extern void SyncVariables(void);
extern void UnsyncVariables(void);
+extern bool editFile(const char *fname, int lineno);
+
#endif /* COMMAND_H */
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 76e01b02a3..d2f6fdf01a 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -12,6 +12,8 @@
#include <math.h>
#include <pwd.h>
#include <signal.h>
+#include <sys/stat.h>
+#include <utime.h>
#ifndef WIN32
#include <unistd.h> /* for write() */
#else
@@ -83,6 +85,63 @@ openQueryOutputFile(const char *fname, FILE **fout, bool *is_pipe)
return true;
}
+
+/*
+ * editFileWithCheck -- invoke editor and check if the file was touched
+ */
+bool
+editFileWithCheck(const char *fname, int lineno, bool *edited)
+{
+ bool error = false;
+ struct stat before,
+ after;
+
+ {
+ struct utimbuf ut;
+
+ /*
+ * Try to set the file modification time of the temporary file
+ * a few seconds in the past. Otherwise, the low granularity
+ * (one second, or even worse on some filesystems) that we can
+ * portably measure with stat(2) could lead us to not
+ * recognize a modification, if the user typed very quickly.
+ *
+ * This is a rather unlikely race condition, so don't error
+ * out if the utime(2) call fails --- that would make the cure
+ * worse than the disease.
+ */
+ ut.modtime = ut.actime = time(NULL) - 2;
+ (void) utime(fname, &ut);
+ }
+
+ if (!error && stat(fname, &before) != 0)
+ {
+ pg_log_error("%s: %m", fname);
+ error = true;
+ }
+
+ /* call editor */
+ if (!error)
+ error = !editFile(fname, lineno);
+
+ if (!error && stat(fname, &after) != 0)
+ {
+ pg_log_error("%s: %m", fname);
+ error = true;
+ }
+
+ /* file was edited if the size or modification time has changed */
+ if (!error &&
+ (before.st_size != after.st_size ||
+ before.st_mtime != after.st_mtime))
+ {
+ *edited = true;
+ }
+
+ return !error;
+}
+
+
/*
* setQFout
* -- handler for -o command line option and \o command
@@ -1228,7 +1287,7 @@ sendquery_cleanup:
ResetCancelConn();
/* reset \g's output-to-filename trigger */
- if (pset.gfname)
+ if (pset.gfname && !pset.gedit_flag)
{
free(pset.gfname);
pset.gfname = NULL;
@@ -1261,6 +1320,8 @@ sendquery_cleanup:
/* reset \gdesc trigger */
pset.gdesc_flag = false;
+ /* we don't reset gedit_flag here, it's still needed in the main loop */
+
/* reset \gexec trigger */
pset.gexec_flag = false;
diff --git a/src/bin/psql/common.h b/src/bin/psql/common.h
index 6efe12274f..6e97c3ae0e 100644
--- a/src/bin/psql/common.h
+++ b/src/bin/psql/common.h
@@ -16,6 +16,7 @@
#include "libpq-fe.h"
extern bool openQueryOutputFile(const char *fname, FILE **fout, bool *is_pipe);
+extern bool editFileWithCheck(const char *fname, int lineno, bool *edited);
extern bool setQFout(const char *fname);
extern char *psql_get_variable(const char *varname, PsqlScanQuoteType quote,
diff --git a/src/bin/psql/gedit.c b/src/bin/psql/gedit.c
new file mode 100644
index 0000000000..d77d8ffb70
--- /dev/null
+++ b/src/bin/psql/gedit.c
@@ -0,0 +1,961 @@
+/*
+ * psql - the PostgreSQL interactive terminal
+ *
+ * Copyright (c) 2023, PostgreSQL Global Development Group
+ *
+ * src/bin/psql/gedit.c
+ */
+
+#include <sys/stat.h>
+#include <time.h>
+#include <utime.h>
+
+#include "postgres_fe.h"
+#include "catalog/pg_type_d.h"
+#include "command.h"
+#include "common.h"
+#include "common/jsonapi.h"
+#include "common/logging.h"
+#include "common/string.h"
+#include "lib/ilist.h"
+#include "pqexpbuffer.h"
+#include "psqlscanslash.h"
+#include "settings.h"
+
+#include "gedit.h"
+
+/*
+ * Process a single option for \gedit
+ */
+static bool
+process_command_gedit_option(char *option, char *valptr)
+{
+ bool success = false;
+
+ if (strcmp(option, "table") == 0)
+ {
+ if (valptr && *valptr)
+ {
+ if (pset.gedit_table)
+ free(pset.gedit_table);
+ pset.gedit_table = strdup(valptr);
+ success = true;
+ } else
+ pg_log_error("\\gedit: missing table name in table option");
+ }
+ else if (strcmp(option, "key") == 0)
+ {
+ if (valptr && *valptr)
+ {
+ char *p;
+ int nkeycols = 1;
+ char *saveptr = NULL;
+ int i = 0;
+ char *tok;
+
+ /* count number of tokens. Currently, this assumes no commas in table names and no quoting */
+ for (p = valptr; *p; p++)
+ {
+ if (*p == ',')
+ nkeycols++;
+ }
+
+ Assert(! pset.gedit_key_columns);
+ pset.gedit_key_columns = calloc(nkeycols + 1, sizeof(char *));
+ if (! pset.gedit_key_columns)
+ {
+ pg_log_error("\\gedit: out of memory");
+ return false;
+ }
+
+ do {
+ tok = strtok_r(saveptr ? NULL : valptr, ",", &saveptr);
+ if (tok)
+ pset.gedit_key_columns[i++] = strdup(tok);
+ } while (tok);
+ pset.gedit_key_columns[i] = NULL; /* NULL-terminate array */
+
+ success = true;
+
+ } else
+ pg_log_error("\\gedit: missing column names in key option");
+ }
+ else if (strcmp(option, "x") == 0 || strcmp(option, "expanded") == 0 || strcmp(option, "vertical") == 0)
+ {
+ success = do_pset("expanded", valptr, &pset.popt, true);
+ }
+ else
+ pg_log_error("\\gedit: unknown option \"%s\"", option);
+
+ return success;
+}
+
+/*
+ * Process parenthesized options for \gedit
+ */
+bool
+process_command_gedit_options(PsqlScanState scan_state, bool active_branch)
+{
+ bool success = true;
+ bool first_option = true;
+ bool found_r_paren = false;
+
+ do
+ {
+ char *option;
+ size_t optlen;
+
+ option = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, false);
+ if (!option)
+ {
+ if (active_branch && !first_option)
+ {
+ pg_log_error("\\gedit: missing right parenthesis in options");
+ success = false;
+ }
+ break;
+ }
+
+ /* Skip over '(' in first option */
+ if (first_option)
+ {
+ char *tmp;
+
+ if (option[0] != '(')
+ {
+ pg_log_error("\\gedit: missing left parenthesis in options");
+ success = false;
+ break;
+ }
+
+ tmp = strdup(option + 1);
+ free(option);
+ option = tmp;
+ first_option = false;
+ }
+
+ /* Check for terminating right paren, and remove it from string */
+ optlen = strlen(option);
+ if (optlen > 0 && option[optlen - 1] == ')')
+ {
+ option[--optlen] = '\0';
+ found_r_paren = true;
+ }
+
+ /* If there was anything besides parentheses, parse/execute it */
+ if (optlen > 0)
+ {
+ /* We can have either "name" or "name=value" */
+ char *valptr = strchr(option, '=');
+
+ if (valptr)
+ *valptr++ = '\0';
+ if (active_branch)
+ success &= process_command_gedit_option(option, valptr);
+ }
+
+ free(option);
+ } while (!found_r_paren);
+
+ return success;
+}
+
+/*
+ * Extract table name from query
+ */
+char *
+gedit_table_name(PQExpBuffer query_buf)
+{
+ char *query = strdup(query_buf->data);
+ char *tok;
+ char *saveptr = NULL;
+ char *res = NULL;
+
+ do {
+ tok = strtok_r(saveptr ? NULL : query, " \t", &saveptr);
+ if (tok && (strcasecmp(tok, "FROM") == 0 || strcasecmp(tok, "TABLE") == 0))
+ {
+ if ((res = strtok_r(NULL, " \t", &saveptr))) /* next word or NULL */
+ {
+ int l = strlen(res);
+ Assert (l > 0);
+
+ res = strdup(res);
+ if (res[l-1] == ';') /* strip trailing ; */
+ res[l-1] = '\0';
+ }
+ break;
+ }
+ } while (tok != NULL);
+
+ free(query);
+
+ return res;
+}
+
+/*
+ * Append one array-quoted value to a buffer.
+ */
+static void
+PQExpBufferAppendArrayValue(PQExpBuffer buf, const char *value)
+{
+ const char *p;
+ appendPQExpBufferChar(buf, '"');
+ for (p = value; *p; p++)
+ {
+ if (*p == '"')
+ appendPQExpBufferStr(buf, "\\\"");
+ else if (*p == '\\')
+ appendPQExpBufferStr(buf, "\\\\");
+ else
+ appendPQExpBufferChar(buf, *p);
+ }
+ appendPQExpBufferChar(buf, '"');
+}
+
+/*
+ * Describe a query, and return the list of columns as an array literal.
+ */
+static bool
+gedit_get_query_columns(PGconn *conn, const char *query, PQExpBuffer query_columns)
+{
+ PGresult *result = PQprepare(conn, "", query, 0, NULL);
+ if (PQresultStatus(result) != PGRES_COMMAND_OK)
+ {
+ pg_log_error("%s", PQerrorMessage(pset.db));
+ PQclear(result);
+ return false;
+ }
+ PQclear(result);
+
+ result = PQdescribePrepared(pset.db, "");
+ if (PQresultStatus(result) == PGRES_COMMAND_OK)
+ {
+ appendPQExpBufferChar(query_columns, '{');
+ if (PQnfields(result) > 0)
+ {
+ int i;
+ for (i = 0; i < PQnfields(result); i++)
+ {
+ char *name;
+ name = PQfname(result, i);
+ if (i > 0)
+ appendPQExpBufferChar(query_columns, ',');
+ PQExpBufferAppendArrayValue(query_columns, name);
+ }
+ }
+ appendPQExpBufferChar(query_columns, '}');
+ }
+
+ return true;
+}
+
+/*
+ * Given a query and a table, get a table key that is contained in the query
+ * columns.
+ */
+char **
+gedit_table_key_columns(PGconn *conn, const char *query, const char *table)
+{
+ PQExpBuffer query_columns = createPQExpBuffer();
+ char key_query[] =
+ "WITH keys AS (SELECT array_agg(attname ORDER BY ordinality) keycols, indisprimary "
+ "FROM pg_index, unnest(indkey) with ordinality u(keycol) "
+ "JOIN pg_attribute ON keycol = attnum "
+ "WHERE indrelid = $1::regclass AND attrelid = $1::regclass AND (indisprimary or indisunique) "
+ "GROUP BY indexrelid), "
+ "key AS (SELECT keycols FROM keys WHERE keycols <@ $2 " /* all keys contained in the query columns */
+ "ORDER BY indisprimary DESC LIMIT 1) " /* prefer primary key over unique indexes */
+ "SELECT unnest(keycols) FROM key;";
+ Oid types[] = { NAMEOID, NAMEARRAYOID };
+ const char *values[] = { table, NULL };
+ char **gedit_key_columns = NULL;
+ PGresult *res;
+
+ if (! gedit_get_query_columns(pset.db, query, query_columns))
+ {
+ destroyPQExpBuffer(query_columns);
+ return NULL;
+ }
+ values[1] = query_columns->data;
+ res = PQexecParams(conn, key_query, 2, types, values, NULL, NULL, 0);
+ destroyPQExpBuffer(query_columns);
+
+ if (PQresultStatus(res) == PGRES_TUPLES_OK)
+ {
+ int nkeycols = PQntuples(res);
+ if (nkeycols > 0)
+ {
+ int i;
+ gedit_key_columns = calloc(nkeycols + 1, sizeof(char *));
+ if (! gedit_key_columns)
+ {
+ pg_log_error("\\gedit: Out of memory.");
+ return NULL;
+ }
+
+ for (i = 0; i < nkeycols; i++)
+ {
+ gedit_key_columns[i] = strdup(PQgetvalue(res, i, 0));
+ }
+ }
+ else
+ {
+ pg_log_error("\\gedit: no key of table \"%s\" is contained in the returned query columns.", table);
+ pg_log_error_hint("Select more columns or manually specify a key using \\gedit (key=...)");
+ }
+ }
+ else if (PQresultStatus(res) == PGRES_FATAL_ERROR)
+ {
+ char *val = PQresultErrorField(res, PG_DIAG_SQLSTATE);
+ if (strcmp(val, "42P01") == 0)
+ pg_log_error("\\gedit: table \"%s\" does not exist", table);
+ else
+ pg_log_error("\\gedit: error while retrieving key columns of table \"%s\": %s",
+ table, PQerrorMessage(pset.db));
+ }
+ PQclear(res);
+
+ return gedit_key_columns;
+}
+
+/*
+ * Check if a query contains the given columns.
+ */
+bool
+gedit_check_key_columns(PGconn *conn, const char *query, char **key_columns)
+{
+ char **key_column;
+ PGresult *result = PQprepare(conn, "", query, 0, NULL);
+
+ if (PQresultStatus(result) != PGRES_COMMAND_OK)
+ {
+ pg_log_error("\\gedit: %s", PQerrorMessage(conn));
+ PQclear(result);
+ return false;
+ }
+ PQclear(result);
+
+ result = PQdescribePrepared(conn, "");
+ if (PQresultStatus(result) != PGRES_COMMAND_OK)
+ {
+ pg_log_error("\\gedit: %s", PQerrorMessage(conn));
+ PQclear(result);
+ return false;
+ }
+
+ if (PQnfields(result) == 0)
+ {
+ pg_log_error("\\gedit: query has no columns");
+ PQclear(result);
+ return false;
+ }
+
+ for (key_column = key_columns; *key_column; key_column++)
+ {
+ bool found = false;
+ int i;
+ for (i = 0; i < PQnfields(result); i++)
+ {
+ if (strcmp(*key_column, PQfname(result, i)) == 0)
+ {
+ found = true;
+ break;
+ }
+ }
+
+ if (! found)
+ {
+ pg_log_error("\\gedit: key column \"%s\" not found in query", *key_column);
+ PQclear(result);
+ return false;
+ }
+ }
+
+ PQclear(result);
+ return true;
+}
+
+/* JSON parsing */
+
+typedef struct
+{
+ char *name;
+ char *value;
+ slist_node cell_node;
+} Cell;
+
+static void
+cell_free (Cell *cell1)
+{
+ if (cell1->name)
+ free(cell1->name);
+ if (cell1->value)
+ free(cell1->value);
+ free(cell1);
+}
+
+typedef struct
+{
+ slist_head cells;
+ slist_node row_node;
+} Row;
+
+static void
+row_free (Row *row1)
+{
+ slist_mutable_iter cell_iter1;
+
+ slist_foreach_modify(cell_iter1, &row1->cells)
+ {
+ Cell *cell1 = slist_container(Cell, cell_node, cell_iter1.cur);
+ cell_free(cell1);
+ }
+
+ free(row1);
+}
+
+static void
+data_free (slist_head *data)
+{
+ slist_mutable_iter row_iter1;
+
+ slist_foreach_modify(row_iter1, data)
+ {
+ Row *row1 = slist_container(Row, row_node, row_iter1.cur);
+ row_free(row1);
+ }
+}
+
+typedef enum
+{
+ GEDIT_JSON_TOP,
+ GEDIT_JSON_ARRAY,
+ GEDIT_JSON_OBJECT,
+} gedit_context_type;
+
+typedef struct
+{
+ gedit_context_type context;
+ char *fieldname;
+ slist_head *data;
+ Row *current_row;
+ Cell *current_cell;
+} gedit_parse_state;
+
+static JsonParseErrorType
+gedit_array_start_action(void *state)
+{
+ gedit_parse_state *pstate = state;
+
+ if (pstate->context != GEDIT_JSON_TOP)
+ {
+ pg_log_error("\\gedit: arrays are only permitted as the top-level syntax element");
+ return JSON_SEM_ACTION_FAILED;
+ }
+ pstate->context = GEDIT_JSON_ARRAY;
+
+ return JSON_SUCCESS;
+}
+
+static JsonParseErrorType
+gedit_object_start_action(void *state)
+{
+ gedit_parse_state *pstate = state;
+ Row *row = calloc(sizeof(Row), 1);
+
+ if (pstate->context != GEDIT_JSON_ARRAY)
+ {
+ pg_log_error("\\gedit: objects are only permitted inside the top-level array");
+ return JSON_SEM_ACTION_FAILED;
+ }
+ pstate->context = GEDIT_JSON_OBJECT;
+
+ if(slist_is_empty(pstate->data))
+ slist_push_head(pstate->data, &row->row_node);
+ else
+ slist_insert_after(&pstate->current_row->row_node, &row->row_node);
+ pstate->current_row = row;
+
+ return JSON_SUCCESS;
+}
+
+static JsonParseErrorType
+gedit_object_end_action(void *state)
+{
+ gedit_parse_state *pstate = state;
+ pstate->context = GEDIT_JSON_ARRAY;
+ return JSON_SUCCESS;
+}
+
+static JsonParseErrorType
+gedit_object_field_start_action (void *state, char *fname, bool isnull)
+{
+ gedit_parse_state *pstate = state;
+ Cell *cell = calloc(sizeof(Cell), 1);
+
+ cell->name = fname;
+
+ if(slist_is_empty(&pstate->current_row->cells))
+ slist_push_head(&pstate->current_row->cells, &cell->cell_node);
+ else
+ slist_insert_after(&pstate->current_cell->cell_node, &cell->cell_node);
+ pstate->current_cell = cell;
+
+ return JSON_SUCCESS;
+}
+
+static JsonParseErrorType
+gedit_scalar_action(void *state, char *token, JsonTokenType tokentype)
+{
+ gedit_parse_state *pstate = state;
+ if (pstate->context != GEDIT_JSON_OBJECT)
+ {
+ pfree(token);
+ pg_log_error("\\gedit: scalars are only permitted inside objects");
+ return JSON_SEM_ACTION_FAILED;
+ }
+
+ /* when token "null", leave value NULL */
+ if (tokentype == JSON_TOKEN_NULL)
+ {
+ pfree(token);
+ pstate->current_cell->value = NULL;
+ }
+ else
+ pstate->current_cell->value = token;
+
+ return JSON_SUCCESS;
+}
+
+static bool
+read_json_file(const char *fname, int *line_number, slist_head *data)
+{
+ bool success = false;
+ FILE *stream = NULL;
+ char line[1024];
+ PQExpBuffer jsondata = createPQExpBuffer();
+
+ gedit_parse_state parse_state = {
+ .data = data
+ };
+ JsonSemAction geditSemAction = {
+ .semstate = &parse_state,
+ .array_start = gedit_array_start_action,
+ .object_start = gedit_object_start_action,
+ .object_end = gedit_object_end_action,
+ .object_field_start = gedit_object_field_start_action,
+ .scalar = gedit_scalar_action,
+ };
+ JsonLexContext jsonlexer;
+ JsonParseErrorType parseresult;
+
+ if (PQExpBufferBroken(jsondata))
+ {
+ pg_log_error("PQresultErrorField: out of memory");
+ return false;
+ }
+
+ if (!(stream = fopen(fname, PG_BINARY_R)))
+ {
+ pg_log_error("%s: %m", fname);
+ goto error;
+ }
+
+ while (fgets(line, sizeof(line), stream) != NULL)
+ appendPQExpBufferStr(jsondata, line);
+
+ if (ferror(stream))
+ {
+ pg_log_error("%s: %m", fname);
+ goto error;
+ }
+
+ makeJsonLexContextCstringLen(&jsonlexer, jsondata->data, jsondata->len, pset.encoding, true);
+ parseresult = pg_parse_json(&jsonlexer, &geditSemAction);
+ if (line_number)
+ *line_number = jsonlexer.line_number;
+
+ success = (parseresult == JSON_SUCCESS);
+
+error:
+ destroyPQExpBuffer(jsondata);
+ if (stream)
+ fclose(stream);
+
+ return success;
+}
+
+/* main code */
+
+/* NULL-aware variant of strcmp() */
+static bool
+str_distinct(char *a, char *b)
+{
+ if ((a == NULL) && (b == NULL))
+ return false;
+ if ((a == NULL) || (b == NULL))
+ return true;
+ return strcmp(a, b) != 0;
+}
+
+/*
+ * Check if value consists entirely of alphanumeric chars and the first
+ * character is not a digit.
+ */
+static bool
+identifier_needs_no_quoting(const char *value)
+{
+ return strspn(value, "abcdefghijklmnopqrstuvwxyz_0123456789") == strlen(value) &&
+ strspn(value, "0123456789") == 0;
+}
+
+/* NULL-aware variant of quote_literal */
+static void
+quote_literal_or_null(PQExpBuffer query_buf, char *value, bool as_ident)
+{
+ char *p;
+
+ if (!value)
+ {
+ appendPQExpBufferStr(query_buf, "NULL");
+ return;
+ }
+
+ if (as_ident)
+ {
+ if (identifier_needs_no_quoting(value)) /* skip quoting of simple names */
+ appendPQExpBufferStr(query_buf, value);
+ else
+ {
+ p = PQescapeIdentifier(pset.db, value, strlen(value));
+ appendPQExpBufferStr(query_buf, p);
+ free(p);
+ }
+ }
+ else
+ {
+ p = PQescapeLiteral(pset.db, value, strlen(value));
+ appendPQExpBufferStr(query_buf, p);
+ free(p);
+ }
+}
+
+static Row *
+find_matching_row(Row *row1, slist_head *data2, PQExpBuffer where_clause)
+{
+ slist_mutable_iter row_iter2;
+
+ slist_foreach_modify(row_iter2, data2)
+ {
+ Row *row2 = slist_container(Row, row_node, row_iter2.cur);
+ char **key_column;
+
+ for (key_column = pset.gedit_key_columns; *key_column; key_column++)
+ {
+ slist_iter cell_iter1;
+ Cell *cell1;
+
+ slist_foreach(cell_iter1, &row1->cells)
+ {
+ slist_iter cell_iter2;
+ cell1 = slist_container(Cell, cell_node, cell_iter1.cur);
+
+ if (strcmp(*key_column, cell1->name) != 0)
+ continue; /* look at next column */
+
+ slist_foreach(cell_iter2, &row2->cells)
+ {
+ Cell *cell2 = slist_container(Cell, cell_node, cell_iter2.cur);
+ if (strcmp(*key_column, cell2->name) != 0)
+ continue; /* look at next column */
+
+ if (str_distinct(cell1->value, cell2->value) != 0)
+ goto next_row; /* key column value doesn't match, look at next row */
+
+ /* matching column found */
+ if (key_column == pset.gedit_key_columns) /* first key column */
+ printfPQExpBuffer(where_clause, " WHERE ");
+ else
+ appendPQExpBufferStr(where_clause, " AND ");
+ quote_literal_or_null(where_clause, cell1->name, true);
+ appendPQExpBufferStr(where_clause, " = ");
+ quote_literal_or_null(where_clause, cell1->value, false);
+
+ goto next_key; /* look at next key column */
+ }
+ /* key column not found in edited result; ignore row*/
+ goto next_row;
+ }
+ /* should not happen, report error and move on */
+ pg_log_error("\\gedit: key column \"%s\" not found in original result", *key_column);
+ return NULL;
+
+next_key:
+ }
+
+ /* all columns matched */
+ slist_delete_current(&row_iter2); /* caller needs to free row */
+ appendPQExpBufferStr(where_clause, ";");
+ return row2;
+
+next_row:
+ }
+
+ return NULL;
+}
+
+static void
+generate_where_clause(PQExpBuffer query_buf, Row *row1)
+{
+ char **key_column;
+
+ appendPQExpBuffer(query_buf, " WHERE ");
+
+ for (key_column = pset.gedit_key_columns; *key_column; key_column++)
+ {
+ slist_iter cell_iter1;
+
+ slist_foreach(cell_iter1, &row1->cells)
+ {
+ Cell *cell1 = slist_container(Cell, cell_node, cell_iter1.cur);
+
+ if (strcmp(*key_column, cell1->name) == 0)
+ {
+ if (key_column != pset.gedit_key_columns) /* not the first key column */
+ appendPQExpBufferStr(query_buf, " AND ");
+ quote_literal_or_null(query_buf, cell1->name, true);
+ appendPQExpBufferStr(query_buf, " = ");
+ quote_literal_or_null(query_buf, cell1->value, false);
+ }
+ }
+ }
+
+ appendPQExpBufferStr(query_buf, ";");
+}
+
+#define APPEND_NEWLINE \
+ if (cmd_started) \
+ appendPQExpBufferChar(query_buf, '\n'); \
+ cmd_started = true
+
+#define APPEND_COMMA \
+ if (list_started) \
+ appendPQExpBufferStr(query_buf, ", "); \
+ list_started = true
+
+static bool
+generate_update_commands(PQExpBuffer query_buf, slist_head *data1, slist_head *data2)
+{
+ slist_iter row_iter1, row_iter2;
+ bool cmd_started = false;
+ PQExpBuffer where_clause = createPQExpBuffer();
+
+ /* look for differing rows, produce UPDATE and DELETE statements */
+ slist_foreach(row_iter1, data1)
+ {
+ Row *row1 = slist_container(Row, row_node, row_iter1.cur);
+ Row *row2 = find_matching_row(row1, data2, where_clause);
+ slist_mutable_iter cell_iter1, cell_iter2;
+ bool list_started = false;
+
+ /* no matching row was found, DELETE original row */
+ if (!row2)
+ {
+ APPEND_NEWLINE;
+ appendPQExpBuffer(query_buf, "DELETE FROM %s", pset.gedit_table);
+ generate_where_clause(query_buf, row1);
+ continue;
+ }
+
+ /* loop over both rows */
+ slist_foreach_modify(cell_iter1, &row1->cells)
+ {
+ Cell *cell1 = slist_container(Cell, cell_node, cell_iter1.cur);
+
+ slist_foreach_modify(cell_iter2, &row2->cells)
+ {
+ Cell *cell2 = slist_container(Cell, cell_node, cell_iter2.cur);
+
+ if (strcmp(cell1->name, cell2->name) != 0)
+ continue; /* cell names do not match */
+
+ if (str_distinct(cell1->value, cell2->value))
+ {
+ if (!list_started)
+ {
+ APPEND_NEWLINE;
+ appendPQExpBuffer(query_buf, "UPDATE %s SET ", pset.gedit_table);
+ }
+ APPEND_COMMA;
+ quote_literal_or_null(query_buf, cell2->name, true);
+ appendPQExpBufferStr(query_buf, " = ");
+ quote_literal_or_null(query_buf, cell2->value, false);
+ }
+
+ /* no need to look again at these cells, delete them */
+ slist_delete_current(&cell_iter1);
+ cell_free(cell1);
+ slist_delete_current(&cell_iter2);
+ cell_free(cell2);
+
+ break;
+ }
+ }
+
+ /* any cells left in row2 were manually added, include them in the UPDATE */
+ slist_foreach_modify(cell_iter2, &row2->cells)
+ {
+ Cell *cell2 = slist_container(Cell, cell_node, cell_iter2.cur);
+ if (list_started)
+ appendPQExpBufferStr(query_buf, ",");
+ else
+ {
+ APPEND_NEWLINE;
+ appendPQExpBuffer(query_buf, "UPDATE %s SET", pset.gedit_table);
+ list_started = true;
+ }
+ appendPQExpBufferStr(query_buf, " ");
+ quote_literal_or_null(query_buf, cell2->name, true);
+ appendPQExpBufferStr(query_buf, " = ");
+ quote_literal_or_null(query_buf, cell2->value, false);
+
+ slist_delete_current(&cell_iter2);
+ cell_free(cell2);
+ }
+ /*
+ * Any cells left in row1 were deleted while editing, ignore them.
+ * (We could set them NULL, but let's better require the user do that
+ * explicitly.)
+ */
+
+ if (list_started)
+ appendPQExpBufferStr(query_buf, where_clause->data);
+
+ row_free(row2);
+ }
+
+ /* all rows left in data2 are newly added, produce INSERT statements */
+ slist_foreach(row_iter2, data2)
+ {
+ Row *row2 = slist_container(Row, row_node, row_iter2.cur);
+ slist_iter cell_iter2;
+ bool list_started = false;
+
+ APPEND_NEWLINE;
+ appendPQExpBuffer(query_buf, "INSERT INTO %s (", pset.gedit_table);
+
+ slist_foreach(cell_iter2, &row2->cells)
+ {
+ Cell *cell2 = slist_container(Cell, cell_node, cell_iter2.cur);
+ APPEND_COMMA;
+ quote_literal_or_null(query_buf, cell2->name, true);
+ }
+
+ appendPQExpBuffer(query_buf, ") VALUES (");
+ list_started = false;
+
+ slist_foreach(cell_iter2, &row2->cells)
+ {
+ Cell *cell2 = slist_container(Cell, cell_node, cell_iter2.cur);
+ APPEND_COMMA;
+ quote_literal_or_null(query_buf, cell2->value, false);
+ }
+
+ appendPQExpBuffer(query_buf, ");");
+ }
+
+ if (cmd_started)
+ printf("%s\n", query_buf->data);
+
+ destroyPQExpBuffer(where_clause);
+ return cmd_started;
+}
+
+/*
+ * gedit_edit_and_build_query -- handler for \gedit
+ *
+ * This function reads the query result in JSON format and calls an editor. If
+ * the file was edited, it reads the file contents again, and puts the diff
+ * into the query buffer as INSERT/UPDATE/DELETE commands.
+ */
+backslashResult
+gedit_edit_and_build_query(const char *fname, PQExpBuffer query_buf)
+{
+ bool error = false;
+ bool edited = false;
+ int line_number = 0;
+ slist_head data1 = {0};
+ slist_head data2 = {0};
+ backslashResult result = PSQL_CMD_SKIP_LINE;
+
+ if (!read_json_file(fname, NULL, &data1))
+ {
+ pg_log_error("%s: invalid json data even before editing", fname);
+ error = true;
+ }
+
+ while (!error)
+ {
+ char *retry;
+ bool do_retry;
+
+ /* call editor */
+ edited = false;
+ error = !editFileWithCheck(fname, line_number, &edited);
+
+ if (!edited)
+ break;
+ if (error)
+ {
+ pg_log_error("%s: %m", fname);
+ continue;
+ }
+ if (read_json_file(fname, &line_number, &data2))
+ break;
+
+ /* loop until edit produces valid json */
+ retry = simple_prompt(_("Edit again? [y] "), true);
+ if (!retry)
+ {
+ pg_log_error("simple_prompt: out of memory");
+ error = true;
+ break;
+ }
+ do_retry = (*retry == '\0' || *retry == 'y');
+ free(retry);
+ if (!do_retry)
+ {
+ error = true;
+ break;
+ }
+ }
+
+ /* remove temp file */
+ if (remove(fname) == -1)
+ {
+ pg_log_error("%s: %m", fname);
+ error = true;
+ }
+
+ if (error)
+ result = PSQL_CMD_ERROR;
+ else if (edited)
+ {
+ /* generate command to run from diffing data1 and data2 */
+ if (generate_update_commands(query_buf, &data1, &data2))
+ result = PSQL_CMD_NEWEDIT;
+ }
+ if (result == PSQL_CMD_SKIP_LINE) /* either file wasn't edited, or editing didn't change data */
+ pg_log_info("\\gedit: no changes");
+
+ if (data1.head.next)
+ data_free(&data1);
+ if (data2.head.next)
+ data_free(&data2);
+
+ return result;
+}
+
+
diff --git a/src/bin/psql/gedit.h b/src/bin/psql/gedit.h
new file mode 100644
index 0000000000..15c9c2e5c5
--- /dev/null
+++ b/src/bin/psql/gedit.h
@@ -0,0 +1,17 @@
+/*
+ * psql - the PostgreSQL interactive terminal
+ *
+ * Copyright (c) 2023, PostgreSQL Global Development Group
+ *
+ * src/bin/psql/gedit.h
+ */
+#ifndef GEDIT_H
+#define GEDIT_H
+
+extern bool process_command_gedit_options(PsqlScanState scan_state, bool active_branch);
+extern char *gedit_table_name(PQExpBuffer query_buf);
+extern char **gedit_table_key_columns(PGconn *conn, const char *query, const char *table);
+extern bool gedit_check_key_columns(PGconn *conn, const char *query, char **key_columns);
+extern backslashResult gedit_edit_and_build_query(const char *filename_arg, PQExpBuffer query_buf);
+
+#endif
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 3950ffc2c6..ec5f9c39cb 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -172,6 +172,8 @@ slashUsage(unsigned short int pager)
HELP0(" \\g [(OPTIONS)] [FILE] execute query (and send result to file or |pipe);\n"
" \\g with no arguments is equivalent to a semicolon\n");
HELP0(" \\gdesc describe result of query, without executing it\n");
+ HELP0(" \\gedit execute query, edit the result with an external editor,\n");
+ HELP0(" and send back the changes as UPDATE statements\n");
HELP0(" \\gexec execute query, then execute each value in its result\n");
HELP0(" \\gset [PREFIX] execute query and store result in psql variables\n");
HELP0(" \\gx [(OPTIONS)] [FILE] as \\g, but forces expanded output mode\n");
diff --git a/src/bin/psql/mainloop.c b/src/bin/psql/mainloop.c
index c24e38d988..739f52eea7 100644
--- a/src/bin/psql/mainloop.c
+++ b/src/bin/psql/mainloop.c
@@ -10,6 +10,7 @@
#include "command.h"
#include "common.h"
#include "common/logging.h"
+#include "gedit.h"
#include "input.h"
#include "mainloop.h"
#include "mb/pg_wchar.h"
@@ -525,8 +526,29 @@ MainLoop(FILE *source)
/* flush any paren nesting info after forced send */
psql_scan_reset(scan_state);
+
+ /* edit query result when gedit was requested */
+ if (pset.gedit_flag)
+ {
+ char **p;
+
+ if (success)
+ slashCmdStatus = gedit_edit_and_build_query(pset.gfname, query_buf);
+
+ /* free all gedit data */
+ pset.gedit_flag = false;
+ free(pset.gfname);
+ pset.gfname = NULL;
+ free(pset.gedit_table);
+ pset.gedit_table = NULL;
+ for (p = pset.gedit_key_columns; *p; p++)
+ free(*p);
+ free(pset.gedit_key_columns);
+ pset.gedit_key_columns = NULL;
+ }
}
- else if (slashCmdStatus == PSQL_CMD_NEWEDIT)
+ /* fall-through to NEWEDIT when \gedit */
+ if (slashCmdStatus == PSQL_CMD_NEWEDIT)
{
/* should not see this in inactive branch */
Assert(conditional_active(cond_stack));
diff --git a/src/bin/psql/meson.build b/src/bin/psql/meson.build
index f3a6392138..839bc48390 100644
--- a/src/bin/psql/meson.build
+++ b/src/bin/psql/meson.build
@@ -6,6 +6,7 @@ psql_sources = files(
'copy.c',
'crosstabview.c',
'describe.c',
+ 'gedit.c',
'help.c',
'input.c',
'large_obj.c',
@@ -67,6 +68,7 @@ tests += {
't/001_basic.pl',
't/010_tab_completion.pl',
't/020_cancel.pl',
+ 't/030_gedit.pl',
],
},
}
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 505f99d8e4..b426b2f69a 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -95,6 +95,9 @@ typedef struct _psqlSettings
char *gset_prefix; /* one-shot prefix argument for \gset */
bool gdesc_flag; /* one-shot request to describe query result */
+ bool gedit_flag; /* one-shot request to edit query result */
+ char *gedit_table; /* table used with \gedit */
+ char **gedit_key_columns; /* key columns used with \gedit */
bool gexec_flag; /* one-shot request to execute query result */
bool bind_flag; /* one-shot request to use extended query
* protocol */
diff --git a/src/bin/psql/t/030_gedit.pl b/src/bin/psql/t/030_gedit.pl
new file mode 100644
index 0000000000..5819109322
--- /dev/null
+++ b/src/bin/psql/t/030_gedit.pl
@@ -0,0 +1,106 @@
+
+# Copyright (c) 2023, PostgreSQL Global Development Group
+
+use strict;
+use warnings;
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Execute a psql command and check its exit code and output on stdout and stderr.
+sub psql_like
+{
+ local $Test::Builder::Level = $Test::Builder::Level + 1;
+
+ my ($node, $sql, $expected_exitcode, $expected_stdout, $expected_stderr, $test_name) = @_;
+
+ my ($ret, $stdout, $stderr) = $node->psql('postgres', $sql);
+
+ is($ret, $expected_exitcode, "$test_name: exit code $expected_exitcode");
+ like($stdout, $expected_stdout, "$test_name: stdout matches");
+ like($stderr, $expected_stderr, "$test_name: stderr matches");
+
+ return;
+}
+
+my $node = PostgreSQL::Test::Cluster->new('main');
+$node->init;
+$node->start;
+
+# prepare test table (with a non-standard primary key layout)
+psql_like($node, "create table foo (t text, a int, b int, primary key (b, a))",
+ 0, qr/^$/, qr/^$/, 'create table');
+psql_like($node, "insert into foo values ('foo', 1, 2), ('bar', 3, 4);",
+ 0, qr/^$/, qr/^$/, 'insert data');
+
+# normal cases
+$ENV{EDITOR} = 'true';
+psql_like($node, 'select * from foo \gedit',
+ 0, qr/^$/, qr/\\gedit: no changes/, 'gedit without saving');
+
+$ENV{EDITOR} = 'touch';
+psql_like($node, 'select * from foo \gedit',
+ 0, qr/^$/, qr/\\gedit: no changes/, 'gedit without change');
+
+$ENV{EDITOR} = 'sed -i -e s/bar/moo/';
+psql_like($node, 'select * from foo \gedit',
+ 0,
+ qr/UPDATE foo SET t = 'moo' WHERE b = '4' AND a = '3';/,
+ qr/^$/, 'gedit with UPDATE');
+
+$ENV{EDITOR} = "sed -i -e '1a{\"a\":6,\"b\":7},'";
+psql_like($node, "select * from foo;\n\\gedit",
+ 0,
+ qr/foo\|1\|2\nmoo\|3\|4\nINSERT INTO foo \(a, b\) VALUES \('6', '7'\);/,
+ qr/^$/, 'gedit as separate command, with INSERT');
+
+$ENV{EDITOR} = 'sed -i -e s/1/5/';
+psql_like($node, 'select * from foo \gedit',
+ 0,
+ qr/DELETE FROM foo WHERE b = '2' AND a = '1';.*INSERT INTO foo \(t, a, b\) VALUES \('foo', '5', '2'\);/s,
+ qr/^$/, 'gedit with INSERT and DELETE');
+
+$ENV{EDITOR} = 'sed -i -e /moo/d';
+psql_like($node, 'select * from foo \gedit',
+ 0,
+ qr/DELETE FROM foo WHERE b = '4' AND a = '3';/,
+ qr/^$/, 'gedit with DELETE');
+
+$ENV{EDITOR} = 'sed -i -e s/foo/baz/';
+psql_like($node, 'select * from foo \gedit (key=b)',
+ 0,
+ qr/UPDATE foo SET t = 'baz' WHERE b = '2';/,
+ qr/^$/, 'gedit with custom key');
+
+# error cases
+$ENV{EDITOR} = 'touch';
+psql_like($node, "select blub \\gedit",
+ 3, qr/^$/, qr/\\gedit: could not determine table name from query/, 'no FROM in query');
+
+psql_like($node, "blub from blub \\gedit",
+ 3, qr/^$/, qr/ERROR: syntax error at or near "blub"/, 'syntax error reported normally');
+
+psql_like($node, "select * from blub \\gedit",
+ 3, qr/^$/, qr/ERROR: relation "blub" does not exist/, 'gedit on missing table');
+
+psql_like($node, "select * from blub \\gedit (bla)",
+ 3, qr/^$/, qr/\\gedit: unknown option "bla"/, 'unknown gedit option');
+
+psql_like($node, "select * from blub \\gedit (blub=moo)",
+ 3, qr/^$/, qr/\\gedit: unknown option "blub"/, 'unknown gedit option with value');
+
+psql_like($node, 'select * from foo \gedit (table=bar)',
+ 3, qr/^$/, qr/\\gedit: table "bar" does not exist/, 'gedit with custom table name');
+
+psql_like($node, 'select a from foo \gedit',
+ 3, qr/^$/, qr/\\gedit: no key of table "foo" is contained in the returned query columns/,
+ 'key missing in query');
+
+psql_like($node, 'select a from foo \gedit (key=a,b,c)',
+ 3, qr/^$/, qr/\\gedit: key column "b" not found in query/,
+ 'custom key missing in query');
+
+$node->stop;
+
+done_testing();
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index f4ca4d0013..d3bf835155 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1725,7 +1725,7 @@ psql_completion(const char *text, int start, int end)
"\\echo", "\\edit", "\\ef", "\\elif", "\\else", "\\encoding",
"\\endif", "\\errverbose", "\\ev",
"\\f",
- "\\g", "\\gdesc", "\\getenv", "\\gexec", "\\gset", "\\gx",
+ "\\g", "\\gdesc", "\\getenv", "\\gedit", "\\gexec", "\\gset", "\\gx",
"\\help", "\\html",
"\\if", "\\include", "\\include_relative", "\\ir",
"\\list", "\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
--
2.43.0
