[PATCH] Add additional extended protocol commands to psql: \parse and \bindx

From 7c616373487734f60cd0012f78a9e889852675b4 Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <anthonin.bonnefoy@gmail.com>
Date: Wed, 1 Nov 2023 10:45:12 +0100
Subject: [PATCH v2] psql: Add support for prepared stmt with extended protocol

Currently, only unnamed prepared statement is supported by psql with the
\bind command so it's not possible to test named statement creation and
execution through extended protocol.

This commit introduces three additional commands: \parse, \bindx and
\close.
\parse creates a prepared statement using extended protocol.
\bindx binds and execute an existing prepared statement using extended
protocol.
\close closes an existing prepared statement using extended protocol.
---
 doc/src/sgml/ref/psql-ref.sgml     |  86 ++++++++++++++++++++++
 src/bin/psql/command.c             | 111 +++++++++++++++++++++++++++++
 src/bin/psql/common.c              |  28 +++++++-
 src/bin/psql/help.c                |   4 ++
 src/bin/psql/settings.h            |   6 ++
 src/bin/psql/tab-complete.c        |   6 +-
 src/test/regress/expected/psql.out |  46 ++++++++++++
 src/test/regress/sql/psql.sql      |  24 +++++++
 8 files changed, 306 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index cc7d797159..b4551b516e 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -916,6 +916,35 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
        </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-meta-command-bindx">
+       <term><literal>\bindx</literal> <replaceable class="parameter">prepared_statement_name</replaceable> [ <replaceable class="parameter">parameter</replaceable> ] ... </term>
+
+       <listitem>
+        <para>
+        <literal>\bindx</literal> is equivalent to <literal>\bind</literal>,
+         except that it takes the name of an explicit existing prepared
+         statement as first parameter.
+        </para>
+
+        <para>
+         Example:
+<programlisting>
+PREPARE stmt1 AS INSERT INTO tbl1 VALUES ($1, $2);
+\bindx stmt1 'first value' 'second value' \g
+</programlisting>
+        </para>
+
+        <para>
+         This command causes the extended query protocol (see <xref
+         linkend="protocol-query-concepts"/>) to be used, unlike normal
+         <application>psql</application> operation, which uses the simple
+         query protocol. So this command can be useful to test the extended
+         query protocol from psql.
+        </para>
+
+       </listitem>
+      </varlistentry>
+
       <varlistentry id="app-psql-meta-command-c-lc">
         <term><literal>\c</literal> or <literal>\connect [ -reuse-previous=<replaceable class="parameter">on|off</replaceable> ] [ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] [ <replaceable class="parameter">host</replaceable> ] [ <replaceable class="parameter">port</replaceable> ] | <replaceable class="parameter">conninfo</replaceable> ]</literal></term>
         <listitem>
@@ -1037,6 +1066,35 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
         </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-meta-command-close">
+       <term><literal>\close</literal> [ <replaceable class="parameter">prepared_statement_name</replaceable> ] </term>
+
+       <listitem>
+        <para>
+        Closes the specified prepared statement. Without argument, closes the unnamed statement.
+        If no prepared statement exists with this name, the operation is a no-op.
+        </para>
+
+        <para>
+         Example:
+<programlisting>
+PREPARE stmt1 AS SELECT $1;
+\close stmt1 \g
+</programlisting>
+        </para>
+
+        <para>
+         This command causes the extended query protocol (see <xref
+         linkend="protocol-query-concepts"/>) to be used, unlike normal
+         <application>psql</application> operation, which uses the simple
+         query protocol. So this command can be useful to test the extended
+         query protocol from psql.
+        </para>
+
+       </listitem>
+      </varlistentry>
+
+
       <varlistentry id="app-psql-meta-commands-copy">
         <term><literal>\copy { <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column_list</replaceable> ) ] }
         <literal>from</literal>
@@ -2779,6 +2837,34 @@ lo_import 152801
         </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-meta-command-parse">
+        <term><literal>\parse <replaceable class="parameter">prepared_statement_name</replaceable></literal></term>
+        <listitem>
+        <para>
+        Creates a prepared statement for the next query execution.
+        </para>
+
+        <para>
+         Example:
+<programlisting>
+SELECT $1 \parse stmt1 \g
+</programlisting>
+        </para>
+
+        <para>
+         This command causes the extended query protocol (see <xref
+         linkend="protocol-query-concepts"/>) to be used, unlike normal
+         <application>psql</application> operation, which uses the simple
+         query protocol. A <xref linkend="protocol-message-formats-Parse"/>
+         message will be issued by this command so it can be useful to
+         test the extended query protocol from psql. This command affects
+         only the next query executed; all subsequent queries will use the
+         simple query protocol by default.
+        </para>
+
+        </listitem>
+      </varlistentry>
+
       <varlistentry id="app-psql-meta-command-password">
         <term><literal>\password [ <replaceable class="parameter">username</replaceable> ]</literal></term>
         <listitem>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 9ad911e28d..8044fc1dee 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -64,10 +64,14 @@ static backslashResult exec_command(const char *cmd,
 									PQExpBuffer previous_buf);
 static backslashResult exec_command_a(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_bind(PsqlScanState scan_state, bool active_branch);
+static backslashResult exec_command_bindx(PsqlScanState scan_state, bool active_branch,
+										  const char *cmd);
 static backslashResult exec_command_C(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_connect(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_cd(PsqlScanState scan_state, bool active_branch,
 									   const char *cmd);
+static backslashResult exec_command_close(PsqlScanState scan_state, bool active_branch,
+									   const char *cmd);
 static backslashResult exec_command_conninfo(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_copy(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_copyright(PsqlScanState scan_state, bool active_branch);
@@ -116,6 +120,8 @@ static backslashResult exec_command_lo(PsqlScanState scan_state, bool active_bra
 static backslashResult exec_command_out(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_print(PsqlScanState scan_state, bool active_branch,
 										  PQExpBuffer query_buf, PQExpBuffer previous_buf);
+static backslashResult exec_command_parse(PsqlScanState scan_state, bool active_branch,
+										  const char* cmd);
 static backslashResult exec_command_password(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_prompt(PsqlScanState scan_state, bool active_branch,
 										   const char *cmd);
@@ -311,12 +317,16 @@ exec_command(const char *cmd,
 		status = exec_command_a(scan_state, active_branch);
 	else if (strcmp(cmd, "bind") == 0)
 		status = exec_command_bind(scan_state, active_branch);
+	else if (strcmp(cmd, "bindx") == 0)
+		status = exec_command_bindx(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "C") == 0)
 		status = exec_command_C(scan_state, active_branch);
 	else if (strcmp(cmd, "c") == 0 || strcmp(cmd, "connect") == 0)
 		status = exec_command_connect(scan_state, active_branch);
 	else if (strcmp(cmd, "cd") == 0)
 		status = exec_command_cd(scan_state, active_branch, cmd);
+	else if (strcmp(cmd, "close") == 0)
+		status = exec_command_close(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "conninfo") == 0)
 		status = exec_command_conninfo(scan_state, active_branch);
 	else if (pg_strcasecmp(cmd, "copy") == 0)
@@ -378,6 +388,8 @@ exec_command(const char *cmd,
 	else if (strcmp(cmd, "p") == 0 || strcmp(cmd, "print") == 0)
 		status = exec_command_print(scan_state, active_branch,
 									query_buf, previous_buf);
+	else if (strcmp(cmd, "parse") == 0)
+		status = exec_command_parse(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "password") == 0)
 		status = exec_command_password(scan_state, active_branch);
 	else if (strcmp(cmd, "prompt") == 0)
@@ -471,6 +483,7 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
 		int			nalloc = 0;
 
 		pset.bind_params = NULL;
+		pset.stmtName = NULL;
 
 		while ((opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false)))
 		{
@@ -490,6 +503,51 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
 	return status;
 }
 
+/*
+ * \bindx -- set query parameters for an existing prepared statement
+ */
+static backslashResult
+exec_command_bindx(PsqlScanState scan_state, bool active_branch,
+				   const char* cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		char	   *opt;
+		int			nparams = 0;
+		int			nalloc = 0;
+
+		pset.bind_params = NULL;
+		pset.stmtName = NULL;
+
+		opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false);
+		if (!opt)
+		{
+			pg_log_error("\\%s: missing required argument", cmd);
+			status = PSQL_CMD_ERROR;
+			free(opt);
+		} else {
+			pset.stmtName = opt;
+			pset.bind_flag = true;
+
+			while ((opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false)))
+			{
+				nparams++;
+				if (nparams > nalloc)
+				{
+					nalloc = nalloc ? nalloc * 2 : 1;
+					pset.bind_params = pg_realloc_array(pset.bind_params, char *, nalloc);
+				}
+				pset.bind_params[nparams - 1] = opt;
+			}
+			pset.bind_nparams = nparams;
+		}
+	}
+
+	return status;
+}
+
 /*
  * \C -- override table title (formerly change HTML caption)
  */
@@ -640,6 +698,27 @@ exec_command_cd(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	return success ? PSQL_CMD_SKIP_LINE : PSQL_CMD_ERROR;
 }
 
+/*
+ * \close -- close a previously prepared statement
+ */
+static backslashResult
+exec_command_close(PsqlScanState scan_state, bool active_branch, const char *cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		char	   *opt = psql_scan_slash_option(scan_state,
+												 OT_NORMAL, NULL, false);
+		pset.stmtName = opt;
+		pset.close_flag = true;
+	}
+	else
+		ignore_slash_options(scan_state);
+
+	return status;
+}
+
 /*
  * \conninfo -- display information about the current connection
  */
@@ -2093,6 +2172,38 @@ exec_command_print(PsqlScanState scan_state, bool active_branch,
 	return PSQL_CMD_SKIP_LINE;
 }
 
+/*
+ * \parse -- parse query
+ */
+static backslashResult
+exec_command_parse(PsqlScanState scan_state, bool active_branch,
+				   const char* cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		char	   *opt = psql_scan_slash_option(scan_state,
+												 OT_NORMAL, NULL, false);
+		pset.stmtName = NULL;
+		if (!opt)
+		{
+			pg_log_error("\\%s: missing required argument", cmd);
+			status = PSQL_CMD_ERROR;
+			free(opt);
+		}
+		else
+		{
+			pset.stmtName = opt;
+			pset.parse_flag = true;
+		}
+	}
+	else
+		ignore_slash_options(scan_state);
+
+	return status;
+}
+
 /*
  * \password -- set user password
  */
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 76e01b02a3..2e853b71c4 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1241,16 +1241,34 @@ sendquery_cleanup:
 		pset.gsavepopt = NULL;
 	}
 
-	/* clean up after \bind */
+	/* clean up after \bind or \bindx */
 	if (pset.bind_flag)
 	{
 		for (i = 0; i < pset.bind_nparams; i++)
 			free(pset.bind_params[i]);
 		free(pset.bind_params);
+		free(pset.stmtName);
 		pset.bind_params = NULL;
+		pset.stmtName = NULL;
 		pset.bind_flag = false;
 	}
 
+	/* clean up after \parse */
+	if (pset.parse_flag)
+	{
+		free(pset.stmtName);
+		pset.stmtName = NULL;
+		pset.parse_flag = false;
+	}
+
+	/* clean up after \close */
+	if (pset.close_flag)
+	{
+		free(pset.stmtName);
+		pset.stmtName = NULL;
+		pset.close_flag = false;
+	}
+
 	/* reset \gset trigger */
 	if (pset.gset_prefix)
 	{
@@ -1433,8 +1451,14 @@ ExecQueryAndProcessResults(const char *query,
 	else
 		INSTR_TIME_SET_ZERO(before);
 
-	if (pset.bind_flag)
+	if (pset.bind_flag && pset.stmtName == NULL)
 		success = PQsendQueryParams(pset.db, query, pset.bind_nparams, NULL, (const char *const *) pset.bind_params, NULL, NULL, 0);
+	else if (pset.bind_flag && pset.stmtName != NULL)
+		success = PQsendQueryPrepared(pset.db, pset.stmtName, pset.bind_nparams, (const char *const *) pset.bind_params, NULL, NULL, 0);
+	else if (pset.close_flag)
+		success = PQsendClosePrepared(pset.db, pset.stmtName);
+	else if (pset.parse_flag)
+		success = PQsendPrepare(pset.db, pset.stmtName, query, 0, NULL);
 	else
 		success = PQsendQuery(pset.db, query);
 
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 4e79a819d8..bb2e66c109 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -165,6 +165,9 @@ slashUsage(unsigned short int pager)
 
 	HELP0("General\n");
 	HELP0("  \\bind [PARAM]...       set query parameters\n");
+	HELP0("  \\bindx STMT_NAME [PARAM]...\n"
+		  "                         set query parameters for an existing prepared statement\n");
+	HELP0("  \\close STMT_NAME       close an existing prepared statement\n");
 	HELP0("  \\copyright             show PostgreSQL usage and distribution terms\n");
 	HELP0("  \\crosstabview [COLUMNS] execute query and display result in crosstab\n");
 	HELP0("  \\errverbose            show most recent error message at maximum verbosity\n");
@@ -312,6 +315,7 @@ slashUsage(unsigned short int pager)
 			  "                         connect to new database (currently no connection)\n");
 	HELP0("  \\conninfo              display information about current connection\n");
 	HELP0("  \\encoding [ENCODING]   show or set client encoding\n");
+	HELP0("  \\parse STMT_NAME       create a prepared statement\n");
 	HELP0("  \\password [USERNAME]   securely change the password for a user\n");
 	HELP0("\n");
 
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 505f99d8e4..4048c45ebf 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -100,6 +100,12 @@ typedef struct _psqlSettings
 								 * protocol */
 	int			bind_nparams;	/* number of parameters */
 	char	  **bind_params;	/* parameters for extended query protocol call */
+	bool		close_flag;		/* one-shot request to close a prepared statement using
+								 * extended query protocol */
+	bool		parse_flag;		/* one-shot request to parse query using extended query
+								 * protocol */
+	char	   *stmtName;		/* prepared statement name used for extended query
+								 * protocol commands */
 	bool		crosstab_flag;	/* one-shot request to crosstab result */
 	char	   *ctv_args[4];	/* \crosstabview arguments */
 
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 09914165e4..43595da6f9 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1712,8 +1712,8 @@ psql_completion(const char *text, int start, int end)
 	/* psql's backslash commands. */
 	static const char *const backslash_commands[] = {
 		"\\a",
-		"\\bind",
-		"\\connect", "\\conninfo", "\\C", "\\cd", "\\copy",
+		"\\bind", "\\bindx",
+		"\\connect", "\\conninfo", "\\C", "\\cd", "\\close", "\\copy",
 		"\\copyright", "\\crosstabview",
 		"\\d", "\\da", "\\dA", "\\dAc", "\\dAf", "\\dAo", "\\dAp",
 		"\\db", "\\dc", "\\dconfig", "\\dC", "\\dd", "\\ddp", "\\dD",
@@ -1730,7 +1730,7 @@ psql_completion(const char *text, int start, int end)
 		"\\if", "\\include", "\\include_relative", "\\ir",
 		"\\list", "\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
 		"\\out",
-		"\\password", "\\print", "\\prompt", "\\pset",
+		"\\parse", "\\password", "\\print", "\\prompt", "\\pset",
 		"\\qecho", "\\quit",
 		"\\reset",
 		"\\s", "\\set", "\\setenv", "\\sf", "\\sv",
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 4f3fd46420..a290d573f8 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -98,6 +98,47 @@ two | 2
    1 |   2
 (1 row)
 
+-- \parse (extended query protocol)
+SELECT 1 \parse '' \g
+SELECT 2 \parse stmt1 \g
+SELECT $1 \parse stmt2 \g
+SELECT $1, $2 \parse stmt3 \g
+-- \bindx (extended query protocol)
+\bindx '' \g
+ ?column? 
+----------
+        1
+(1 row)
+
+\bindx stmt1 \g
+ ?column? 
+----------
+        2
+(1 row)
+
+\bindx stmt2 'foo' \g
+ ?column? 
+----------
+ foo
+(1 row)
+
+\bindx stmt3 'foo' 'bar' \g
+ ?column? | ?column? 
+----------+----------
+ foo      | bar
+(1 row)
+
+-- \close (extended query protocol)
+\close \g
+\close stmt2 \g
+\close stmt2 \g
+select name, statement from pg_prepared_statements order by name;
+ name  |   statement    
+-------+----------------
+ stmt1 | SELECT 2 
+ stmt3 | SELECT $1, $2 
+(2 rows)
+
 -- \bind (extended query protocol)
 SELECT 1 \bind \g
  ?column? 
@@ -129,6 +170,11 @@ ERROR:  cannot insert multiple commands into a prepared statement
 -- bind error
 SELECT $1, $2 \bind 'foo' \g
 ERROR:  bind message supplies 1 parameters, but prepared statement "" requires 2
+-- bindx error
+\bindx stmt2 'baz' \g
+ERROR:  prepared statement "stmt2" does not exist
+\bindx stmt3 'baz' \g
+ERROR:  bind message supplies 1 parameters, but prepared statement "stmt3" requires 2
 -- \gset
 select 10 as test01, 20 as test02, 'Hello' as test03 \gset pref01_
 \echo :pref01_test01 :pref01_test02 :pref01_test03
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index c997106b9f..399810ca3e 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -45,6 +45,27 @@ SELECT 1 as one, 2 as two \g (format=csv csv_fieldsep='\t')
 SELECT 1 as one, 2 as two \gx (title='foo bar')
 \g
 
+-- \parse (extended query protocol)
+
+SELECT 1 \parse '' \g
+SELECT 2 \parse stmt1 \g
+SELECT $1 \parse stmt2 \g
+SELECT $1, $2 \parse stmt3 \g
+
+-- \bindx (extended query protocol)
+
+\bindx '' \g
+\bindx stmt1 \g
+\bindx stmt2 'foo' \g
+\bindx stmt3 'foo' 'bar' \g
+
+-- \close (extended query protocol)
+
+\close \g
+\close stmt2 \g
+\close stmt2 \g
+select name, statement from pg_prepared_statements order by name;
+
 -- \bind (extended query protocol)
 
 SELECT 1 \bind \g
@@ -58,6 +79,9 @@ SELECT foo \bind \g
 SELECT 1 \; SELECT 2 \bind \g
 -- bind error
 SELECT $1, $2 \bind 'foo' \g
+-- bindx error
+\bindx stmt2 'baz' \g
+\bindx stmt3 'baz' \g
 
 -- \gset
 
-- 
2.39.3 (Apple Git-145)

From ab7ba5df2ee9d0db38c9215da8014c98ef919a9f Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <anthonin.bonnefoy@gmail.com>
Date: Wed, 1 Nov 2023 10:45:12 +0100
Subject: [PATCH v3] psql: Add support for prepared stmt with extended protocol

Currently, only unnamed prepared statement is supported by psql with the
\bind command so it's not possible to test named statement creation and
execution through extended protocol.

This commit introduces three additional commands: \parse, \bindx and
\close.
\parse creates a prepared statement using extended protocol.
\bindx binds and execute an existing prepared statement using extended
protocol.
\close closes an existing prepared statement using extended protocol.
---
 doc/src/sgml/ref/psql-ref.sgml     |  86 +++++++++++++++++++++
 src/bin/psql/command.c             | 117 +++++++++++++++++++++++++++++
 src/bin/psql/common.c              |  28 ++++++-
 src/bin/psql/help.c                |   4 +
 src/bin/psql/settings.h            |   6 ++
 src/bin/psql/tab-complete.c        |   6 +-
 src/test/regress/expected/psql.out |  50 ++++++++++++
 src/test/regress/sql/psql.sql      |  28 +++++++
 8 files changed, 320 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index cc7d797159..b0f383ab2a 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -916,6 +916,35 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
        </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-meta-command-bindx">
+       <term><literal>\bindx</literal> <replaceable class="parameter">prepared_statement_name</replaceable> [ <replaceable class="parameter">parameter</replaceable> ] ... </term>
+
+       <listitem>
+        <para>
+        <literal>\bindx</literal> is equivalent to <literal>\bind</literal>,
+         except that it takes the name of an explicit existing prepared
+         statement as first parameter.
+        </para>
+
+        <para>
+         Example:
+<programlisting>
+INSERT INTO tbls1 VALUES ($1, $2) \parse stmt1
+\bindx stmt1 'first value' 'second value' \g
+</programlisting>
+        </para>
+
+        <para>
+         This command causes the extended query protocol (see <xref
+         linkend="protocol-query-concepts"/>) to be used, unlike normal
+         <application>psql</application> operation, which uses the simple
+         query protocol. So this command can be useful to test the extended
+         query protocol from psql.
+        </para>
+
+       </listitem>
+      </varlistentry>
+
       <varlistentry id="app-psql-meta-command-c-lc">
         <term><literal>\c</literal> or <literal>\connect [ -reuse-previous=<replaceable class="parameter">on|off</replaceable> ] [ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] [ <replaceable class="parameter">host</replaceable> ] [ <replaceable class="parameter">port</replaceable> ] | <replaceable class="parameter">conninfo</replaceable> ]</literal></term>
         <listitem>
@@ -1037,6 +1066,35 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
         </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-meta-command-close">
+       <term><literal>\close</literal> [ <replaceable class="parameter">prepared_statement_name</replaceable> ] </term>
+
+       <listitem>
+        <para>
+        Closes the specified prepared statement. Without argument, closes the unnamed statement.
+        If no prepared statement exists with this name, the operation is a no-op.
+        </para>
+
+        <para>
+         Example:
+<programlisting>
+SELECT $1 \parse stmt1
+\close stmt1
+</programlisting>
+        </para>
+
+        <para>
+         This command causes the extended query protocol (see <xref
+         linkend="protocol-query-concepts"/>) to be used, unlike normal
+         <application>psql</application> operation, which uses the simple
+         query protocol. So this command can be useful to test the extended
+         query protocol from psql.
+        </para>
+
+       </listitem>
+      </varlistentry>
+
+
       <varlistentry id="app-psql-meta-commands-copy">
         <term><literal>\copy { <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column_list</replaceable> ) ] }
         <literal>from</literal>
@@ -2779,6 +2837,34 @@ lo_import 152801
         </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-meta-command-parse">
+        <term><literal>\parse <replaceable class="parameter">prepared_statement_name</replaceable></literal></term>
+        <listitem>
+        <para>
+        Creates a prepared statement from the current query buffer
+        </para>
+
+        <para>
+         Example:
+<programlisting>
+SELECT $1 \parse stmt1
+</programlisting>
+        </para>
+
+        <para>
+         This command causes the extended query protocol (see <xref
+         linkend="protocol-query-concepts"/>) to be used, unlike normal
+         <application>psql</application> operation, which uses the simple
+         query protocol. A <xref linkend="protocol-message-formats-Parse"/>
+         message will be issued by this command so it can be useful to
+         test the extended query protocol from psql. This command affects
+         only the next query executed; all subsequent queries will use the
+         simple query protocol by default.
+        </para>
+
+        </listitem>
+      </varlistentry>
+
       <varlistentry id="app-psql-meta-command-password">
         <term><literal>\password [ <replaceable class="parameter">username</replaceable> ]</literal></term>
         <listitem>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 9ad911e28d..07d58ec66f 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -64,10 +64,14 @@ static backslashResult exec_command(const char *cmd,
 									PQExpBuffer previous_buf);
 static backslashResult exec_command_a(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_bind(PsqlScanState scan_state, bool active_branch);
+static backslashResult exec_command_bindx(PsqlScanState scan_state, bool active_branch,
+										  const char *cmd);
 static backslashResult exec_command_C(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_connect(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_cd(PsqlScanState scan_state, bool active_branch,
 									   const char *cmd);
+static backslashResult exec_command_close(PsqlScanState scan_state, bool active_branch,
+									   const char *cmd);
 static backslashResult exec_command_conninfo(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_copy(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_copyright(PsqlScanState scan_state, bool active_branch);
@@ -116,6 +120,8 @@ static backslashResult exec_command_lo(PsqlScanState scan_state, bool active_bra
 static backslashResult exec_command_out(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_print(PsqlScanState scan_state, bool active_branch,
 										  PQExpBuffer query_buf, PQExpBuffer previous_buf);
+static backslashResult exec_command_parse(PsqlScanState scan_state, bool active_branch,
+										  const char* cmd);
 static backslashResult exec_command_password(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_prompt(PsqlScanState scan_state, bool active_branch,
 										   const char *cmd);
@@ -311,12 +317,16 @@ exec_command(const char *cmd,
 		status = exec_command_a(scan_state, active_branch);
 	else if (strcmp(cmd, "bind") == 0)
 		status = exec_command_bind(scan_state, active_branch);
+	else if (strcmp(cmd, "bindx") == 0)
+		status = exec_command_bindx(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "C") == 0)
 		status = exec_command_C(scan_state, active_branch);
 	else if (strcmp(cmd, "c") == 0 || strcmp(cmd, "connect") == 0)
 		status = exec_command_connect(scan_state, active_branch);
 	else if (strcmp(cmd, "cd") == 0)
 		status = exec_command_cd(scan_state, active_branch, cmd);
+	else if (strcmp(cmd, "close") == 0)
+		status = exec_command_close(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "conninfo") == 0)
 		status = exec_command_conninfo(scan_state, active_branch);
 	else if (pg_strcasecmp(cmd, "copy") == 0)
@@ -378,6 +388,8 @@ exec_command(const char *cmd,
 	else if (strcmp(cmd, "p") == 0 || strcmp(cmd, "print") == 0)
 		status = exec_command_print(scan_state, active_branch,
 									query_buf, previous_buf);
+	else if (strcmp(cmd, "parse") == 0)
+		status = exec_command_parse(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "password") == 0)
 		status = exec_command_password(scan_state, active_branch);
 	else if (strcmp(cmd, "prompt") == 0)
@@ -471,6 +483,7 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
 		int			nalloc = 0;
 
 		pset.bind_params = NULL;
+		pset.stmtName = NULL;
 
 		while ((opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false)))
 		{
@@ -486,6 +499,55 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
 		pset.bind_nparams = nparams;
 		pset.bind_flag = true;
 	}
+	else
+		ignore_slash_options(scan_state);
+
+	return status;
+}
+
+/*
+ * \bindx -- set query parameters for an existing prepared statement
+ */
+static backslashResult
+exec_command_bindx(PsqlScanState scan_state, bool active_branch,
+				   const char* cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		char	   *opt;
+		int			nparams = 0;
+		int			nalloc = 0;
+
+		pset.bind_params = NULL;
+		pset.stmtName = NULL;
+
+		opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false);
+		if (!opt)
+		{
+			pg_log_error("\\%s: missing required argument", cmd);
+			status = PSQL_CMD_ERROR;
+			free(opt);
+		} else {
+			pset.stmtName = opt;
+			pset.bind_flag = true;
+
+			while ((opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false)))
+			{
+				nparams++;
+				if (nparams > nalloc)
+				{
+					nalloc = nalloc ? nalloc * 2 : 1;
+					pset.bind_params = pg_realloc_array(pset.bind_params, char *, nalloc);
+				}
+				pset.bind_params[nparams - 1] = opt;
+			}
+			pset.bind_nparams = nparams;
+		}
+	}
+	else
+		ignore_slash_options(scan_state);
 
 	return status;
 }
@@ -640,6 +702,28 @@ exec_command_cd(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	return success ? PSQL_CMD_SKIP_LINE : PSQL_CMD_ERROR;
 }
 
+/*
+ * \close -- close a previously prepared statement
+ */
+static backslashResult
+exec_command_close(PsqlScanState scan_state, bool active_branch, const char *cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		char	   *opt = psql_scan_slash_option(scan_state,
+												 OT_NORMAL, NULL, false);
+		pset.stmtName = opt;
+		pset.close_flag = true;
+		status = PSQL_CMD_SEND;
+	}
+	else
+		ignore_slash_options(scan_state);
+
+	return status;
+}
+
 /*
  * \conninfo -- display information about the current connection
  */
@@ -2093,6 +2177,39 @@ exec_command_print(PsqlScanState scan_state, bool active_branch,
 	return PSQL_CMD_SKIP_LINE;
 }
 
+/*
+ * \parse -- parse query
+ */
+static backslashResult
+exec_command_parse(PsqlScanState scan_state, bool active_branch,
+				   const char* cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		char	   *opt = psql_scan_slash_option(scan_state,
+												 OT_NORMAL, NULL, false);
+		pset.stmtName = NULL;
+		if (!opt)
+		{
+			pg_log_error("\\%s: missing required argument", cmd);
+			status = PSQL_CMD_ERROR;
+			free(opt);
+		}
+		else
+		{
+			pset.stmtName = opt;
+			pset.parse_flag = true;
+			status = PSQL_CMD_SEND;
+		}
+	}
+	else
+		ignore_slash_options(scan_state);
+
+	return status;
+}
+
 /*
  * \password -- set user password
  */
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 76e01b02a3..2e853b71c4 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1241,16 +1241,34 @@ sendquery_cleanup:
 		pset.gsavepopt = NULL;
 	}
 
-	/* clean up after \bind */
+	/* clean up after \bind or \bindx */
 	if (pset.bind_flag)
 	{
 		for (i = 0; i < pset.bind_nparams; i++)
 			free(pset.bind_params[i]);
 		free(pset.bind_params);
+		free(pset.stmtName);
 		pset.bind_params = NULL;
+		pset.stmtName = NULL;
 		pset.bind_flag = false;
 	}
 
+	/* clean up after \parse */
+	if (pset.parse_flag)
+	{
+		free(pset.stmtName);
+		pset.stmtName = NULL;
+		pset.parse_flag = false;
+	}
+
+	/* clean up after \close */
+	if (pset.close_flag)
+	{
+		free(pset.stmtName);
+		pset.stmtName = NULL;
+		pset.close_flag = false;
+	}
+
 	/* reset \gset trigger */
 	if (pset.gset_prefix)
 	{
@@ -1433,8 +1451,14 @@ ExecQueryAndProcessResults(const char *query,
 	else
 		INSTR_TIME_SET_ZERO(before);
 
-	if (pset.bind_flag)
+	if (pset.bind_flag && pset.stmtName == NULL)
 		success = PQsendQueryParams(pset.db, query, pset.bind_nparams, NULL, (const char *const *) pset.bind_params, NULL, NULL, 0);
+	else if (pset.bind_flag && pset.stmtName != NULL)
+		success = PQsendQueryPrepared(pset.db, pset.stmtName, pset.bind_nparams, (const char *const *) pset.bind_params, NULL, NULL, 0);
+	else if (pset.close_flag)
+		success = PQsendClosePrepared(pset.db, pset.stmtName);
+	else if (pset.parse_flag)
+		success = PQsendPrepare(pset.db, pset.stmtName, query, 0, NULL);
 	else
 		success = PQsendQuery(pset.db, query);
 
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 4e79a819d8..bb2e66c109 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -165,6 +165,9 @@ slashUsage(unsigned short int pager)
 
 	HELP0("General\n");
 	HELP0("  \\bind [PARAM]...       set query parameters\n");
+	HELP0("  \\bindx STMT_NAME [PARAM]...\n"
+		  "                         set query parameters for an existing prepared statement\n");
+	HELP0("  \\close STMT_NAME       close an existing prepared statement\n");
 	HELP0("  \\copyright             show PostgreSQL usage and distribution terms\n");
 	HELP0("  \\crosstabview [COLUMNS] execute query and display result in crosstab\n");
 	HELP0("  \\errverbose            show most recent error message at maximum verbosity\n");
@@ -312,6 +315,7 @@ slashUsage(unsigned short int pager)
 			  "                         connect to new database (currently no connection)\n");
 	HELP0("  \\conninfo              display information about current connection\n");
 	HELP0("  \\encoding [ENCODING]   show or set client encoding\n");
+	HELP0("  \\parse STMT_NAME       create a prepared statement\n");
 	HELP0("  \\password [USERNAME]   securely change the password for a user\n");
 	HELP0("\n");
 
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 505f99d8e4..4048c45ebf 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -100,6 +100,12 @@ typedef struct _psqlSettings
 								 * protocol */
 	int			bind_nparams;	/* number of parameters */
 	char	  **bind_params;	/* parameters for extended query protocol call */
+	bool		close_flag;		/* one-shot request to close a prepared statement using
+								 * extended query protocol */
+	bool		parse_flag;		/* one-shot request to parse query using extended query
+								 * protocol */
+	char	   *stmtName;		/* prepared statement name used for extended query
+								 * protocol commands */
 	bool		crosstab_flag;	/* one-shot request to crosstab result */
 	char	   *ctv_args[4];	/* \crosstabview arguments */
 
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 6bfdb5f008..6cea2d2d86 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1712,8 +1712,8 @@ psql_completion(const char *text, int start, int end)
 	/* psql's backslash commands. */
 	static const char *const backslash_commands[] = {
 		"\\a",
-		"\\bind",
-		"\\connect", "\\conninfo", "\\C", "\\cd", "\\copy",
+		"\\bind", "\\bindx",
+		"\\connect", "\\conninfo", "\\C", "\\cd", "\\close", "\\copy",
 		"\\copyright", "\\crosstabview",
 		"\\d", "\\da", "\\dA", "\\dAc", "\\dAf", "\\dAo", "\\dAp",
 		"\\db", "\\dc", "\\dconfig", "\\dC", "\\dd", "\\ddp", "\\dD",
@@ -1730,7 +1730,7 @@ psql_completion(const char *text, int start, int end)
 		"\\if", "\\include", "\\include_relative", "\\ir",
 		"\\list", "\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
 		"\\out",
-		"\\password", "\\print", "\\prompt", "\\pset",
+		"\\parse", "\\password", "\\print", "\\prompt", "\\pset",
 		"\\qecho", "\\quit",
 		"\\reset",
 		"\\s", "\\set", "\\setenv", "\\sf", "\\sv",
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 4f3fd46420..4a2dbdd38f 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -98,6 +98,47 @@ two | 2
    1 |   2
 (1 row)
 
+-- \parse (extended query protocol)
+SELECT 1 \parse ''
+SELECT 2 \parse stmt1
+SELECT $1 \parse stmt2
+SELECT $1, $2 \parse stmt3
+-- \bindx (extended query protocol)
+\bindx '' \g
+ ?column? 
+----------
+        1
+(1 row)
+
+\bindx stmt1 \g
+ ?column? 
+----------
+        2
+(1 row)
+
+\bindx stmt2 'foo' \g
+ ?column? 
+----------
+ foo
+(1 row)
+
+\bindx stmt3 'foo' 'bar' \g
+ ?column? | ?column? 
+----------+----------
+ foo      | bar
+(1 row)
+
+-- \close (extended query protocol)
+\close
+\close stmt2
+\close stmt2
+select name, statement from pg_prepared_statements order by name;
+ name  |   statement    
+-------+----------------
+ stmt1 | SELECT 2 
+ stmt3 | SELECT $1, $2 
+(2 rows)
+
 -- \bind (extended query protocol)
 SELECT 1 \bind \g
  ?column? 
@@ -129,6 +170,11 @@ ERROR:  cannot insert multiple commands into a prepared statement
 -- bind error
 SELECT $1, $2 \bind 'foo' \g
 ERROR:  bind message supplies 1 parameters, but prepared statement "" requires 2
+-- bindx error
+\bindx stmt2 'baz' \g
+ERROR:  prepared statement "stmt2" does not exist
+\bindx stmt3 'baz' \g
+ERROR:  bind message supplies 1 parameters, but prepared statement "stmt3" requires 2
 -- \gset
 select 10 as test01, 20 as test02, 'Hello' as test03 \gset pref01_
 \echo :pref01_test01 :pref01_test02 :pref01_test03
@@ -4506,9 +4552,12 @@ bar 'bar' "bar"
 	\echo `nosuchcommand` :foo :'foo' :"foo"
 	\pset fieldsep | `nosuchcommand` :foo :'foo' :"foo"
 	\a
+	SELECT $1 \bind 1 \g
+	\bindx stmt1 1 2 \g
 	\C arg1
 	\c arg1 arg2 arg3 arg4
 	\cd arg1
+	\close stmt1
 	\conninfo
 	\copy arg1 arg2 arg3 arg4 arg5 arg6
 	\copyright
@@ -4537,6 +4586,7 @@ invalid command \lo
 	\lo_list
 	\o arg1
 	\p
+	SELECT 1 \parse
 	\password arg1
 	\prompt arg1 arg2
 	\pset arg1 arg2
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index c997106b9f..2bec298a17 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -45,6 +45,27 @@ SELECT 1 as one, 2 as two \g (format=csv csv_fieldsep='\t')
 SELECT 1 as one, 2 as two \gx (title='foo bar')
 \g
 
+-- \parse (extended query protocol)
+
+SELECT 1 \parse ''
+SELECT 2 \parse stmt1
+SELECT $1 \parse stmt2
+SELECT $1, $2 \parse stmt3
+
+-- \bindx (extended query protocol)
+
+\bindx '' \g
+\bindx stmt1 \g
+\bindx stmt2 'foo' \g
+\bindx stmt3 'foo' 'bar' \g
+
+-- \close (extended query protocol)
+
+\close
+\close stmt2
+\close stmt2
+select name, statement from pg_prepared_statements order by name;
+
 -- \bind (extended query protocol)
 
 SELECT 1 \bind \g
@@ -58,6 +79,9 @@ SELECT foo \bind \g
 SELECT 1 \; SELECT 2 \bind \g
 -- bind error
 SELECT $1, $2 \bind 'foo' \g
+-- bindx error
+\bindx stmt2 'baz' \g
+\bindx stmt3 'baz' \g
 
 -- \gset
 
@@ -989,9 +1013,12 @@ select \if false \\ (bogus \else \\ 42 \endif \\ forty_two;
 	\echo `nosuchcommand` :foo :'foo' :"foo"
 	\pset fieldsep | `nosuchcommand` :foo :'foo' :"foo"
 	\a
+	SELECT $1 \bind 1 \g
+	\bindx stmt1 1 2 \g
 	\C arg1
 	\c arg1 arg2 arg3 arg4
 	\cd arg1
+	\close stmt1
 	\conninfo
 	\copy arg1 arg2 arg3 arg4 arg5 arg6
 	\copyright
@@ -1019,6 +1046,7 @@ select \if false \\ (bogus \else \\ 42 \endif \\ forty_two;
 	\lo_list
 	\o arg1
 	\p
+	SELECT 1 \parse
 	\password arg1
 	\prompt arg1 arg2
 	\pset arg1 arg2
-- 
2.39.3 (Apple Git-145)

From 530edef7c9ebfb719defbb73c790c637d90d53f1 Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
Date: Thu, 18 Jan 2024 08:46:33 +0100
Subject: [PATCH v4 2/2] psql: Add support for prepared stmt with extended
 protocol

Currently, only unnamed prepared statement is supported by psql with the
\bind command so it's not possible to test named statement creation and
execution through extended protocol.

This commit introduces three additional commands: \parse, \bindx and
\close.
\parse creates a prepared statement using extended protocol.
\bindx binds and execute an existing prepared statement using extended
protocol.
\close closes an existing prepared statement using extended protocol.
---
 doc/src/sgml/ref/psql-ref.sgml     |  86 +++++++++++++++++++++
 src/bin/psql/command.c             | 115 +++++++++++++++++++++++++++++
 src/bin/psql/common.c              |  28 ++++++-
 src/bin/psql/help.c                |   4 +
 src/bin/psql/settings.h            |   6 ++
 src/bin/psql/tab-complete.c        |   6 +-
 src/test/regress/expected/psql.out |  49 ++++++++++++
 src/test/regress/sql/psql.sql      |  27 +++++++
 8 files changed, 316 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index cc7d797159..b0f383ab2a 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -916,6 +916,35 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
        </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-meta-command-bindx">
+       <term><literal>\bindx</literal> <replaceable class="parameter">prepared_statement_name</replaceable> [ <replaceable class="parameter">parameter</replaceable> ] ... </term>
+
+       <listitem>
+        <para>
+        <literal>\bindx</literal> is equivalent to <literal>\bind</literal>,
+         except that it takes the name of an explicit existing prepared
+         statement as first parameter.
+        </para>
+
+        <para>
+         Example:
+<programlisting>
+INSERT INTO tbls1 VALUES ($1, $2) \parse stmt1
+\bindx stmt1 'first value' 'second value' \g
+</programlisting>
+        </para>
+
+        <para>
+         This command causes the extended query protocol (see <xref
+         linkend="protocol-query-concepts"/>) to be used, unlike normal
+         <application>psql</application> operation, which uses the simple
+         query protocol. So this command can be useful to test the extended
+         query protocol from psql.
+        </para>
+
+       </listitem>
+      </varlistentry>
+
       <varlistentry id="app-psql-meta-command-c-lc">
         <term><literal>\c</literal> or <literal>\connect [ -reuse-previous=<replaceable class="parameter">on|off</replaceable> ] [ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] [ <replaceable class="parameter">host</replaceable> ] [ <replaceable class="parameter">port</replaceable> ] | <replaceable class="parameter">conninfo</replaceable> ]</literal></term>
         <listitem>
@@ -1037,6 +1066,35 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
         </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-meta-command-close">
+       <term><literal>\close</literal> [ <replaceable class="parameter">prepared_statement_name</replaceable> ] </term>
+
+       <listitem>
+        <para>
+        Closes the specified prepared statement. Without argument, closes the unnamed statement.
+        If no prepared statement exists with this name, the operation is a no-op.
+        </para>
+
+        <para>
+         Example:
+<programlisting>
+SELECT $1 \parse stmt1
+\close stmt1
+</programlisting>
+        </para>
+
+        <para>
+         This command causes the extended query protocol (see <xref
+         linkend="protocol-query-concepts"/>) to be used, unlike normal
+         <application>psql</application> operation, which uses the simple
+         query protocol. So this command can be useful to test the extended
+         query protocol from psql.
+        </para>
+
+       </listitem>
+      </varlistentry>
+
+
       <varlistentry id="app-psql-meta-commands-copy">
         <term><literal>\copy { <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column_list</replaceable> ) ] }
         <literal>from</literal>
@@ -2779,6 +2837,34 @@ lo_import 152801
         </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-meta-command-parse">
+        <term><literal>\parse <replaceable class="parameter">prepared_statement_name</replaceable></literal></term>
+        <listitem>
+        <para>
+        Creates a prepared statement from the current query buffer
+        </para>
+
+        <para>
+         Example:
+<programlisting>
+SELECT $1 \parse stmt1
+</programlisting>
+        </para>
+
+        <para>
+         This command causes the extended query protocol (see <xref
+         linkend="protocol-query-concepts"/>) to be used, unlike normal
+         <application>psql</application> operation, which uses the simple
+         query protocol. A <xref linkend="protocol-message-formats-Parse"/>
+         message will be issued by this command so it can be useful to
+         test the extended query protocol from psql. This command affects
+         only the next query executed; all subsequent queries will use the
+         simple query protocol by default.
+        </para>
+
+        </listitem>
+      </varlistentry>
+
       <varlistentry id="app-psql-meta-command-password">
         <term><literal>\password [ <replaceable class="parameter">username</replaceable> ]</literal></term>
         <listitem>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 5c906e4806..07d58ec66f 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -64,10 +64,14 @@ static backslashResult exec_command(const char *cmd,
 									PQExpBuffer previous_buf);
 static backslashResult exec_command_a(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_bind(PsqlScanState scan_state, bool active_branch);
+static backslashResult exec_command_bindx(PsqlScanState scan_state, bool active_branch,
+										  const char *cmd);
 static backslashResult exec_command_C(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_connect(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_cd(PsqlScanState scan_state, bool active_branch,
 									   const char *cmd);
+static backslashResult exec_command_close(PsqlScanState scan_state, bool active_branch,
+									   const char *cmd);
 static backslashResult exec_command_conninfo(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_copy(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_copyright(PsqlScanState scan_state, bool active_branch);
@@ -116,6 +120,8 @@ static backslashResult exec_command_lo(PsqlScanState scan_state, bool active_bra
 static backslashResult exec_command_out(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_print(PsqlScanState scan_state, bool active_branch,
 										  PQExpBuffer query_buf, PQExpBuffer previous_buf);
+static backslashResult exec_command_parse(PsqlScanState scan_state, bool active_branch,
+										  const char* cmd);
 static backslashResult exec_command_password(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_prompt(PsqlScanState scan_state, bool active_branch,
 										   const char *cmd);
@@ -311,12 +317,16 @@ exec_command(const char *cmd,
 		status = exec_command_a(scan_state, active_branch);
 	else if (strcmp(cmd, "bind") == 0)
 		status = exec_command_bind(scan_state, active_branch);
+	else if (strcmp(cmd, "bindx") == 0)
+		status = exec_command_bindx(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "C") == 0)
 		status = exec_command_C(scan_state, active_branch);
 	else if (strcmp(cmd, "c") == 0 || strcmp(cmd, "connect") == 0)
 		status = exec_command_connect(scan_state, active_branch);
 	else if (strcmp(cmd, "cd") == 0)
 		status = exec_command_cd(scan_state, active_branch, cmd);
+	else if (strcmp(cmd, "close") == 0)
+		status = exec_command_close(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "conninfo") == 0)
 		status = exec_command_conninfo(scan_state, active_branch);
 	else if (pg_strcasecmp(cmd, "copy") == 0)
@@ -378,6 +388,8 @@ exec_command(const char *cmd,
 	else if (strcmp(cmd, "p") == 0 || strcmp(cmd, "print") == 0)
 		status = exec_command_print(scan_state, active_branch,
 									query_buf, previous_buf);
+	else if (strcmp(cmd, "parse") == 0)
+		status = exec_command_parse(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "password") == 0)
 		status = exec_command_password(scan_state, active_branch);
 	else if (strcmp(cmd, "prompt") == 0)
@@ -471,6 +483,7 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
 		int			nalloc = 0;
 
 		pset.bind_params = NULL;
+		pset.stmtName = NULL;
 
 		while ((opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false)))
 		{
@@ -492,6 +505,53 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
 	return status;
 }
 
+/*
+ * \bindx -- set query parameters for an existing prepared statement
+ */
+static backslashResult
+exec_command_bindx(PsqlScanState scan_state, bool active_branch,
+				   const char* cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		char	   *opt;
+		int			nparams = 0;
+		int			nalloc = 0;
+
+		pset.bind_params = NULL;
+		pset.stmtName = NULL;
+
+		opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false);
+		if (!opt)
+		{
+			pg_log_error("\\%s: missing required argument", cmd);
+			status = PSQL_CMD_ERROR;
+			free(opt);
+		} else {
+			pset.stmtName = opt;
+			pset.bind_flag = true;
+
+			while ((opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false)))
+			{
+				nparams++;
+				if (nparams > nalloc)
+				{
+					nalloc = nalloc ? nalloc * 2 : 1;
+					pset.bind_params = pg_realloc_array(pset.bind_params, char *, nalloc);
+				}
+				pset.bind_params[nparams - 1] = opt;
+			}
+			pset.bind_nparams = nparams;
+		}
+	}
+	else
+		ignore_slash_options(scan_state);
+
+	return status;
+}
+
 /*
  * \C -- override table title (formerly change HTML caption)
  */
@@ -642,6 +702,28 @@ exec_command_cd(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	return success ? PSQL_CMD_SKIP_LINE : PSQL_CMD_ERROR;
 }
 
+/*
+ * \close -- close a previously prepared statement
+ */
+static backslashResult
+exec_command_close(PsqlScanState scan_state, bool active_branch, const char *cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		char	   *opt = psql_scan_slash_option(scan_state,
+												 OT_NORMAL, NULL, false);
+		pset.stmtName = opt;
+		pset.close_flag = true;
+		status = PSQL_CMD_SEND;
+	}
+	else
+		ignore_slash_options(scan_state);
+
+	return status;
+}
+
 /*
  * \conninfo -- display information about the current connection
  */
@@ -2095,6 +2177,39 @@ exec_command_print(PsqlScanState scan_state, bool active_branch,
 	return PSQL_CMD_SKIP_LINE;
 }
 
+/*
+ * \parse -- parse query
+ */
+static backslashResult
+exec_command_parse(PsqlScanState scan_state, bool active_branch,
+				   const char* cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		char	   *opt = psql_scan_slash_option(scan_state,
+												 OT_NORMAL, NULL, false);
+		pset.stmtName = NULL;
+		if (!opt)
+		{
+			pg_log_error("\\%s: missing required argument", cmd);
+			status = PSQL_CMD_ERROR;
+			free(opt);
+		}
+		else
+		{
+			pset.stmtName = opt;
+			pset.parse_flag = true;
+			status = PSQL_CMD_SEND;
+		}
+	}
+	else
+		ignore_slash_options(scan_state);
+
+	return status;
+}
+
 /*
  * \password -- set user password
  */
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 76e01b02a3..2e853b71c4 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1241,16 +1241,34 @@ sendquery_cleanup:
 		pset.gsavepopt = NULL;
 	}
 
-	/* clean up after \bind */
+	/* clean up after \bind or \bindx */
 	if (pset.bind_flag)
 	{
 		for (i = 0; i < pset.bind_nparams; i++)
 			free(pset.bind_params[i]);
 		free(pset.bind_params);
+		free(pset.stmtName);
 		pset.bind_params = NULL;
+		pset.stmtName = NULL;
 		pset.bind_flag = false;
 	}
 
+	/* clean up after \parse */
+	if (pset.parse_flag)
+	{
+		free(pset.stmtName);
+		pset.stmtName = NULL;
+		pset.parse_flag = false;
+	}
+
+	/* clean up after \close */
+	if (pset.close_flag)
+	{
+		free(pset.stmtName);
+		pset.stmtName = NULL;
+		pset.close_flag = false;
+	}
+
 	/* reset \gset trigger */
 	if (pset.gset_prefix)
 	{
@@ -1433,8 +1451,14 @@ ExecQueryAndProcessResults(const char *query,
 	else
 		INSTR_TIME_SET_ZERO(before);
 
-	if (pset.bind_flag)
+	if (pset.bind_flag && pset.stmtName == NULL)
 		success = PQsendQueryParams(pset.db, query, pset.bind_nparams, NULL, (const char *const *) pset.bind_params, NULL, NULL, 0);
+	else if (pset.bind_flag && pset.stmtName != NULL)
+		success = PQsendQueryPrepared(pset.db, pset.stmtName, pset.bind_nparams, (const char *const *) pset.bind_params, NULL, NULL, 0);
+	else if (pset.close_flag)
+		success = PQsendClosePrepared(pset.db, pset.stmtName);
+	else if (pset.parse_flag)
+		success = PQsendPrepare(pset.db, pset.stmtName, query, 0, NULL);
 	else
 		success = PQsendQuery(pset.db, query);
 
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 4e79a819d8..bb2e66c109 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -165,6 +165,9 @@ slashUsage(unsigned short int pager)
 
 	HELP0("General\n");
 	HELP0("  \\bind [PARAM]...       set query parameters\n");
+	HELP0("  \\bindx STMT_NAME [PARAM]...\n"
+		  "                         set query parameters for an existing prepared statement\n");
+	HELP0("  \\close STMT_NAME       close an existing prepared statement\n");
 	HELP0("  \\copyright             show PostgreSQL usage and distribution terms\n");
 	HELP0("  \\crosstabview [COLUMNS] execute query and display result in crosstab\n");
 	HELP0("  \\errverbose            show most recent error message at maximum verbosity\n");
@@ -312,6 +315,7 @@ slashUsage(unsigned short int pager)
 			  "                         connect to new database (currently no connection)\n");
 	HELP0("  \\conninfo              display information about current connection\n");
 	HELP0("  \\encoding [ENCODING]   show or set client encoding\n");
+	HELP0("  \\parse STMT_NAME       create a prepared statement\n");
 	HELP0("  \\password [USERNAME]   securely change the password for a user\n");
 	HELP0("\n");
 
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 505f99d8e4..4048c45ebf 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -100,6 +100,12 @@ typedef struct _psqlSettings
 								 * protocol */
 	int			bind_nparams;	/* number of parameters */
 	char	  **bind_params;	/* parameters for extended query protocol call */
+	bool		close_flag;		/* one-shot request to close a prepared statement using
+								 * extended query protocol */
+	bool		parse_flag;		/* one-shot request to parse query using extended query
+								 * protocol */
+	char	   *stmtName;		/* prepared statement name used for extended query
+								 * protocol commands */
 	bool		crosstab_flag;	/* one-shot request to crosstab result */
 	char	   *ctv_args[4];	/* \crosstabview arguments */
 
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 6bfdb5f008..6cea2d2d86 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1712,8 +1712,8 @@ psql_completion(const char *text, int start, int end)
 	/* psql's backslash commands. */
 	static const char *const backslash_commands[] = {
 		"\\a",
-		"\\bind",
-		"\\connect", "\\conninfo", "\\C", "\\cd", "\\copy",
+		"\\bind", "\\bindx",
+		"\\connect", "\\conninfo", "\\C", "\\cd", "\\close", "\\copy",
 		"\\copyright", "\\crosstabview",
 		"\\d", "\\da", "\\dA", "\\dAc", "\\dAf", "\\dAo", "\\dAp",
 		"\\db", "\\dc", "\\dconfig", "\\dC", "\\dd", "\\ddp", "\\dD",
@@ -1730,7 +1730,7 @@ psql_completion(const char *text, int start, int end)
 		"\\if", "\\include", "\\include_relative", "\\ir",
 		"\\list", "\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
 		"\\out",
-		"\\password", "\\print", "\\prompt", "\\pset",
+		"\\parse", "\\password", "\\print", "\\prompt", "\\pset",
 		"\\qecho", "\\quit",
 		"\\reset",
 		"\\s", "\\set", "\\setenv", "\\sf", "\\sv",
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index ad02772562..4a2dbdd38f 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -98,6 +98,47 @@ two | 2
    1 |   2
 (1 row)
 
+-- \parse (extended query protocol)
+SELECT 1 \parse ''
+SELECT 2 \parse stmt1
+SELECT $1 \parse stmt2
+SELECT $1, $2 \parse stmt3
+-- \bindx (extended query protocol)
+\bindx '' \g
+ ?column? 
+----------
+        1
+(1 row)
+
+\bindx stmt1 \g
+ ?column? 
+----------
+        2
+(1 row)
+
+\bindx stmt2 'foo' \g
+ ?column? 
+----------
+ foo
+(1 row)
+
+\bindx stmt3 'foo' 'bar' \g
+ ?column? | ?column? 
+----------+----------
+ foo      | bar
+(1 row)
+
+-- \close (extended query protocol)
+\close
+\close stmt2
+\close stmt2
+select name, statement from pg_prepared_statements order by name;
+ name  |   statement    
+-------+----------------
+ stmt1 | SELECT 2 
+ stmt3 | SELECT $1, $2 
+(2 rows)
+
 -- \bind (extended query protocol)
 SELECT 1 \bind \g
  ?column? 
@@ -129,6 +170,11 @@ ERROR:  cannot insert multiple commands into a prepared statement
 -- bind error
 SELECT $1, $2 \bind 'foo' \g
 ERROR:  bind message supplies 1 parameters, but prepared statement "" requires 2
+-- bindx error
+\bindx stmt2 'baz' \g
+ERROR:  prepared statement "stmt2" does not exist
+\bindx stmt3 'baz' \g
+ERROR:  bind message supplies 1 parameters, but prepared statement "stmt3" requires 2
 -- \gset
 select 10 as test01, 20 as test02, 'Hello' as test03 \gset pref01_
 \echo :pref01_test01 :pref01_test02 :pref01_test03
@@ -4507,9 +4553,11 @@ bar 'bar' "bar"
 	\pset fieldsep | `nosuchcommand` :foo :'foo' :"foo"
 	\a
 	SELECT $1 \bind 1 \g
+	\bindx stmt1 1 2 \g
 	\C arg1
 	\c arg1 arg2 arg3 arg4
 	\cd arg1
+	\close stmt1
 	\conninfo
 	\copy arg1 arg2 arg3 arg4 arg5 arg6
 	\copyright
@@ -4538,6 +4586,7 @@ invalid command \lo
 	\lo_list
 	\o arg1
 	\p
+	SELECT 1 \parse
 	\password arg1
 	\prompt arg1 arg2
 	\pset arg1 arg2
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 129f853353..2bec298a17 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -45,6 +45,27 @@ SELECT 1 as one, 2 as two \g (format=csv csv_fieldsep='\t')
 SELECT 1 as one, 2 as two \gx (title='foo bar')
 \g
 
+-- \parse (extended query protocol)
+
+SELECT 1 \parse ''
+SELECT 2 \parse stmt1
+SELECT $1 \parse stmt2
+SELECT $1, $2 \parse stmt3
+
+-- \bindx (extended query protocol)
+
+\bindx '' \g
+\bindx stmt1 \g
+\bindx stmt2 'foo' \g
+\bindx stmt3 'foo' 'bar' \g
+
+-- \close (extended query protocol)
+
+\close
+\close stmt2
+\close stmt2
+select name, statement from pg_prepared_statements order by name;
+
 -- \bind (extended query protocol)
 
 SELECT 1 \bind \g
@@ -58,6 +79,9 @@ SELECT foo \bind \g
 SELECT 1 \; SELECT 2 \bind \g
 -- bind error
 SELECT $1, $2 \bind 'foo' \g
+-- bindx error
+\bindx stmt2 'baz' \g
+\bindx stmt3 'baz' \g
 
 -- \gset
 
@@ -990,9 +1014,11 @@ select \if false \\ (bogus \else \\ 42 \endif \\ forty_two;
 	\pset fieldsep | `nosuchcommand` :foo :'foo' :"foo"
 	\a
 	SELECT $1 \bind 1 \g
+	\bindx stmt1 1 2 \g
 	\C arg1
 	\c arg1 arg2 arg3 arg4
 	\cd arg1
+	\close stmt1
 	\conninfo
 	\copy arg1 arg2 arg3 arg4 arg5 arg6
 	\copyright
@@ -1020,6 +1046,7 @@ select \if false \\ (bogus \else \\ 42 \endif \\ forty_two;
 	\lo_list
 	\o arg1
 	\p
+	SELECT 1 \parse
 	\password arg1
 	\prompt arg1 arg2
 	\pset arg1 arg2
-- 
2.39.3 (Apple Git-145)

From 5e892a65d65c899be251418f89283e374a485f8c Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
Date: Thu, 18 Jan 2024 08:34:26 +0100
Subject: [PATCH v4 1/2] psql: Add ignore_slash_options in bind's inactive
 branch

All commands accepting arguments call ignore_slash_options in inactive
branch to scan and discard extra arguments except bind.

This commit adds the missing ignore_slash_options to bin'd inactive
branch. The behaviour is unchanged as those extra arguments are
discarded later in HandleSlashCmds.
---
 src/bin/psql/command.c             | 2 ++
 src/test/regress/expected/psql.out | 1 +
 src/test/regress/sql/psql.sql      | 1 +
 3 files changed, 4 insertions(+)

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 9ad911e28d..5c906e4806 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -486,6 +486,8 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
 		pset.bind_nparams = nparams;
 		pset.bind_flag = true;
 	}
+	else
+		ignore_slash_options(scan_state);
 
 	return status;
 }
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 4f3fd46420..ad02772562 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -4506,6 +4506,7 @@ bar 'bar' "bar"
 	\echo `nosuchcommand` :foo :'foo' :"foo"
 	\pset fieldsep | `nosuchcommand` :foo :'foo' :"foo"
 	\a
+	SELECT $1 \bind 1 \g
 	\C arg1
 	\c arg1 arg2 arg3 arg4
 	\cd arg1
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index c997106b9f..129f853353 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -989,6 +989,7 @@ select \if false \\ (bogus \else \\ 42 \endif \\ forty_two;
 	\echo `nosuchcommand` :foo :'foo' :"foo"
 	\pset fieldsep | `nosuchcommand` :foo :'foo' :"foo"
 	\a
+	SELECT $1 \bind 1 \g
 	\C arg1
 	\c arg1 arg2 arg3 arg4
 	\cd arg1
-- 
2.39.3 (Apple Git-145)

From fed9e757c793815251fe41069cc8f01a5f8aacb8 Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
Date: Thu, 18 Jan 2024 08:46:33 +0100
Subject: [PATCH v5] psql: Add support for prepared stmt with extended protocol

Currently, only unnamed prepared statement is supported by psql with the
\bind command so it's not possible to test named statement creation and
execution through extended protocol.

This commit introduces three additional commands: \parse, \bindx and
\close.
\parse creates a prepared statement using extended protocol.
\bindx binds and execute an existing prepared statement using extended
protocol.
\close closes an existing prepared statement using extended protocol.
---
 doc/src/sgml/ref/psql-ref.sgml     |  86 +++++++++++++++++++++
 src/bin/psql/command.c             | 115 +++++++++++++++++++++++++++++
 src/bin/psql/common.c              |  28 ++++++-
 src/bin/psql/help.c                |   4 +
 src/bin/psql/settings.h            |   6 ++
 src/bin/psql/tab-complete.c        |   6 +-
 src/test/regress/expected/psql.out |  49 ++++++++++++
 src/test/regress/sql/psql.sql      |  27 +++++++
 8 files changed, 316 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index cc7d797159..b0f383ab2a 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -916,6 +916,35 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
        </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-meta-command-bindx">
+       <term><literal>\bindx</literal> <replaceable class="parameter">prepared_statement_name</replaceable> [ <replaceable class="parameter">parameter</replaceable> ] ... </term>
+
+       <listitem>
+        <para>
+        <literal>\bindx</literal> is equivalent to <literal>\bind</literal>,
+         except that it takes the name of an explicit existing prepared
+         statement as first parameter.
+        </para>
+
+        <para>
+         Example:
+<programlisting>
+INSERT INTO tbls1 VALUES ($1, $2) \parse stmt1
+\bindx stmt1 'first value' 'second value' \g
+</programlisting>
+        </para>
+
+        <para>
+         This command causes the extended query protocol (see <xref
+         linkend="protocol-query-concepts"/>) to be used, unlike normal
+         <application>psql</application> operation, which uses the simple
+         query protocol. So this command can be useful to test the extended
+         query protocol from psql.
+        </para>
+
+       </listitem>
+      </varlistentry>
+
       <varlistentry id="app-psql-meta-command-c-lc">
         <term><literal>\c</literal> or <literal>\connect [ -reuse-previous=<replaceable class="parameter">on|off</replaceable> ] [ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] [ <replaceable class="parameter">host</replaceable> ] [ <replaceable class="parameter">port</replaceable> ] | <replaceable class="parameter">conninfo</replaceable> ]</literal></term>
         <listitem>
@@ -1037,6 +1066,35 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
         </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-meta-command-close">
+       <term><literal>\close</literal> [ <replaceable class="parameter">prepared_statement_name</replaceable> ] </term>
+
+       <listitem>
+        <para>
+        Closes the specified prepared statement. Without argument, closes the unnamed statement.
+        If no prepared statement exists with this name, the operation is a no-op.
+        </para>
+
+        <para>
+         Example:
+<programlisting>
+SELECT $1 \parse stmt1
+\close stmt1
+</programlisting>
+        </para>
+
+        <para>
+         This command causes the extended query protocol (see <xref
+         linkend="protocol-query-concepts"/>) to be used, unlike normal
+         <application>psql</application> operation, which uses the simple
+         query protocol. So this command can be useful to test the extended
+         query protocol from psql.
+        </para>
+
+       </listitem>
+      </varlistentry>
+
+
       <varlistentry id="app-psql-meta-commands-copy">
         <term><literal>\copy { <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column_list</replaceable> ) ] }
         <literal>from</literal>
@@ -2779,6 +2837,34 @@ lo_import 152801
         </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-meta-command-parse">
+        <term><literal>\parse <replaceable class="parameter">prepared_statement_name</replaceable></literal></term>
+        <listitem>
+        <para>
+        Creates a prepared statement from the current query buffer
+        </para>
+
+        <para>
+         Example:
+<programlisting>
+SELECT $1 \parse stmt1
+</programlisting>
+        </para>
+
+        <para>
+         This command causes the extended query protocol (see <xref
+         linkend="protocol-query-concepts"/>) to be used, unlike normal
+         <application>psql</application> operation, which uses the simple
+         query protocol. A <xref linkend="protocol-message-formats-Parse"/>
+         message will be issued by this command so it can be useful to
+         test the extended query protocol from psql. This command affects
+         only the next query executed; all subsequent queries will use the
+         simple query protocol by default.
+        </para>
+
+        </listitem>
+      </varlistentry>
+
       <varlistentry id="app-psql-meta-command-password">
         <term><literal>\password [ <replaceable class="parameter">username</replaceable> ]</literal></term>
         <listitem>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 5c906e4806..07d58ec66f 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -64,10 +64,14 @@ static backslashResult exec_command(const char *cmd,
 									PQExpBuffer previous_buf);
 static backslashResult exec_command_a(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_bind(PsqlScanState scan_state, bool active_branch);
+static backslashResult exec_command_bindx(PsqlScanState scan_state, bool active_branch,
+										  const char *cmd);
 static backslashResult exec_command_C(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_connect(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_cd(PsqlScanState scan_state, bool active_branch,
 									   const char *cmd);
+static backslashResult exec_command_close(PsqlScanState scan_state, bool active_branch,
+									   const char *cmd);
 static backslashResult exec_command_conninfo(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_copy(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_copyright(PsqlScanState scan_state, bool active_branch);
@@ -116,6 +120,8 @@ static backslashResult exec_command_lo(PsqlScanState scan_state, bool active_bra
 static backslashResult exec_command_out(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_print(PsqlScanState scan_state, bool active_branch,
 										  PQExpBuffer query_buf, PQExpBuffer previous_buf);
+static backslashResult exec_command_parse(PsqlScanState scan_state, bool active_branch,
+										  const char* cmd);
 static backslashResult exec_command_password(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_prompt(PsqlScanState scan_state, bool active_branch,
 										   const char *cmd);
@@ -311,12 +317,16 @@ exec_command(const char *cmd,
 		status = exec_command_a(scan_state, active_branch);
 	else if (strcmp(cmd, "bind") == 0)
 		status = exec_command_bind(scan_state, active_branch);
+	else if (strcmp(cmd, "bindx") == 0)
+		status = exec_command_bindx(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "C") == 0)
 		status = exec_command_C(scan_state, active_branch);
 	else if (strcmp(cmd, "c") == 0 || strcmp(cmd, "connect") == 0)
 		status = exec_command_connect(scan_state, active_branch);
 	else if (strcmp(cmd, "cd") == 0)
 		status = exec_command_cd(scan_state, active_branch, cmd);
+	else if (strcmp(cmd, "close") == 0)
+		status = exec_command_close(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "conninfo") == 0)
 		status = exec_command_conninfo(scan_state, active_branch);
 	else if (pg_strcasecmp(cmd, "copy") == 0)
@@ -378,6 +388,8 @@ exec_command(const char *cmd,
 	else if (strcmp(cmd, "p") == 0 || strcmp(cmd, "print") == 0)
 		status = exec_command_print(scan_state, active_branch,
 									query_buf, previous_buf);
+	else if (strcmp(cmd, "parse") == 0)
+		status = exec_command_parse(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "password") == 0)
 		status = exec_command_password(scan_state, active_branch);
 	else if (strcmp(cmd, "prompt") == 0)
@@ -471,6 +483,7 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
 		int			nalloc = 0;
 
 		pset.bind_params = NULL;
+		pset.stmtName = NULL;
 
 		while ((opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false)))
 		{
@@ -492,6 +505,53 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
 	return status;
 }
 
+/*
+ * \bindx -- set query parameters for an existing prepared statement
+ */
+static backslashResult
+exec_command_bindx(PsqlScanState scan_state, bool active_branch,
+				   const char* cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		char	   *opt;
+		int			nparams = 0;
+		int			nalloc = 0;
+
+		pset.bind_params = NULL;
+		pset.stmtName = NULL;
+
+		opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false);
+		if (!opt)
+		{
+			pg_log_error("\\%s: missing required argument", cmd);
+			status = PSQL_CMD_ERROR;
+			free(opt);
+		} else {
+			pset.stmtName = opt;
+			pset.bind_flag = true;
+
+			while ((opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false)))
+			{
+				nparams++;
+				if (nparams > nalloc)
+				{
+					nalloc = nalloc ? nalloc * 2 : 1;
+					pset.bind_params = pg_realloc_array(pset.bind_params, char *, nalloc);
+				}
+				pset.bind_params[nparams - 1] = opt;
+			}
+			pset.bind_nparams = nparams;
+		}
+	}
+	else
+		ignore_slash_options(scan_state);
+
+	return status;
+}
+
 /*
  * \C -- override table title (formerly change HTML caption)
  */
@@ -642,6 +702,28 @@ exec_command_cd(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	return success ? PSQL_CMD_SKIP_LINE : PSQL_CMD_ERROR;
 }
 
+/*
+ * \close -- close a previously prepared statement
+ */
+static backslashResult
+exec_command_close(PsqlScanState scan_state, bool active_branch, const char *cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		char	   *opt = psql_scan_slash_option(scan_state,
+												 OT_NORMAL, NULL, false);
+		pset.stmtName = opt;
+		pset.close_flag = true;
+		status = PSQL_CMD_SEND;
+	}
+	else
+		ignore_slash_options(scan_state);
+
+	return status;
+}
+
 /*
  * \conninfo -- display information about the current connection
  */
@@ -2095,6 +2177,39 @@ exec_command_print(PsqlScanState scan_state, bool active_branch,
 	return PSQL_CMD_SKIP_LINE;
 }
 
+/*
+ * \parse -- parse query
+ */
+static backslashResult
+exec_command_parse(PsqlScanState scan_state, bool active_branch,
+				   const char* cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		char	   *opt = psql_scan_slash_option(scan_state,
+												 OT_NORMAL, NULL, false);
+		pset.stmtName = NULL;
+		if (!opt)
+		{
+			pg_log_error("\\%s: missing required argument", cmd);
+			status = PSQL_CMD_ERROR;
+			free(opt);
+		}
+		else
+		{
+			pset.stmtName = opt;
+			pset.parse_flag = true;
+			status = PSQL_CMD_SEND;
+		}
+	}
+	else
+		ignore_slash_options(scan_state);
+
+	return status;
+}
+
 /*
  * \password -- set user password
  */
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 76e01b02a3..2e853b71c4 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1241,16 +1241,34 @@ sendquery_cleanup:
 		pset.gsavepopt = NULL;
 	}
 
-	/* clean up after \bind */
+	/* clean up after \bind or \bindx */
 	if (pset.bind_flag)
 	{
 		for (i = 0; i < pset.bind_nparams; i++)
 			free(pset.bind_params[i]);
 		free(pset.bind_params);
+		free(pset.stmtName);
 		pset.bind_params = NULL;
+		pset.stmtName = NULL;
 		pset.bind_flag = false;
 	}
 
+	/* clean up after \parse */
+	if (pset.parse_flag)
+	{
+		free(pset.stmtName);
+		pset.stmtName = NULL;
+		pset.parse_flag = false;
+	}
+
+	/* clean up after \close */
+	if (pset.close_flag)
+	{
+		free(pset.stmtName);
+		pset.stmtName = NULL;
+		pset.close_flag = false;
+	}
+
 	/* reset \gset trigger */
 	if (pset.gset_prefix)
 	{
@@ -1433,8 +1451,14 @@ ExecQueryAndProcessResults(const char *query,
 	else
 		INSTR_TIME_SET_ZERO(before);
 
-	if (pset.bind_flag)
+	if (pset.bind_flag && pset.stmtName == NULL)
 		success = PQsendQueryParams(pset.db, query, pset.bind_nparams, NULL, (const char *const *) pset.bind_params, NULL, NULL, 0);
+	else if (pset.bind_flag && pset.stmtName != NULL)
+		success = PQsendQueryPrepared(pset.db, pset.stmtName, pset.bind_nparams, (const char *const *) pset.bind_params, NULL, NULL, 0);
+	else if (pset.close_flag)
+		success = PQsendClosePrepared(pset.db, pset.stmtName);
+	else if (pset.parse_flag)
+		success = PQsendPrepare(pset.db, pset.stmtName, query, 0, NULL);
 	else
 		success = PQsendQuery(pset.db, query);
 
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 4e79a819d8..bb2e66c109 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -165,6 +165,9 @@ slashUsage(unsigned short int pager)
 
 	HELP0("General\n");
 	HELP0("  \\bind [PARAM]...       set query parameters\n");
+	HELP0("  \\bindx STMT_NAME [PARAM]...\n"
+		  "                         set query parameters for an existing prepared statement\n");
+	HELP0("  \\close STMT_NAME       close an existing prepared statement\n");
 	HELP0("  \\copyright             show PostgreSQL usage and distribution terms\n");
 	HELP0("  \\crosstabview [COLUMNS] execute query and display result in crosstab\n");
 	HELP0("  \\errverbose            show most recent error message at maximum verbosity\n");
@@ -312,6 +315,7 @@ slashUsage(unsigned short int pager)
 			  "                         connect to new database (currently no connection)\n");
 	HELP0("  \\conninfo              display information about current connection\n");
 	HELP0("  \\encoding [ENCODING]   show or set client encoding\n");
+	HELP0("  \\parse STMT_NAME       create a prepared statement\n");
 	HELP0("  \\password [USERNAME]   securely change the password for a user\n");
 	HELP0("\n");
 
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 505f99d8e4..4048c45ebf 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -100,6 +100,12 @@ typedef struct _psqlSettings
 								 * protocol */
 	int			bind_nparams;	/* number of parameters */
 	char	  **bind_params;	/* parameters for extended query protocol call */
+	bool		close_flag;		/* one-shot request to close a prepared statement using
+								 * extended query protocol */
+	bool		parse_flag;		/* one-shot request to parse query using extended query
+								 * protocol */
+	char	   *stmtName;		/* prepared statement name used for extended query
+								 * protocol commands */
 	bool		crosstab_flag;	/* one-shot request to crosstab result */
 	char	   *ctv_args[4];	/* \crosstabview arguments */
 
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index ada711d02f..7c6f4a5ff4 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1712,8 +1712,8 @@ psql_completion(const char *text, int start, int end)
 	/* psql's backslash commands. */
 	static const char *const backslash_commands[] = {
 		"\\a",
-		"\\bind",
-		"\\connect", "\\conninfo", "\\C", "\\cd", "\\copy",
+		"\\bind", "\\bindx",
+		"\\connect", "\\conninfo", "\\C", "\\cd", "\\close", "\\copy",
 		"\\copyright", "\\crosstabview",
 		"\\d", "\\da", "\\dA", "\\dAc", "\\dAf", "\\dAo", "\\dAp",
 		"\\db", "\\dc", "\\dconfig", "\\dC", "\\dd", "\\ddp", "\\dD",
@@ -1730,7 +1730,7 @@ psql_completion(const char *text, int start, int end)
 		"\\if", "\\include", "\\include_relative", "\\ir",
 		"\\list", "\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
 		"\\out",
-		"\\password", "\\print", "\\prompt", "\\pset",
+		"\\parse", "\\password", "\\print", "\\prompt", "\\pset",
 		"\\qecho", "\\quit",
 		"\\reset",
 		"\\s", "\\set", "\\setenv", "\\sf", "\\sv",
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index ad02772562..4a2dbdd38f 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -98,6 +98,47 @@ two | 2
    1 |   2
 (1 row)
 
+-- \parse (extended query protocol)
+SELECT 1 \parse ''
+SELECT 2 \parse stmt1
+SELECT $1 \parse stmt2
+SELECT $1, $2 \parse stmt3
+-- \bindx (extended query protocol)
+\bindx '' \g
+ ?column? 
+----------
+        1
+(1 row)
+
+\bindx stmt1 \g
+ ?column? 
+----------
+        2
+(1 row)
+
+\bindx stmt2 'foo' \g
+ ?column? 
+----------
+ foo
+(1 row)
+
+\bindx stmt3 'foo' 'bar' \g
+ ?column? | ?column? 
+----------+----------
+ foo      | bar
+(1 row)
+
+-- \close (extended query protocol)
+\close
+\close stmt2
+\close stmt2
+select name, statement from pg_prepared_statements order by name;
+ name  |   statement    
+-------+----------------
+ stmt1 | SELECT 2 
+ stmt3 | SELECT $1, $2 
+(2 rows)
+
 -- \bind (extended query protocol)
 SELECT 1 \bind \g
  ?column? 
@@ -129,6 +170,11 @@ ERROR:  cannot insert multiple commands into a prepared statement
 -- bind error
 SELECT $1, $2 \bind 'foo' \g
 ERROR:  bind message supplies 1 parameters, but prepared statement "" requires 2
+-- bindx error
+\bindx stmt2 'baz' \g
+ERROR:  prepared statement "stmt2" does not exist
+\bindx stmt3 'baz' \g
+ERROR:  bind message supplies 1 parameters, but prepared statement "stmt3" requires 2
 -- \gset
 select 10 as test01, 20 as test02, 'Hello' as test03 \gset pref01_
 \echo :pref01_test01 :pref01_test02 :pref01_test03
@@ -4507,9 +4553,11 @@ bar 'bar' "bar"
 	\pset fieldsep | `nosuchcommand` :foo :'foo' :"foo"
 	\a
 	SELECT $1 \bind 1 \g
+	\bindx stmt1 1 2 \g
 	\C arg1
 	\c arg1 arg2 arg3 arg4
 	\cd arg1
+	\close stmt1
 	\conninfo
 	\copy arg1 arg2 arg3 arg4 arg5 arg6
 	\copyright
@@ -4538,6 +4586,7 @@ invalid command \lo
 	\lo_list
 	\o arg1
 	\p
+	SELECT 1 \parse
 	\password arg1
 	\prompt arg1 arg2
 	\pset arg1 arg2
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 129f853353..2bec298a17 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -45,6 +45,27 @@ SELECT 1 as one, 2 as two \g (format=csv csv_fieldsep='\t')
 SELECT 1 as one, 2 as two \gx (title='foo bar')
 \g
 
+-- \parse (extended query protocol)
+
+SELECT 1 \parse ''
+SELECT 2 \parse stmt1
+SELECT $1 \parse stmt2
+SELECT $1, $2 \parse stmt3
+
+-- \bindx (extended query protocol)
+
+\bindx '' \g
+\bindx stmt1 \g
+\bindx stmt2 'foo' \g
+\bindx stmt3 'foo' 'bar' \g
+
+-- \close (extended query protocol)
+
+\close
+\close stmt2
+\close stmt2
+select name, statement from pg_prepared_statements order by name;
+
 -- \bind (extended query protocol)
 
 SELECT 1 \bind \g
@@ -58,6 +79,9 @@ SELECT foo \bind \g
 SELECT 1 \; SELECT 2 \bind \g
 -- bind error
 SELECT $1, $2 \bind 'foo' \g
+-- bindx error
+\bindx stmt2 'baz' \g
+\bindx stmt3 'baz' \g
 
 -- \gset
 
@@ -990,9 +1014,11 @@ select \if false \\ (bogus \else \\ 42 \endif \\ forty_two;
 	\pset fieldsep | `nosuchcommand` :foo :'foo' :"foo"
 	\a
 	SELECT $1 \bind 1 \g
+	\bindx stmt1 1 2 \g
 	\C arg1
 	\c arg1 arg2 arg3 arg4
 	\cd arg1
+	\close stmt1
 	\conninfo
 	\copy arg1 arg2 arg3 arg4 arg5 arg6
 	\copyright
@@ -1020,6 +1046,7 @@ select \if false \\ (bogus \else \\ 42 \endif \\ forty_two;
 	\lo_list
 	\o arg1
 	\p
+	SELECT 1 \parse
 	\password arg1
 	\prompt arg1 arg2
 	\pset arg1 arg2
-- 
2.34.1

From 62303eeab20740f8e7bba36d6595b2a02771e5aa Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
Date: Thu, 18 Jan 2024 08:46:33 +0100
Subject: [PATCH v6 1/2] psql: Add support for prepared stmt with extended
 protocol

Currently, only unnamed prepared statement is supported by psql with the
\bind command so it's not possible to test named statement creation and
execution through extended protocol.

This commit introduces three additional commands: \parse, \bindx and
\close.
\parse creates a prepared statement using extended protocol.
\bindx binds and execute an existing prepared statement using extended
protocol.
\close closes an existing prepared statement using extended protocol.
---
 src/bin/psql/command.c             | 128 +++++++++++++++++++++++++++++
 src/bin/psql/common.c              |  36 +++++++-
 src/bin/psql/help.c                |   4 +
 src/bin/psql/settings.h            |   6 ++
 src/bin/psql/tab-complete.c        |   6 +-
 src/test/regress/expected/psql.out |  55 +++++++++++++
 src/test/regress/sql/psql.sql      |  28 ++++++-
 doc/src/sgml/ref/psql-ref.sgml     |  90 ++++++++++++++++++++
 8 files changed, 346 insertions(+), 7 deletions(-)

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 180781ecd0..f23a7404cc 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -64,10 +64,14 @@ static backslashResult exec_command(const char *cmd,
 									PQExpBuffer previous_buf);
 static backslashResult exec_command_a(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_bind(PsqlScanState scan_state, bool active_branch);
+static backslashResult exec_command_bindx(PsqlScanState scan_state, bool active_branch,
+										  const char *cmd);
 static backslashResult exec_command_C(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_connect(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_cd(PsqlScanState scan_state, bool active_branch,
 									   const char *cmd);
+static backslashResult exec_command_close(PsqlScanState scan_state, bool active_branch,
+										  const char *cmd);
 static backslashResult exec_command_conninfo(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_copy(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_copyright(PsqlScanState scan_state, bool active_branch);
@@ -116,6 +120,8 @@ static backslashResult exec_command_lo(PsqlScanState scan_state, bool active_bra
 static backslashResult exec_command_out(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_print(PsqlScanState scan_state, bool active_branch,
 										  PQExpBuffer query_buf, PQExpBuffer previous_buf);
+static backslashResult exec_command_parse(PsqlScanState scan_state, bool active_branch,
+										  const char *cmd);
 static backslashResult exec_command_password(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_prompt(PsqlScanState scan_state, bool active_branch,
 										   const char *cmd);
@@ -312,12 +318,16 @@ exec_command(const char *cmd,
 		status = exec_command_a(scan_state, active_branch);
 	else if (strcmp(cmd, "bind") == 0)
 		status = exec_command_bind(scan_state, active_branch);
+	else if (strcmp(cmd, "bindx") == 0)
+		status = exec_command_bindx(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "C") == 0)
 		status = exec_command_C(scan_state, active_branch);
 	else if (strcmp(cmd, "c") == 0 || strcmp(cmd, "connect") == 0)
 		status = exec_command_connect(scan_state, active_branch);
 	else if (strcmp(cmd, "cd") == 0)
 		status = exec_command_cd(scan_state, active_branch, cmd);
+	else if (strcmp(cmd, "close") == 0)
+		status = exec_command_close(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "conninfo") == 0)
 		status = exec_command_conninfo(scan_state, active_branch);
 	else if (pg_strcasecmp(cmd, "copy") == 0)
@@ -379,6 +389,8 @@ exec_command(const char *cmd,
 	else if (strcmp(cmd, "p") == 0 || strcmp(cmd, "print") == 0)
 		status = exec_command_print(scan_state, active_branch,
 									query_buf, previous_buf);
+	else if (strcmp(cmd, "parse") == 0)
+		status = exec_command_parse(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "password") == 0)
 		status = exec_command_password(scan_state, active_branch);
 	else if (strcmp(cmd, "prompt") == 0)
@@ -472,6 +484,7 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
 		int			nalloc = 0;
 
 		pset.bind_params = NULL;
+		pset.stmtName = NULL;
 
 		while ((opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false)))
 		{
@@ -493,6 +506,56 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
 	return status;
 }
 
+/*
+ * \bindx -- set query parameters for an existing prepared statement
+ */
+static backslashResult
+exec_command_bindx(PsqlScanState scan_state, bool active_branch,
+				   const char *cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		char	   *opt;
+		int			nparams = 0;
+		int			nalloc = 0;
+
+		pset.bind_params = NULL;
+		pset.stmtName = NULL;
+
+		/* get the mandatory prepared statement name */
+		opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false);
+		if (!opt)
+		{
+			pg_log_error("\\%s: missing required argument", cmd);
+			status = PSQL_CMD_ERROR;
+		}
+		else
+		{
+			pset.stmtName = opt;
+			pset.bind_flag = true;
+
+			/* set of parameters */
+			while ((opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false)))
+			{
+				nparams++;
+				if (nparams > nalloc)
+				{
+					nalloc = nalloc ? nalloc * 2 : 1;
+					pset.bind_params = pg_realloc_array(pset.bind_params, char *, nalloc);
+				}
+				pset.bind_params[nparams - 1] = opt;
+			}
+			pset.bind_nparams = nparams;
+		}
+	}
+	else
+		ignore_slash_options(scan_state);
+
+	return status;
+}
+
 /*
  * \C -- override table title (formerly change HTML caption)
  */
@@ -643,6 +706,38 @@ exec_command_cd(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	return success ? PSQL_CMD_SKIP_LINE : PSQL_CMD_ERROR;
 }
 
+/*
+ * \close -- close a previously prepared statement
+ */
+static backslashResult
+exec_command_close(PsqlScanState scan_state, bool active_branch, const char *cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		char	   *opt = psql_scan_slash_option(scan_state,
+												 OT_NORMAL, NULL, false);
+
+		pset.stmtName = NULL;
+		if (!opt)
+		{
+			pg_log_error("\\%s: missing required argument", cmd);
+			status = PSQL_CMD_ERROR;
+		}
+		else
+		{
+			pset.stmtName = opt;
+			pset.close_flag = true;
+			status = PSQL_CMD_SEND;
+		}
+	}
+	else
+		ignore_slash_options(scan_state);
+
+	return status;
+}
+
 /*
  * \conninfo -- display information about the current connection
  */
@@ -2096,6 +2191,39 @@ exec_command_print(PsqlScanState scan_state, bool active_branch,
 	return PSQL_CMD_SKIP_LINE;
 }
 
+/*
+ * \parse -- parse query
+ */
+static backslashResult
+exec_command_parse(PsqlScanState scan_state, bool active_branch,
+				   const char *cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		char	   *opt = psql_scan_slash_option(scan_state,
+												 OT_NORMAL, NULL, false);
+
+		pset.stmtName = NULL;
+		if (!opt)
+		{
+			pg_log_error("\\%s: missing required argument", cmd);
+			status = PSQL_CMD_ERROR;
+		}
+		else
+		{
+			pset.stmtName = opt;
+			pset.parse_flag = true;
+			status = PSQL_CMD_SEND;
+		}
+	}
+	else
+		ignore_slash_options(scan_state);
+
+	return status;
+}
+
 /*
  * \password -- set user password
  */
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index fe8e049c4c..1710ee9fc5 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1274,16 +1274,34 @@ sendquery_cleanup:
 		pset.gsavepopt = NULL;
 	}
 
-	/* clean up after \bind */
+	/* clean up after \bind or \bindx */
 	if (pset.bind_flag)
 	{
 		for (i = 0; i < pset.bind_nparams; i++)
 			free(pset.bind_params[i]);
 		free(pset.bind_params);
+		free(pset.stmtName);
 		pset.bind_params = NULL;
+		pset.stmtName = NULL;
 		pset.bind_flag = false;
 	}
 
+	/* clean up after \parse */
+	if (pset.parse_flag)
+	{
+		free(pset.stmtName);
+		pset.stmtName = NULL;
+		pset.parse_flag = false;
+	}
+
+	/* clean up after \close */
+	if (pset.close_flag)
+	{
+		free(pset.stmtName);
+		pset.stmtName = NULL;
+		pset.close_flag = false;
+	}
+
 	/* reset \gset trigger */
 	if (pset.gset_prefix)
 	{
@@ -1469,8 +1487,20 @@ ExecQueryAndProcessResults(const char *query,
 	else
 		INSTR_TIME_SET_ZERO(before);
 
-	if (pset.bind_flag)
-		success = PQsendQueryParams(pset.db, query, pset.bind_nparams, NULL, (const char *const *) pset.bind_params, NULL, NULL, 0);
+	if (pset.bind_flag && pset.stmtName == NULL)	/* \bind */
+		success = PQsendQueryParams(pset.db, query,
+									pset.bind_nparams, NULL,
+									(const char *const *) pset.bind_params,
+									NULL, NULL, 0);
+	else if (pset.bind_flag && pset.stmtName != NULL)	/* \bindx */
+		success = PQsendQueryPrepared(pset.db, pset.stmtName,
+									  pset.bind_nparams,
+									  (const char *const *) pset.bind_params,
+									  NULL, NULL, 0);
+	else if (pset.close_flag)	/* \close */
+		success = PQsendClosePrepared(pset.db, pset.stmtName);
+	else if (pset.parse_flag)	/* \parse */
+		success = PQsendPrepare(pset.db, pset.stmtName, query, 0, NULL);
 	else
 		success = PQsendQuery(pset.db, query);
 
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 6f58a11074..b1f167d1dd 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -165,6 +165,9 @@ slashUsage(unsigned short int pager)
 
 	HELP0("General\n");
 	HELP0("  \\bind [PARAM]...       set query parameters\n");
+	HELP0("  \\bindx STMT_NAME [PARAM]...\n"
+		  "                         set query parameters for an existing prepared statement\n");
+	HELP0("  \\close STMT_NAME       close an existing prepared statement\n");
 	HELP0("  \\copyright             show PostgreSQL usage and distribution terms\n");
 	HELP0("  \\crosstabview [COLUMNS] execute query and display result in crosstab\n");
 	HELP0("  \\errverbose            show most recent error message at maximum verbosity\n");
@@ -312,6 +315,7 @@ slashUsage(unsigned short int pager)
 			  "                         connect to new database (currently no connection)\n");
 	HELP0("  \\conninfo              display information about current connection\n");
 	HELP0("  \\encoding [ENCODING]   show or set client encoding\n");
+	HELP0("  \\parse STMT_NAME       create a prepared statement\n");
 	HELP0("  \\password [USERNAME]   securely change the password for a user\n");
 	HELP0("\n");
 
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 505f99d8e4..485fca871c 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -100,6 +100,12 @@ typedef struct _psqlSettings
 								 * protocol */
 	int			bind_nparams;	/* number of parameters */
 	char	  **bind_params;	/* parameters for extended query protocol call */
+	bool		close_flag;		/* one-shot request to close a prepared
+								 * statement using extended query protocol */
+	bool		parse_flag;		/* one-shot request to parse query using
+								 * extended query protocol */
+	char	   *stmtName;		/* prepared statement name used for extended
+								 * query protocol commands */
 	bool		crosstab_flag;	/* one-shot request to crosstab result */
 	char	   *ctv_args[4];	/* \crosstabview arguments */
 
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index d453e224d9..6bf231cea6 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1713,8 +1713,8 @@ psql_completion(const char *text, int start, int end)
 	/* psql's backslash commands. */
 	static const char *const backslash_commands[] = {
 		"\\a",
-		"\\bind",
-		"\\connect", "\\conninfo", "\\C", "\\cd", "\\copy",
+		"\\bind", "\\bindx",
+		"\\connect", "\\conninfo", "\\C", "\\cd", "\\close", "\\copy",
 		"\\copyright", "\\crosstabview",
 		"\\d", "\\da", "\\dA", "\\dAc", "\\dAf", "\\dAo", "\\dAp",
 		"\\db", "\\dc", "\\dconfig", "\\dC", "\\dd", "\\ddp", "\\dD",
@@ -1731,7 +1731,7 @@ psql_completion(const char *text, int start, int end)
 		"\\if", "\\include", "\\include_relative", "\\ir",
 		"\\list", "\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
 		"\\out",
-		"\\password", "\\print", "\\prompt", "\\pset",
+		"\\parse", "\\password", "\\print", "\\prompt", "\\pset",
 		"\\qecho", "\\quit",
 		"\\reset",
 		"\\s", "\\set", "\\setenv", "\\sf", "\\sv",
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 3bbe4c5f97..79e8e5f8cd 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -98,6 +98,53 @@ two | 2
    1 |   2
 (1 row)
 
+-- \parse (extended query protocol)
+\parse
+\parse: missing required argument
+SELECT 1 \parse ''
+SELECT 2 \parse stmt1
+SELECT $1 \parse stmt2
+SELECT $1, $2 \parse stmt3
+-- \bindx (extended query protocol)
+\bindx
+\bindx: missing required argument
+\bindx '' \g
+ ?column? 
+----------
+        1
+(1 row)
+
+\bindx stmt1 \g
+ ?column? 
+----------
+        2
+(1 row)
+
+\bindx stmt2 'foo' \g
+ ?column? 
+----------
+ foo
+(1 row)
+
+\bindx stmt3 'foo' 'bar' \g
+ ?column? | ?column? 
+----------+----------
+ foo      | bar
+(1 row)
+
+-- \close (extended query protocol)
+\close
+\close: missing required argument
+\close ''
+\close stmt2
+\close stmt2
+SELECT name, statement FROM pg_prepared_statements ORDER BY name;
+ name  |   statement    
+-------+----------------
+ stmt1 | SELECT 2 
+ stmt3 | SELECT $1, $2 
+(2 rows)
+
 -- \bind (extended query protocol)
 SELECT 1 \bind \g
  ?column? 
@@ -129,6 +176,11 @@ ERROR:  cannot insert multiple commands into a prepared statement
 -- bind error
 SELECT $1, $2 \bind 'foo' \g
 ERROR:  bind message supplies 1 parameters, but prepared statement "" requires 2
+-- bindx error
+\bindx stmt2 'baz' \g
+ERROR:  prepared statement "stmt2" does not exist
+\bindx stmt3 'baz' \g
+ERROR:  bind message supplies 1 parameters, but prepared statement "stmt3" requires 2
 -- \gset
 select 10 as test01, 20 as test02, 'Hello' as test03 \gset pref01_
 \echo :pref01_test01 :pref01_test02 :pref01_test03
@@ -4507,9 +4559,11 @@ bar 'bar' "bar"
 	\pset fieldsep | `nosuchcommand` :foo :'foo' :"foo"
 	\a
 	SELECT $1 \bind 1 \g
+	\bindx stmt1 1 2 \g
 	\C arg1
 	\c arg1 arg2 arg3 arg4
 	\cd arg1
+	\close stmt1
 	\conninfo
 	\copy arg1 arg2 arg3 arg4 arg5 arg6
 	\copyright
@@ -4538,6 +4592,7 @@ invalid command \lo
 	\lo_list
 	\o arg1
 	\p
+	SELECT 1 \parse
 	\password arg1
 	\prompt arg1 arg2
 	\pset arg1 arg2
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 3b3c6f6e29..da233109a0 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -45,8 +45,28 @@ SELECT 1 as one, 2 as two \g (format=csv csv_fieldsep='\t')
 SELECT 1 as one, 2 as two \gx (title='foo bar')
 \g
 
--- \bind (extended query protocol)
+-- \parse (extended query protocol)
+\parse
+SELECT 1 \parse ''
+SELECT 2 \parse stmt1
+SELECT $1 \parse stmt2
+SELECT $1, $2 \parse stmt3
 
+-- \bindx (extended query protocol)
+\bindx
+\bindx '' \g
+\bindx stmt1 \g
+\bindx stmt2 'foo' \g
+\bindx stmt3 'foo' 'bar' \g
+
+-- \close (extended query protocol)
+\close
+\close ''
+\close stmt2
+\close stmt2
+SELECT name, statement FROM pg_prepared_statements ORDER BY name;
+
+-- \bind (extended query protocol)
 SELECT 1 \bind \g
 SELECT $1 \bind 'foo' \g
 SELECT $1, $2 \bind 'foo' 'bar' \g
@@ -58,6 +78,9 @@ SELECT foo \bind \g
 SELECT 1 \; SELECT 2 \bind \g
 -- bind error
 SELECT $1, $2 \bind 'foo' \g
+-- bindx error
+\bindx stmt2 'baz' \g
+\bindx stmt3 'baz' \g
 
 -- \gset
 
@@ -990,9 +1013,11 @@ select \if false \\ (bogus \else \\ 42 \endif \\ forty_two;
 	\pset fieldsep | `nosuchcommand` :foo :'foo' :"foo"
 	\a
 	SELECT $1 \bind 1 \g
+	\bindx stmt1 1 2 \g
 	\C arg1
 	\c arg1 arg2 arg3 arg4
 	\cd arg1
+	\close stmt1
 	\conninfo
 	\copy arg1 arg2 arg3 arg4 arg5 arg6
 	\copyright
@@ -1020,6 +1045,7 @@ select \if false \\ (bogus \else \\ 42 \endif \\ forty_two;
 	\lo_list
 	\o arg1
 	\p
+	SELECT 1 \parse
 	\password arg1
 	\prompt arg1 arg2
 	\pset arg1 arg2
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 830306ea1e..dd2f805357 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -916,6 +916,36 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
        </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-meta-command-bindx">
+       <term><literal>\bindx</literal> <replaceable class="parameter">statement_name</replaceable> [ <replaceable class="parameter">parameter</replaceable> ] ... </term>
+
+       <listitem>
+        <para>
+         <literal>\bindx</literal> is equivalent to <literal>\bind</literal>,
+         except that it takes the name of an existing prepared statement as
+         first parameter. An empty string denotes the unnamed prepared
+         statement.
+        </para>
+
+        <para>
+         Example:
+<programlisting>
+INSERT INTO tbls1 VALUES ($1, $2) \parse stmt1
+\bindx stmt1 'first value' 'second value' \g
+</programlisting>
+        </para>
+
+        <para>
+         This command causes the extended query protocol (see
+         <xref linkend="protocol-query-concepts"/>) to be used, unlike normal
+         <application>psql</application> operation, which uses the simple
+         query protocol. So this command can be useful to test the extended
+         query protocol from <application>psql</application>.
+        </para>
+
+       </listitem>
+      </varlistentry>
+
       <varlistentry id="app-psql-meta-command-c-lc">
         <term><literal>\c</literal> or <literal>\connect [ -reuse-previous=<replaceable class="parameter">on|off</replaceable> ] [ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] [ <replaceable class="parameter">host</replaceable> ] [ <replaceable class="parameter">port</replaceable> ] | <replaceable class="parameter">conninfo</replaceable> ]</literal></term>
         <listitem>
@@ -1037,6 +1067,35 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
         </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-meta-command-close">
+       <term><literal>\close</literal> <replaceable class="parameter">prepared_statement_name</replaceable></term>
+
+       <listitem>
+        <para>
+         Closes the specified prepared statement. An empty string denotes the
+         unnamed prepared statement. If no prepared statement exists with this
+         name, the operation is a no-op.
+        </para>
+
+        <para>
+         Example:
+<programlisting>
+SELECT $1 \parse stmt1
+\close stmt1
+</programlisting>
+        </para>
+
+        <para>
+         This command causes the extended query protocol to be used,
+         unlike normal <application>psql</application> operation, which
+         uses the simple query protocol. So this command can be useful
+         to test the extended query protocol from
+         <application>psql</application>.
+        </para>
+
+       </listitem>
+      </varlistentry>
+
       <varlistentry id="app-psql-meta-commands-copy">
         <term><literal>\copy { <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column_list</replaceable> ) ] }
         <literal>from</literal>
@@ -2779,6 +2838,37 @@ lo_import 152801
         </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-meta-command-parse">
+        <term><literal>\parse <replaceable class="parameter">statement_name</replaceable></literal></term>
+        <listitem>
+        <para>
+         Creates a prepared statement from the current query buffer, based on
+         the name of a destination prepared-statement object. An empty string
+         denotes the unnamed prepared statement.
+        </para>
+
+        <para>
+         Example:
+<programlisting>
+SELECT $1 \parse stmt1
+</programlisting>
+        </para>
+
+        <para>
+         This command causes the extended query protocol to be used, unlike
+         normal <application>psql</application> operation, which uses the
+         simple query protocol. A
+         <xref linkend="protocol-message-formats-Parse"/>
+         message will be issued by this command so it can be useful to
+         test the extended query protocol from
+         <application>psql</application>. This command affects only the next
+         query executed; all subsequent queries will use the simple query
+         protocol by default.
+        </para>
+
+        </listitem>
+      </varlistentry>
+
       <varlistentry id="app-psql-meta-command-password">
         <term><literal>\password [ <replaceable class="parameter">username</replaceable> ]</literal></term>
         <listitem>
-- 
2.45.2

From cce69afc97d2553ec66859fcac1ef75334ef2b60 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Wed, 24 Jul 2024 14:01:17 +0900
Subject: [PATCH v6 2/2] psql: Refactor status of extended protocol commands

Rather than three separate boolean flags, this groups the commands to be
handled with a single enum, able to control which libpq API to call.
---
 src/bin/psql/command.c  |  8 ++--
 src/bin/psql/common.c   | 87 ++++++++++++++++++++++-------------------
 src/bin/psql/settings.h | 17 +++++---
 3 files changed, 61 insertions(+), 51 deletions(-)

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index f23a7404cc..6a3c58b20f 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -498,7 +498,7 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
 		}
 
 		pset.bind_nparams = nparams;
-		pset.bind_flag = true;
+		pset.send_mode = PSQL_SEND_EXTENDED_QUERY_PARAMS;
 	}
 	else
 		ignore_slash_options(scan_state);
@@ -534,7 +534,7 @@ exec_command_bindx(PsqlScanState scan_state, bool active_branch,
 		else
 		{
 			pset.stmtName = opt;
-			pset.bind_flag = true;
+			pset.send_mode = PSQL_SEND_EXTENDED_QUERY_PREPARED;
 
 			/* set of parameters */
 			while ((opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false)))
@@ -728,7 +728,7 @@ exec_command_close(PsqlScanState scan_state, bool active_branch, const char *cmd
 		else
 		{
 			pset.stmtName = opt;
-			pset.close_flag = true;
+			pset.send_mode = PSQL_SEND_EXTENDED_CLOSE;
 			status = PSQL_CMD_SEND;
 		}
 	}
@@ -2214,7 +2214,7 @@ exec_command_parse(PsqlScanState scan_state, bool active_branch,
 		else
 		{
 			pset.stmtName = opt;
-			pset.parse_flag = true;
+			pset.send_mode = PSQL_SEND_EXTENDED_PARSE;
 			status = PSQL_CMD_SEND;
 		}
 	}
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 1710ee9fc5..7f3b6cbd07 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1274,33 +1274,28 @@ sendquery_cleanup:
 		pset.gsavepopt = NULL;
 	}
 
-	/* clean up after \bind or \bindx */
-	if (pset.bind_flag)
+	/* clean up after extended protocol queries */
+	switch (pset.send_mode)
 	{
-		for (i = 0; i < pset.bind_nparams; i++)
-			free(pset.bind_params[i]);
-		free(pset.bind_params);
-		free(pset.stmtName);
-		pset.bind_params = NULL;
-		pset.stmtName = NULL;
-		pset.bind_flag = false;
-	}
-
-	/* clean up after \parse */
-	if (pset.parse_flag)
-	{
-		free(pset.stmtName);
-		pset.stmtName = NULL;
-		pset.parse_flag = false;
-	}
-
-	/* clean up after \close */
-	if (pset.close_flag)
-	{
-		free(pset.stmtName);
-		pset.stmtName = NULL;
-		pset.close_flag = false;
+		case PSQL_SEND_EXTENDED_CLOSE:	/* \close */
+			free(pset.stmtName);
+			break;
+		case PSQL_SEND_EXTENDED_PARSE:	/* \parse */
+			free(pset.stmtName);
+			break;
+		case PSQL_SEND_EXTENDED_QUERY_PARAMS:	/* \bind */
+		case PSQL_SEND_EXTENDED_QUERY_PREPARED:	/* \bindx */
+			for (i = 0; i < pset.bind_nparams; i++)
+				free(pset.bind_params[i]);
+			free(pset.bind_params);
+			free(pset.stmtName);
+			pset.bind_params = NULL;
+			break;
+		case PSQL_SEND_QUERY:
+			break;
 	}
+	pset.stmtName = NULL;
+	pset.send_mode = PSQL_SEND_QUERY;
 
 	/* reset \gset trigger */
 	if (pset.gset_prefix)
@@ -1487,22 +1482,32 @@ ExecQueryAndProcessResults(const char *query,
 	else
 		INSTR_TIME_SET_ZERO(before);
 
-	if (pset.bind_flag && pset.stmtName == NULL)	/* \bind */
-		success = PQsendQueryParams(pset.db, query,
-									pset.bind_nparams, NULL,
-									(const char *const *) pset.bind_params,
-									NULL, NULL, 0);
-	else if (pset.bind_flag && pset.stmtName != NULL)	/* \bindx */
-		success = PQsendQueryPrepared(pset.db, pset.stmtName,
-									  pset.bind_nparams,
-									  (const char *const *) pset.bind_params,
-									  NULL, NULL, 0);
-	else if (pset.close_flag)	/* \close */
-		success = PQsendClosePrepared(pset.db, pset.stmtName);
-	else if (pset.parse_flag)	/* \parse */
-		success = PQsendPrepare(pset.db, pset.stmtName, query, 0, NULL);
-	else
-		success = PQsendQuery(pset.db, query);
+	switch (pset.send_mode)
+	{
+		case PSQL_SEND_EXTENDED_CLOSE:
+			success = PQsendClosePrepared(pset.db, pset.stmtName);
+			break;
+		case PSQL_SEND_EXTENDED_PARSE:
+			success = PQsendPrepare(pset.db, pset.stmtName, query, 0, NULL);
+			break;
+		case PSQL_SEND_EXTENDED_QUERY_PARAMS:
+			Assert(pset.stmtName == NULL);
+			success = PQsendQueryParams(pset.db, query,
+										pset.bind_nparams, NULL,
+										(const char *const *) pset.bind_params,
+										NULL, NULL, 0);
+			break;
+		case PSQL_SEND_EXTENDED_QUERY_PREPARED:
+			Assert(pset.stmtName != NULL);
+			success = PQsendQueryPrepared(pset.db, pset.stmtName,
+										  pset.bind_nparams,
+										  (const char *const *) pset.bind_params,
+										  NULL, NULL, 0);
+			break;
+		case PSQL_SEND_QUERY:
+			success = PQsendQuery(pset.db, query);
+			break;
+	}
 
 	if (!success)
 	{
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 485fca871c..a22de8ef78 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -62,6 +62,15 @@ typedef enum
 	PSQL_COMP_CASE_LOWER,
 } PSQL_COMP_CASE;
 
+typedef enum
+{
+	PSQL_SEND_QUERY,
+	PSQL_SEND_EXTENDED_CLOSE,
+	PSQL_SEND_EXTENDED_PARSE,
+	PSQL_SEND_EXTENDED_QUERY_PARAMS,
+	PSQL_SEND_EXTENDED_QUERY_PREPARED,
+} PSQL_SEND_MODE;
+
 typedef enum
 {
 	hctl_none = 0,
@@ -96,14 +105,10 @@ typedef struct _psqlSettings
 	char	   *gset_prefix;	/* one-shot prefix argument for \gset */
 	bool		gdesc_flag;		/* one-shot request to describe query result */
 	bool		gexec_flag;		/* one-shot request to execute query result */
-	bool		bind_flag;		/* one-shot request to use extended query
-								 * protocol */
+	PSQL_SEND_MODE send_mode;	/* one-shot request to send query with normal
+								 * or extended query protocol */
 	int			bind_nparams;	/* number of parameters */
 	char	  **bind_params;	/* parameters for extended query protocol call */
-	bool		close_flag;		/* one-shot request to close a prepared
-								 * statement using extended query protocol */
-	bool		parse_flag;		/* one-shot request to parse query using
-								 * extended query protocol */
 	char	   *stmtName;		/* prepared statement name used for extended
 								 * query protocol commands */
 	bool		crosstab_flag;	/* one-shot request to crosstab result */
-- 
2.45.2

From 62303eeab20740f8e7bba36d6595b2a02771e5aa Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
Date: Thu, 18 Jan 2024 08:46:33 +0100
Subject: [PATCH v6 1/2] psql: Add support for prepared stmt with extended
 protocol

Currently, only unnamed prepared statement is supported by psql with the
\bind command so it's not possible to test named statement creation and
execution through extended protocol.

This commit introduces three additional commands: \parse, \bindx and
\close.
\parse creates a prepared statement using extended protocol.
\bindx binds and execute an existing prepared statement using extended
protocol.
\close closes an existing prepared statement using extended protocol.
---
 src/bin/psql/command.c             | 128 +++++++++++++++++++++++++++++
 src/bin/psql/common.c              |  36 +++++++-
 src/bin/psql/help.c                |   4 +
 src/bin/psql/settings.h            |   6 ++
 src/bin/psql/tab-complete.c        |   6 +-
 src/test/regress/expected/psql.out |  55 +++++++++++++
 src/test/regress/sql/psql.sql      |  28 ++++++-
 doc/src/sgml/ref/psql-ref.sgml     |  90 ++++++++++++++++++++
 8 files changed, 346 insertions(+), 7 deletions(-)

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 180781ecd0..f23a7404cc 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -64,10 +64,14 @@ static backslashResult exec_command(const char *cmd,
 									PQExpBuffer previous_buf);
 static backslashResult exec_command_a(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_bind(PsqlScanState scan_state, bool active_branch);
+static backslashResult exec_command_bindx(PsqlScanState scan_state, bool active_branch,
+										  const char *cmd);
 static backslashResult exec_command_C(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_connect(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_cd(PsqlScanState scan_state, bool active_branch,
 									   const char *cmd);
+static backslashResult exec_command_close(PsqlScanState scan_state, bool active_branch,
+										  const char *cmd);
 static backslashResult exec_command_conninfo(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_copy(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_copyright(PsqlScanState scan_state, bool active_branch);
@@ -116,6 +120,8 @@ static backslashResult exec_command_lo(PsqlScanState scan_state, bool active_bra
 static backslashResult exec_command_out(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_print(PsqlScanState scan_state, bool active_branch,
 										  PQExpBuffer query_buf, PQExpBuffer previous_buf);
+static backslashResult exec_command_parse(PsqlScanState scan_state, bool active_branch,
+										  const char *cmd);
 static backslashResult exec_command_password(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_prompt(PsqlScanState scan_state, bool active_branch,
 										   const char *cmd);
@@ -312,12 +318,16 @@ exec_command(const char *cmd,
 		status = exec_command_a(scan_state, active_branch);
 	else if (strcmp(cmd, "bind") == 0)
 		status = exec_command_bind(scan_state, active_branch);
+	else if (strcmp(cmd, "bindx") == 0)
+		status = exec_command_bindx(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "C") == 0)
 		status = exec_command_C(scan_state, active_branch);
 	else if (strcmp(cmd, "c") == 0 || strcmp(cmd, "connect") == 0)
 		status = exec_command_connect(scan_state, active_branch);
 	else if (strcmp(cmd, "cd") == 0)
 		status = exec_command_cd(scan_state, active_branch, cmd);
+	else if (strcmp(cmd, "close") == 0)
+		status = exec_command_close(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "conninfo") == 0)
 		status = exec_command_conninfo(scan_state, active_branch);
 	else if (pg_strcasecmp(cmd, "copy") == 0)
@@ -379,6 +389,8 @@ exec_command(const char *cmd,
 	else if (strcmp(cmd, "p") == 0 || strcmp(cmd, "print") == 0)
 		status = exec_command_print(scan_state, active_branch,
 									query_buf, previous_buf);
+	else if (strcmp(cmd, "parse") == 0)
+		status = exec_command_parse(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "password") == 0)
 		status = exec_command_password(scan_state, active_branch);
 	else if (strcmp(cmd, "prompt") == 0)
@@ -472,6 +484,7 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
 		int			nalloc = 0;
 
 		pset.bind_params = NULL;
+		pset.stmtName = NULL;
 
 		while ((opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false)))
 		{
@@ -493,6 +506,56 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
 	return status;
 }
 
+/*
+ * \bindx -- set query parameters for an existing prepared statement
+ */
+static backslashResult
+exec_command_bindx(PsqlScanState scan_state, bool active_branch,
+				   const char *cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		char	   *opt;
+		int			nparams = 0;
+		int			nalloc = 0;
+
+		pset.bind_params = NULL;
+		pset.stmtName = NULL;
+
+		/* get the mandatory prepared statement name */
+		opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false);
+		if (!opt)
+		{
+			pg_log_error("\\%s: missing required argument", cmd);
+			status = PSQL_CMD_ERROR;
+		}
+		else
+		{
+			pset.stmtName = opt;
+			pset.bind_flag = true;
+
+			/* set of parameters */
+			while ((opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false)))
+			{
+				nparams++;
+				if (nparams > nalloc)
+				{
+					nalloc = nalloc ? nalloc * 2 : 1;
+					pset.bind_params = pg_realloc_array(pset.bind_params, char *, nalloc);
+				}
+				pset.bind_params[nparams - 1] = opt;
+			}
+			pset.bind_nparams = nparams;
+		}
+	}
+	else
+		ignore_slash_options(scan_state);
+
+	return status;
+}
+
 /*
  * \C -- override table title (formerly change HTML caption)
  */
@@ -643,6 +706,38 @@ exec_command_cd(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	return success ? PSQL_CMD_SKIP_LINE : PSQL_CMD_ERROR;
 }
 
+/*
+ * \close -- close a previously prepared statement
+ */
+static backslashResult
+exec_command_close(PsqlScanState scan_state, bool active_branch, const char *cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		char	   *opt = psql_scan_slash_option(scan_state,
+												 OT_NORMAL, NULL, false);
+
+		pset.stmtName = NULL;
+		if (!opt)
+		{
+			pg_log_error("\\%s: missing required argument", cmd);
+			status = PSQL_CMD_ERROR;
+		}
+		else
+		{
+			pset.stmtName = opt;
+			pset.close_flag = true;
+			status = PSQL_CMD_SEND;
+		}
+	}
+	else
+		ignore_slash_options(scan_state);
+
+	return status;
+}
+
 /*
  * \conninfo -- display information about the current connection
  */
@@ -2096,6 +2191,39 @@ exec_command_print(PsqlScanState scan_state, bool active_branch,
 	return PSQL_CMD_SKIP_LINE;
 }
 
+/*
+ * \parse -- parse query
+ */
+static backslashResult
+exec_command_parse(PsqlScanState scan_state, bool active_branch,
+				   const char *cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		char	   *opt = psql_scan_slash_option(scan_state,
+												 OT_NORMAL, NULL, false);
+
+		pset.stmtName = NULL;
+		if (!opt)
+		{
+			pg_log_error("\\%s: missing required argument", cmd);
+			status = PSQL_CMD_ERROR;
+		}
+		else
+		{
+			pset.stmtName = opt;
+			pset.parse_flag = true;
+			status = PSQL_CMD_SEND;
+		}
+	}
+	else
+		ignore_slash_options(scan_state);
+
+	return status;
+}
+
 /*
  * \password -- set user password
  */
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index fe8e049c4c..1710ee9fc5 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1274,16 +1274,34 @@ sendquery_cleanup:
 		pset.gsavepopt = NULL;
 	}
 
-	/* clean up after \bind */
+	/* clean up after \bind or \bindx */
 	if (pset.bind_flag)
 	{
 		for (i = 0; i < pset.bind_nparams; i++)
 			free(pset.bind_params[i]);
 		free(pset.bind_params);
+		free(pset.stmtName);
 		pset.bind_params = NULL;
+		pset.stmtName = NULL;
 		pset.bind_flag = false;
 	}
 
+	/* clean up after \parse */
+	if (pset.parse_flag)
+	{
+		free(pset.stmtName);
+		pset.stmtName = NULL;
+		pset.parse_flag = false;
+	}
+
+	/* clean up after \close */
+	if (pset.close_flag)
+	{
+		free(pset.stmtName);
+		pset.stmtName = NULL;
+		pset.close_flag = false;
+	}
+
 	/* reset \gset trigger */
 	if (pset.gset_prefix)
 	{
@@ -1469,8 +1487,20 @@ ExecQueryAndProcessResults(const char *query,
 	else
 		INSTR_TIME_SET_ZERO(before);
 
-	if (pset.bind_flag)
-		success = PQsendQueryParams(pset.db, query, pset.bind_nparams, NULL, (const char *const *) pset.bind_params, NULL, NULL, 0);
+	if (pset.bind_flag && pset.stmtName == NULL)	/* \bind */
+		success = PQsendQueryParams(pset.db, query,
+									pset.bind_nparams, NULL,
+									(const char *const *) pset.bind_params,
+									NULL, NULL, 0);
+	else if (pset.bind_flag && pset.stmtName != NULL)	/* \bindx */
+		success = PQsendQueryPrepared(pset.db, pset.stmtName,
+									  pset.bind_nparams,
+									  (const char *const *) pset.bind_params,
+									  NULL, NULL, 0);
+	else if (pset.close_flag)	/* \close */
+		success = PQsendClosePrepared(pset.db, pset.stmtName);
+	else if (pset.parse_flag)	/* \parse */
+		success = PQsendPrepare(pset.db, pset.stmtName, query, 0, NULL);
 	else
 		success = PQsendQuery(pset.db, query);
 
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 6f58a11074..b1f167d1dd 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -165,6 +165,9 @@ slashUsage(unsigned short int pager)
 
 	HELP0("General\n");
 	HELP0("  \\bind [PARAM]...       set query parameters\n");
+	HELP0("  \\bindx STMT_NAME [PARAM]...\n"
+		  "                         set query parameters for an existing prepared statement\n");
+	HELP0("  \\close STMT_NAME       close an existing prepared statement\n");
 	HELP0("  \\copyright             show PostgreSQL usage and distribution terms\n");
 	HELP0("  \\crosstabview [COLUMNS] execute query and display result in crosstab\n");
 	HELP0("  \\errverbose            show most recent error message at maximum verbosity\n");
@@ -312,6 +315,7 @@ slashUsage(unsigned short int pager)
 			  "                         connect to new database (currently no connection)\n");
 	HELP0("  \\conninfo              display information about current connection\n");
 	HELP0("  \\encoding [ENCODING]   show or set client encoding\n");
+	HELP0("  \\parse STMT_NAME       create a prepared statement\n");
 	HELP0("  \\password [USERNAME]   securely change the password for a user\n");
 	HELP0("\n");
 
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 505f99d8e4..485fca871c 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -100,6 +100,12 @@ typedef struct _psqlSettings
 								 * protocol */
 	int			bind_nparams;	/* number of parameters */
 	char	  **bind_params;	/* parameters for extended query protocol call */
+	bool		close_flag;		/* one-shot request to close a prepared
+								 * statement using extended query protocol */
+	bool		parse_flag;		/* one-shot request to parse query using
+								 * extended query protocol */
+	char	   *stmtName;		/* prepared statement name used for extended
+								 * query protocol commands */
 	bool		crosstab_flag;	/* one-shot request to crosstab result */
 	char	   *ctv_args[4];	/* \crosstabview arguments */
 
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index d453e224d9..6bf231cea6 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1713,8 +1713,8 @@ psql_completion(const char *text, int start, int end)
 	/* psql's backslash commands. */
 	static const char *const backslash_commands[] = {
 		"\\a",
-		"\\bind",
-		"\\connect", "\\conninfo", "\\C", "\\cd", "\\copy",
+		"\\bind", "\\bindx",
+		"\\connect", "\\conninfo", "\\C", "\\cd", "\\close", "\\copy",
 		"\\copyright", "\\crosstabview",
 		"\\d", "\\da", "\\dA", "\\dAc", "\\dAf", "\\dAo", "\\dAp",
 		"\\db", "\\dc", "\\dconfig", "\\dC", "\\dd", "\\ddp", "\\dD",
@@ -1731,7 +1731,7 @@ psql_completion(const char *text, int start, int end)
 		"\\if", "\\include", "\\include_relative", "\\ir",
 		"\\list", "\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
 		"\\out",
-		"\\password", "\\print", "\\prompt", "\\pset",
+		"\\parse", "\\password", "\\print", "\\prompt", "\\pset",
 		"\\qecho", "\\quit",
 		"\\reset",
 		"\\s", "\\set", "\\setenv", "\\sf", "\\sv",
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 3bbe4c5f97..79e8e5f8cd 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -98,6 +98,53 @@ two | 2
    1 |   2
 (1 row)
 
+-- \parse (extended query protocol)
+\parse
+\parse: missing required argument
+SELECT 1 \parse ''
+SELECT 2 \parse stmt1
+SELECT $1 \parse stmt2
+SELECT $1, $2 \parse stmt3
+-- \bindx (extended query protocol)
+\bindx
+\bindx: missing required argument
+\bindx '' \g
+ ?column? 
+----------
+        1
+(1 row)
+
+\bindx stmt1 \g
+ ?column? 
+----------
+        2
+(1 row)
+
+\bindx stmt2 'foo' \g
+ ?column? 
+----------
+ foo
+(1 row)
+
+\bindx stmt3 'foo' 'bar' \g
+ ?column? | ?column? 
+----------+----------
+ foo      | bar
+(1 row)
+
+-- \close (extended query protocol)
+\close
+\close: missing required argument
+\close ''
+\close stmt2
+\close stmt2
+SELECT name, statement FROM pg_prepared_statements ORDER BY name;
+ name  |   statement    
+-------+----------------
+ stmt1 | SELECT 2 
+ stmt3 | SELECT $1, $2 
+(2 rows)
+
 -- \bind (extended query protocol)
 SELECT 1 \bind \g
  ?column? 
@@ -129,6 +176,11 @@ ERROR:  cannot insert multiple commands into a prepared statement
 -- bind error
 SELECT $1, $2 \bind 'foo' \g
 ERROR:  bind message supplies 1 parameters, but prepared statement "" requires 2
+-- bindx error
+\bindx stmt2 'baz' \g
+ERROR:  prepared statement "stmt2" does not exist
+\bindx stmt3 'baz' \g
+ERROR:  bind message supplies 1 parameters, but prepared statement "stmt3" requires 2
 -- \gset
 select 10 as test01, 20 as test02, 'Hello' as test03 \gset pref01_
 \echo :pref01_test01 :pref01_test02 :pref01_test03
@@ -4507,9 +4559,11 @@ bar 'bar' "bar"
 	\pset fieldsep | `nosuchcommand` :foo :'foo' :"foo"
 	\a
 	SELECT $1 \bind 1 \g
+	\bindx stmt1 1 2 \g
 	\C arg1
 	\c arg1 arg2 arg3 arg4
 	\cd arg1
+	\close stmt1
 	\conninfo
 	\copy arg1 arg2 arg3 arg4 arg5 arg6
 	\copyright
@@ -4538,6 +4592,7 @@ invalid command \lo
 	\lo_list
 	\o arg1
 	\p
+	SELECT 1 \parse
 	\password arg1
 	\prompt arg1 arg2
 	\pset arg1 arg2
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 3b3c6f6e29..da233109a0 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -45,8 +45,28 @@ SELECT 1 as one, 2 as two \g (format=csv csv_fieldsep='\t')
 SELECT 1 as one, 2 as two \gx (title='foo bar')
 \g
 
--- \bind (extended query protocol)
+-- \parse (extended query protocol)
+\parse
+SELECT 1 \parse ''
+SELECT 2 \parse stmt1
+SELECT $1 \parse stmt2
+SELECT $1, $2 \parse stmt3
 
+-- \bindx (extended query protocol)
+\bindx
+\bindx '' \g
+\bindx stmt1 \g
+\bindx stmt2 'foo' \g
+\bindx stmt3 'foo' 'bar' \g
+
+-- \close (extended query protocol)
+\close
+\close ''
+\close stmt2
+\close stmt2
+SELECT name, statement FROM pg_prepared_statements ORDER BY name;
+
+-- \bind (extended query protocol)
 SELECT 1 \bind \g
 SELECT $1 \bind 'foo' \g
 SELECT $1, $2 \bind 'foo' 'bar' \g
@@ -58,6 +78,9 @@ SELECT foo \bind \g
 SELECT 1 \; SELECT 2 \bind \g
 -- bind error
 SELECT $1, $2 \bind 'foo' \g
+-- bindx error
+\bindx stmt2 'baz' \g
+\bindx stmt3 'baz' \g
 
 -- \gset
 
@@ -990,9 +1013,11 @@ select \if false \\ (bogus \else \\ 42 \endif \\ forty_two;
 	\pset fieldsep | `nosuchcommand` :foo :'foo' :"foo"
 	\a
 	SELECT $1 \bind 1 \g
+	\bindx stmt1 1 2 \g
 	\C arg1
 	\c arg1 arg2 arg3 arg4
 	\cd arg1
+	\close stmt1
 	\conninfo
 	\copy arg1 arg2 arg3 arg4 arg5 arg6
 	\copyright
@@ -1020,6 +1045,7 @@ select \if false \\ (bogus \else \\ 42 \endif \\ forty_two;
 	\lo_list
 	\o arg1
 	\p
+	SELECT 1 \parse
 	\password arg1
 	\prompt arg1 arg2
 	\pset arg1 arg2
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 830306ea1e..dd2f805357 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -916,6 +916,36 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
        </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-meta-command-bindx">
+       <term><literal>\bindx</literal> <replaceable class="parameter">statement_name</replaceable> [ <replaceable class="parameter">parameter</replaceable> ] ... </term>
+
+       <listitem>
+        <para>
+         <literal>\bindx</literal> is equivalent to <literal>\bind</literal>,
+         except that it takes the name of an existing prepared statement as
+         first parameter. An empty string denotes the unnamed prepared
+         statement.
+        </para>
+
+        <para>
+         Example:
+<programlisting>
+INSERT INTO tbls1 VALUES ($1, $2) \parse stmt1
+\bindx stmt1 'first value' 'second value' \g
+</programlisting>
+        </para>
+
+        <para>
+         This command causes the extended query protocol (see
+         <xref linkend="protocol-query-concepts"/>) to be used, unlike normal
+         <application>psql</application> operation, which uses the simple
+         query protocol. So this command can be useful to test the extended
+         query protocol from <application>psql</application>.
+        </para>
+
+       </listitem>
+      </varlistentry>
+
       <varlistentry id="app-psql-meta-command-c-lc">
         <term><literal>\c</literal> or <literal>\connect [ -reuse-previous=<replaceable class="parameter">on|off</replaceable> ] [ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] [ <replaceable class="parameter">host</replaceable> ] [ <replaceable class="parameter">port</replaceable> ] | <replaceable class="parameter">conninfo</replaceable> ]</literal></term>
         <listitem>
@@ -1037,6 +1067,35 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
         </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-meta-command-close">
+       <term><literal>\close</literal> <replaceable class="parameter">prepared_statement_name</replaceable></term>
+
+       <listitem>
+        <para>
+         Closes the specified prepared statement. An empty string denotes the
+         unnamed prepared statement. If no prepared statement exists with this
+         name, the operation is a no-op.
+        </para>
+
+        <para>
+         Example:
+<programlisting>
+SELECT $1 \parse stmt1
+\close stmt1
+</programlisting>
+        </para>
+
+        <para>
+         This command causes the extended query protocol to be used,
+         unlike normal <application>psql</application> operation, which
+         uses the simple query protocol. So this command can be useful
+         to test the extended query protocol from
+         <application>psql</application>.
+        </para>
+
+       </listitem>
+      </varlistentry>
+
       <varlistentry id="app-psql-meta-commands-copy">
         <term><literal>\copy { <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column_list</replaceable> ) ] }
         <literal>from</literal>
@@ -2779,6 +2838,37 @@ lo_import 152801
         </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-meta-command-parse">
+        <term><literal>\parse <replaceable class="parameter">statement_name</replaceable></literal></term>
+        <listitem>
+        <para>
+         Creates a prepared statement from the current query buffer, based on
+         the name of a destination prepared-statement object. An empty string
+         denotes the unnamed prepared statement.
+        </para>
+
+        <para>
+         Example:
+<programlisting>
+SELECT $1 \parse stmt1
+</programlisting>
+        </para>
+
+        <para>
+         This command causes the extended query protocol to be used, unlike
+         normal <application>psql</application> operation, which uses the
+         simple query protocol. A
+         <xref linkend="protocol-message-formats-Parse"/>
+         message will be issued by this command so it can be useful to
+         test the extended query protocol from
+         <application>psql</application>. This command affects only the next
+         query executed; all subsequent queries will use the simple query
+         protocol by default.
+        </para>
+
+        </listitem>
+      </varlistentry>
+
       <varlistentry id="app-psql-meta-command-password">
         <term><literal>\password [ <replaceable class="parameter">username</replaceable> ]</literal></term>
         <listitem>
-- 
2.45.2

From cce69afc97d2553ec66859fcac1ef75334ef2b60 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Wed, 24 Jul 2024 14:01:17 +0900
Subject: [PATCH v6 2/2] psql: Refactor status of extended protocol commands

Rather than three separate boolean flags, this groups the commands to be
handled with a single enum, able to control which libpq API to call.
---
 src/bin/psql/command.c  |  8 ++--
 src/bin/psql/common.c   | 87 ++++++++++++++++++++++-------------------
 src/bin/psql/settings.h | 17 +++++---
 3 files changed, 61 insertions(+), 51 deletions(-)

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index f23a7404cc..6a3c58b20f 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -498,7 +498,7 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
 		}
 
 		pset.bind_nparams = nparams;
-		pset.bind_flag = true;
+		pset.send_mode = PSQL_SEND_EXTENDED_QUERY_PARAMS;
 	}
 	else
 		ignore_slash_options(scan_state);
@@ -534,7 +534,7 @@ exec_command_bindx(PsqlScanState scan_state, bool active_branch,
 		else
 		{
 			pset.stmtName = opt;
-			pset.bind_flag = true;
+			pset.send_mode = PSQL_SEND_EXTENDED_QUERY_PREPARED;
 
 			/* set of parameters */
 			while ((opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false)))
@@ -728,7 +728,7 @@ exec_command_close(PsqlScanState scan_state, bool active_branch, const char *cmd
 		else
 		{
 			pset.stmtName = opt;
-			pset.close_flag = true;
+			pset.send_mode = PSQL_SEND_EXTENDED_CLOSE;
 			status = PSQL_CMD_SEND;
 		}
 	}
@@ -2214,7 +2214,7 @@ exec_command_parse(PsqlScanState scan_state, bool active_branch,
 		else
 		{
 			pset.stmtName = opt;
-			pset.parse_flag = true;
+			pset.send_mode = PSQL_SEND_EXTENDED_PARSE;
 			status = PSQL_CMD_SEND;
 		}
 	}
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 1710ee9fc5..7f3b6cbd07 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1274,33 +1274,28 @@ sendquery_cleanup:
 		pset.gsavepopt = NULL;
 	}
 
-	/* clean up after \bind or \bindx */
-	if (pset.bind_flag)
+	/* clean up after extended protocol queries */
+	switch (pset.send_mode)
 	{
-		for (i = 0; i < pset.bind_nparams; i++)
-			free(pset.bind_params[i]);
-		free(pset.bind_params);
-		free(pset.stmtName);
-		pset.bind_params = NULL;
-		pset.stmtName = NULL;
-		pset.bind_flag = false;
-	}
-
-	/* clean up after \parse */
-	if (pset.parse_flag)
-	{
-		free(pset.stmtName);
-		pset.stmtName = NULL;
-		pset.parse_flag = false;
-	}
-
-	/* clean up after \close */
-	if (pset.close_flag)
-	{
-		free(pset.stmtName);
-		pset.stmtName = NULL;
-		pset.close_flag = false;
+		case PSQL_SEND_EXTENDED_CLOSE:	/* \close */
+			free(pset.stmtName);
+			break;
+		case PSQL_SEND_EXTENDED_PARSE:	/* \parse */
+			free(pset.stmtName);
+			break;
+		case PSQL_SEND_EXTENDED_QUERY_PARAMS:	/* \bind */
+		case PSQL_SEND_EXTENDED_QUERY_PREPARED:	/* \bindx */
+			for (i = 0; i < pset.bind_nparams; i++)
+				free(pset.bind_params[i]);
+			free(pset.bind_params);
+			free(pset.stmtName);
+			pset.bind_params = NULL;
+			break;
+		case PSQL_SEND_QUERY:
+			break;
 	}
+	pset.stmtName = NULL;
+	pset.send_mode = PSQL_SEND_QUERY;
 
 	/* reset \gset trigger */
 	if (pset.gset_prefix)
@@ -1487,22 +1482,32 @@ ExecQueryAndProcessResults(const char *query,
 	else
 		INSTR_TIME_SET_ZERO(before);
 
-	if (pset.bind_flag && pset.stmtName == NULL)	/* \bind */
-		success = PQsendQueryParams(pset.db, query,
-									pset.bind_nparams, NULL,
-									(const char *const *) pset.bind_params,
-									NULL, NULL, 0);
-	else if (pset.bind_flag && pset.stmtName != NULL)	/* \bindx */
-		success = PQsendQueryPrepared(pset.db, pset.stmtName,
-									  pset.bind_nparams,
-									  (const char *const *) pset.bind_params,
-									  NULL, NULL, 0);
-	else if (pset.close_flag)	/* \close */
-		success = PQsendClosePrepared(pset.db, pset.stmtName);
-	else if (pset.parse_flag)	/* \parse */
-		success = PQsendPrepare(pset.db, pset.stmtName, query, 0, NULL);
-	else
-		success = PQsendQuery(pset.db, query);
+	switch (pset.send_mode)
+	{
+		case PSQL_SEND_EXTENDED_CLOSE:
+			success = PQsendClosePrepared(pset.db, pset.stmtName);
+			break;
+		case PSQL_SEND_EXTENDED_PARSE:
+			success = PQsendPrepare(pset.db, pset.stmtName, query, 0, NULL);
+			break;
+		case PSQL_SEND_EXTENDED_QUERY_PARAMS:
+			Assert(pset.stmtName == NULL);
+			success = PQsendQueryParams(pset.db, query,
+										pset.bind_nparams, NULL,
+										(const char *const *) pset.bind_params,
+										NULL, NULL, 0);
+			break;
+		case PSQL_SEND_EXTENDED_QUERY_PREPARED:
+			Assert(pset.stmtName != NULL);
+			success = PQsendQueryPrepared(pset.db, pset.stmtName,
+										  pset.bind_nparams,
+										  (const char *const *) pset.bind_params,
+										  NULL, NULL, 0);
+			break;
+		case PSQL_SEND_QUERY:
+			success = PQsendQuery(pset.db, query);
+			break;
+	}
 
 	if (!success)
 	{
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 485fca871c..a22de8ef78 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -62,6 +62,15 @@ typedef enum
 	PSQL_COMP_CASE_LOWER,
 } PSQL_COMP_CASE;
 
+typedef enum
+{
+	PSQL_SEND_QUERY,
+	PSQL_SEND_EXTENDED_CLOSE,
+	PSQL_SEND_EXTENDED_PARSE,
+	PSQL_SEND_EXTENDED_QUERY_PARAMS,
+	PSQL_SEND_EXTENDED_QUERY_PREPARED,
+} PSQL_SEND_MODE;
+
 typedef enum
 {
 	hctl_none = 0,
@@ -96,14 +105,10 @@ typedef struct _psqlSettings
 	char	   *gset_prefix;	/* one-shot prefix argument for \gset */
 	bool		gdesc_flag;		/* one-shot request to describe query result */
 	bool		gexec_flag;		/* one-shot request to execute query result */
-	bool		bind_flag;		/* one-shot request to use extended query
-								 * protocol */
+	PSQL_SEND_MODE send_mode;	/* one-shot request to send query with normal
+								 * or extended query protocol */
 	int			bind_nparams;	/* number of parameters */
 	char	  **bind_params;	/* parameters for extended query protocol call */
-	bool		close_flag;		/* one-shot request to close a prepared
-								 * statement using extended query protocol */
-	bool		parse_flag;		/* one-shot request to parse query using
-								 * extended query protocol */
 	char	   *stmtName;		/* prepared statement name used for extended
 								 * query protocol commands */
 	bool		crosstab_flag;	/* one-shot request to crosstab result */
-- 
2.45.2

From b34d74381368312f9196c819e9abcfcf07d35a86 Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
Date: Thu, 18 Jan 2024 08:46:33 +0100
Subject: psql: Add support for prepared stmt with extended protocol

Currently, only unnamed prepared statement is supported by psql with the
\bind command so it's not possible to test named statement creation and
execution through extended protocol.

This commit introduces three additional commands: \parse, \bind_named
and \close.

\parse creates a prepared statement using extended protocol.
\bind_named binds and execute an existing prepared statement using
extended protocol.
\close closes an existing prepared statement using extended protocol.
---
 doc/src/sgml/ref/psql-ref.sgml     |  90 ++++++++++++++++++++
 src/bin/psql/command.c             | 128 +++++++++++++++++++++++++++++
 src/bin/psql/common.c              |  36 +++++++-
 src/bin/psql/help.c                |   4 +
 src/bin/psql/settings.h            |   6 ++
 src/bin/psql/tab-complete.c        |   6 +-
 src/test/regress/expected/psql.out |  55 +++++++++++++
 src/test/regress/sql/psql.sql      |  28 ++++++-
 8 files changed, 346 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 07419a3b92e..3fd9959ed16 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -917,6 +917,36 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
        </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-meta-command-bind-named">
+       <term><literal>\bind_named</literal> <replaceable class="parameter">statement_name</replaceable> [ <replaceable class="parameter">parameter</replaceable> ] ... </term>
+
+       <listitem>
+        <para>
+         <literal>\bind_named</literal> is equivalent to <literal>\bind</literal>,
+         except that it takes the name of an existing prepared statement as
+         first parameter. An empty string denotes the unnamed prepared
+         statement.
+        </para>
+
+        <para>
+         Example:
+<programlisting>
+INSERT INTO tbls1 VALUES ($1, $2) \parse stmt1
+\bind_named stmt1 'first value' 'second value' \g
+</programlisting>
+        </para>
+
+        <para>
+         This command causes the extended query protocol (see
+         <xref linkend="protocol-query-concepts"/>) to be used, unlike normal
+         <application>psql</application> operation, which uses the simple
+         query protocol. So this command can be useful to test the extended
+         query protocol from <application>psql</application>.
+        </para>
+
+       </listitem>
+      </varlistentry>
+
       <varlistentry id="app-psql-meta-command-c-lc">
         <term><literal>\c</literal> or <literal>\connect [ -reuse-previous=<replaceable class="parameter">on|off</replaceable> ] [ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] [ <replaceable class="parameter">host</replaceable> ] [ <replaceable class="parameter">port</replaceable> ] | <replaceable class="parameter">conninfo</replaceable> ]</literal></term>
         <listitem>
@@ -1038,6 +1068,35 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
         </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-meta-command-close">
+       <term><literal>\close</literal> <replaceable class="parameter">prepared_statement_name</replaceable></term>
+
+       <listitem>
+        <para>
+         Closes the specified prepared statement. An empty string denotes the
+         unnamed prepared statement. If no prepared statement exists with this
+         name, the operation is a no-op.
+        </para>
+
+        <para>
+         Example:
+<programlisting>
+SELECT $1 \parse stmt1
+\close stmt1
+</programlisting>
+        </para>
+
+        <para>
+         This command causes the extended query protocol to be used,
+         unlike normal <application>psql</application> operation, which
+         uses the simple query protocol. So this command can be useful
+         to test the extended query protocol from
+         <application>psql</application>.
+        </para>
+
+       </listitem>
+      </varlistentry>
+
       <varlistentry id="app-psql-meta-commands-copy">
         <term><literal>\copy { <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column_list</replaceable> ) ] }
         <literal>from</literal>
@@ -2780,6 +2839,37 @@ lo_import 152801
         </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-meta-command-parse">
+        <term><literal>\parse <replaceable class="parameter">statement_name</replaceable></literal></term>
+        <listitem>
+        <para>
+         Creates a prepared statement from the current query buffer, based on
+         the name of a destination prepared-statement object. An empty string
+         denotes the unnamed prepared statement.
+        </para>
+
+        <para>
+         Example:
+<programlisting>
+SELECT $1 \parse stmt1
+</programlisting>
+        </para>
+
+        <para>
+         This command causes the extended query protocol to be used, unlike
+         normal <application>psql</application> operation, which uses the
+         simple query protocol. A
+         <xref linkend="protocol-message-formats-Parse"/>
+         message will be issued by this command so it can be useful to
+         test the extended query protocol from
+         <application>psql</application>. This command affects only the next
+         query executed; all subsequent queries will use the simple query
+         protocol by default.
+        </para>
+
+        </listitem>
+      </varlistentry>
+
       <varlistentry id="app-psql-meta-command-password">
         <term><literal>\password [ <replaceable class="parameter">username</replaceable> ]</literal></term>
         <listitem>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 180781ecd05..f1731e527c2 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -64,10 +64,14 @@ static backslashResult exec_command(const char *cmd,
 									PQExpBuffer previous_buf);
 static backslashResult exec_command_a(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_bind(PsqlScanState scan_state, bool active_branch);
+static backslashResult exec_command_bind_named(PsqlScanState scan_state, bool active_branch,
+										  const char *cmd);
 static backslashResult exec_command_C(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_connect(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_cd(PsqlScanState scan_state, bool active_branch,
 									   const char *cmd);
+static backslashResult exec_command_close(PsqlScanState scan_state, bool active_branch,
+										  const char *cmd);
 static backslashResult exec_command_conninfo(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_copy(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_copyright(PsqlScanState scan_state, bool active_branch);
@@ -116,6 +120,8 @@ static backslashResult exec_command_lo(PsqlScanState scan_state, bool active_bra
 static backslashResult exec_command_out(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_print(PsqlScanState scan_state, bool active_branch,
 										  PQExpBuffer query_buf, PQExpBuffer previous_buf);
+static backslashResult exec_command_parse(PsqlScanState scan_state, bool active_branch,
+										  const char *cmd);
 static backslashResult exec_command_password(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_prompt(PsqlScanState scan_state, bool active_branch,
 										   const char *cmd);
@@ -312,12 +318,16 @@ exec_command(const char *cmd,
 		status = exec_command_a(scan_state, active_branch);
 	else if (strcmp(cmd, "bind") == 0)
 		status = exec_command_bind(scan_state, active_branch);
+	else if (strcmp(cmd, "bind_named") == 0)
+		status = exec_command_bind_named(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "C") == 0)
 		status = exec_command_C(scan_state, active_branch);
 	else if (strcmp(cmd, "c") == 0 || strcmp(cmd, "connect") == 0)
 		status = exec_command_connect(scan_state, active_branch);
 	else if (strcmp(cmd, "cd") == 0)
 		status = exec_command_cd(scan_state, active_branch, cmd);
+	else if (strcmp(cmd, "close") == 0)
+		status = exec_command_close(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "conninfo") == 0)
 		status = exec_command_conninfo(scan_state, active_branch);
 	else if (pg_strcasecmp(cmd, "copy") == 0)
@@ -379,6 +389,8 @@ exec_command(const char *cmd,
 	else if (strcmp(cmd, "p") == 0 || strcmp(cmd, "print") == 0)
 		status = exec_command_print(scan_state, active_branch,
 									query_buf, previous_buf);
+	else if (strcmp(cmd, "parse") == 0)
+		status = exec_command_parse(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "password") == 0)
 		status = exec_command_password(scan_state, active_branch);
 	else if (strcmp(cmd, "prompt") == 0)
@@ -472,6 +484,7 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
 		int			nalloc = 0;
 
 		pset.bind_params = NULL;
+		pset.stmtName = NULL;
 
 		while ((opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false)))
 		{
@@ -493,6 +506,56 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
 	return status;
 }
 
+/*
+ * \bind_named -- set query parameters for an existing prepared statement
+ */
+static backslashResult
+exec_command_bind_named(PsqlScanState scan_state, bool active_branch,
+				   const char *cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		char	   *opt;
+		int			nparams = 0;
+		int			nalloc = 0;
+
+		pset.bind_params = NULL;
+		pset.stmtName = NULL;
+
+		/* get the mandatory prepared statement name */
+		opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false);
+		if (!opt)
+		{
+			pg_log_error("\\%s: missing required argument", cmd);
+			status = PSQL_CMD_ERROR;
+		}
+		else
+		{
+			pset.stmtName = opt;
+			pset.bind_flag = true;
+
+			/* set of parameters */
+			while ((opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false)))
+			{
+				nparams++;
+				if (nparams > nalloc)
+				{
+					nalloc = nalloc ? nalloc * 2 : 1;
+					pset.bind_params = pg_realloc_array(pset.bind_params, char *, nalloc);
+				}
+				pset.bind_params[nparams - 1] = opt;
+			}
+			pset.bind_nparams = nparams;
+		}
+	}
+	else
+		ignore_slash_options(scan_state);
+
+	return status;
+}
+
 /*
  * \C -- override table title (formerly change HTML caption)
  */
@@ -643,6 +706,38 @@ exec_command_cd(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	return success ? PSQL_CMD_SKIP_LINE : PSQL_CMD_ERROR;
 }
 
+/*
+ * \close -- close a previously prepared statement
+ */
+static backslashResult
+exec_command_close(PsqlScanState scan_state, bool active_branch, const char *cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		char	   *opt = psql_scan_slash_option(scan_state,
+												 OT_NORMAL, NULL, false);
+
+		pset.stmtName = NULL;
+		if (!opt)
+		{
+			pg_log_error("\\%s: missing required argument", cmd);
+			status = PSQL_CMD_ERROR;
+		}
+		else
+		{
+			pset.stmtName = opt;
+			pset.close_flag = true;
+			status = PSQL_CMD_SEND;
+		}
+	}
+	else
+		ignore_slash_options(scan_state);
+
+	return status;
+}
+
 /*
  * \conninfo -- display information about the current connection
  */
@@ -2096,6 +2191,39 @@ exec_command_print(PsqlScanState scan_state, bool active_branch,
 	return PSQL_CMD_SKIP_LINE;
 }
 
+/*
+ * \parse -- parse query
+ */
+static backslashResult
+exec_command_parse(PsqlScanState scan_state, bool active_branch,
+				   const char *cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		char	   *opt = psql_scan_slash_option(scan_state,
+												 OT_NORMAL, NULL, false);
+
+		pset.stmtName = NULL;
+		if (!opt)
+		{
+			pg_log_error("\\%s: missing required argument", cmd);
+			status = PSQL_CMD_ERROR;
+		}
+		else
+		{
+			pset.stmtName = opt;
+			pset.parse_flag = true;
+			status = PSQL_CMD_SEND;
+		}
+	}
+	else
+		ignore_slash_options(scan_state);
+
+	return status;
+}
+
 /*
  * \password -- set user password
  */
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index fe8e049c4c1..e6e57c63dbe 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1274,16 +1274,34 @@ sendquery_cleanup:
 		pset.gsavepopt = NULL;
 	}
 
-	/* clean up after \bind */
+	/* clean up after \bind or \bind_named */
 	if (pset.bind_flag)
 	{
 		for (i = 0; i < pset.bind_nparams; i++)
 			free(pset.bind_params[i]);
 		free(pset.bind_params);
+		free(pset.stmtName);
 		pset.bind_params = NULL;
+		pset.stmtName = NULL;
 		pset.bind_flag = false;
 	}
 
+	/* clean up after \parse */
+	if (pset.parse_flag)
+	{
+		free(pset.stmtName);
+		pset.stmtName = NULL;
+		pset.parse_flag = false;
+	}
+
+	/* clean up after \close */
+	if (pset.close_flag)
+	{
+		free(pset.stmtName);
+		pset.stmtName = NULL;
+		pset.close_flag = false;
+	}
+
 	/* reset \gset trigger */
 	if (pset.gset_prefix)
 	{
@@ -1469,8 +1487,20 @@ ExecQueryAndProcessResults(const char *query,
 	else
 		INSTR_TIME_SET_ZERO(before);
 
-	if (pset.bind_flag)
-		success = PQsendQueryParams(pset.db, query, pset.bind_nparams, NULL, (const char *const *) pset.bind_params, NULL, NULL, 0);
+	if (pset.bind_flag && pset.stmtName == NULL)	/* \bind */
+		success = PQsendQueryParams(pset.db, query,
+									pset.bind_nparams, NULL,
+									(const char *const *) pset.bind_params,
+									NULL, NULL, 0);
+	else if (pset.bind_flag && pset.stmtName != NULL)	/* \bind_named */
+		success = PQsendQueryPrepared(pset.db, pset.stmtName,
+									  pset.bind_nparams,
+									  (const char *const *) pset.bind_params,
+									  NULL, NULL, 0);
+	else if (pset.close_flag)	/* \close */
+		success = PQsendClosePrepared(pset.db, pset.stmtName);
+	else if (pset.parse_flag)	/* \parse */
+		success = PQsendPrepare(pset.db, pset.stmtName, query, 0, NULL);
 	else
 		success = PQsendQuery(pset.db, query);
 
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 6f58a110748..19d20c58781 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -165,6 +165,9 @@ slashUsage(unsigned short int pager)
 
 	HELP0("General\n");
 	HELP0("  \\bind [PARAM]...       set query parameters\n");
+	HELP0("  \\bind_named STMT_NAME [PARAM]...\n"
+		  "                         set query parameters for an existing prepared statement\n");
+	HELP0("  \\close STMT_NAME       close an existing prepared statement\n");
 	HELP0("  \\copyright             show PostgreSQL usage and distribution terms\n");
 	HELP0("  \\crosstabview [COLUMNS] execute query and display result in crosstab\n");
 	HELP0("  \\errverbose            show most recent error message at maximum verbosity\n");
@@ -312,6 +315,7 @@ slashUsage(unsigned short int pager)
 			  "                         connect to new database (currently no connection)\n");
 	HELP0("  \\conninfo              display information about current connection\n");
 	HELP0("  \\encoding [ENCODING]   show or set client encoding\n");
+	HELP0("  \\parse STMT_NAME       create a prepared statement\n");
 	HELP0("  \\password [USERNAME]   securely change the password for a user\n");
 	HELP0("\n");
 
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 505f99d8e47..485fca871c4 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -100,6 +100,12 @@ typedef struct _psqlSettings
 								 * protocol */
 	int			bind_nparams;	/* number of parameters */
 	char	  **bind_params;	/* parameters for extended query protocol call */
+	bool		close_flag;		/* one-shot request to close a prepared
+								 * statement using extended query protocol */
+	bool		parse_flag;		/* one-shot request to parse query using
+								 * extended query protocol */
+	char	   *stmtName;		/* prepared statement name used for extended
+								 * query protocol commands */
 	bool		crosstab_flag;	/* one-shot request to crosstab result */
 	char	   *ctv_args[4];	/* \crosstabview arguments */
 
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 024469474da..0d25981253e 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1713,8 +1713,8 @@ psql_completion(const char *text, int start, int end)
 	/* psql's backslash commands. */
 	static const char *const backslash_commands[] = {
 		"\\a",
-		"\\bind",
-		"\\connect", "\\conninfo", "\\C", "\\cd", "\\copy",
+		"\\bind", "\\bind_named",
+		"\\connect", "\\conninfo", "\\C", "\\cd", "\\close", "\\copy",
 		"\\copyright", "\\crosstabview",
 		"\\d", "\\da", "\\dA", "\\dAc", "\\dAf", "\\dAo", "\\dAp",
 		"\\db", "\\dc", "\\dconfig", "\\dC", "\\dd", "\\ddp", "\\dD",
@@ -1731,7 +1731,7 @@ psql_completion(const char *text, int start, int end)
 		"\\if", "\\include", "\\include_relative", "\\ir",
 		"\\list", "\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
 		"\\out",
-		"\\password", "\\print", "\\prompt", "\\pset",
+		"\\parse", "\\password", "\\print", "\\prompt", "\\pset",
 		"\\qecho", "\\quit",
 		"\\reset",
 		"\\s", "\\set", "\\setenv", "\\sf", "\\sv",
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 3bbe4c5f974..6aeb7cb9636 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -98,6 +98,53 @@ two | 2
    1 |   2
 (1 row)
 
+-- \parse (extended query protocol)
+\parse
+\parse: missing required argument
+SELECT 1 \parse ''
+SELECT 2 \parse stmt1
+SELECT $1 \parse stmt2
+SELECT $1, $2 \parse stmt3
+-- \bind_named (extended query protocol)
+\bind_named
+\bind_named: missing required argument
+\bind_named '' \g
+ ?column? 
+----------
+        1
+(1 row)
+
+\bind_named stmt1 \g
+ ?column? 
+----------
+        2
+(1 row)
+
+\bind_named stmt2 'foo' \g
+ ?column? 
+----------
+ foo
+(1 row)
+
+\bind_named stmt3 'foo' 'bar' \g
+ ?column? | ?column? 
+----------+----------
+ foo      | bar
+(1 row)
+
+-- \close (extended query protocol)
+\close
+\close: missing required argument
+\close ''
+\close stmt2
+\close stmt2
+SELECT name, statement FROM pg_prepared_statements ORDER BY name;
+ name  |   statement    
+-------+----------------
+ stmt1 | SELECT 2 
+ stmt3 | SELECT $1, $2 
+(2 rows)
+
 -- \bind (extended query protocol)
 SELECT 1 \bind \g
  ?column? 
@@ -129,6 +176,11 @@ ERROR:  cannot insert multiple commands into a prepared statement
 -- bind error
 SELECT $1, $2 \bind 'foo' \g
 ERROR:  bind message supplies 1 parameters, but prepared statement "" requires 2
+-- bind_named error
+\bind_named stmt2 'baz' \g
+ERROR:  prepared statement "stmt2" does not exist
+\bind_named stmt3 'baz' \g
+ERROR:  bind message supplies 1 parameters, but prepared statement "stmt3" requires 2
 -- \gset
 select 10 as test01, 20 as test02, 'Hello' as test03 \gset pref01_
 \echo :pref01_test01 :pref01_test02 :pref01_test03
@@ -4507,9 +4559,11 @@ bar 'bar' "bar"
 	\pset fieldsep | `nosuchcommand` :foo :'foo' :"foo"
 	\a
 	SELECT $1 \bind 1 \g
+	\bind_named stmt1 1 2 \g
 	\C arg1
 	\c arg1 arg2 arg3 arg4
 	\cd arg1
+	\close stmt1
 	\conninfo
 	\copy arg1 arg2 arg3 arg4 arg5 arg6
 	\copyright
@@ -4538,6 +4592,7 @@ invalid command \lo
 	\lo_list
 	\o arg1
 	\p
+	SELECT 1 \parse
 	\password arg1
 	\prompt arg1 arg2
 	\pset arg1 arg2
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 3b3c6f6e294..0a2f8b46922 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -45,8 +45,28 @@ SELECT 1 as one, 2 as two \g (format=csv csv_fieldsep='\t')
 SELECT 1 as one, 2 as two \gx (title='foo bar')
 \g
 
+-- \parse (extended query protocol)
+\parse
+SELECT 1 \parse ''
+SELECT 2 \parse stmt1
+SELECT $1 \parse stmt2
+SELECT $1, $2 \parse stmt3
+
+-- \bind_named (extended query protocol)
+\bind_named
+\bind_named '' \g
+\bind_named stmt1 \g
+\bind_named stmt2 'foo' \g
+\bind_named stmt3 'foo' 'bar' \g
+
+-- \close (extended query protocol)
+\close
+\close ''
+\close stmt2
+\close stmt2
+SELECT name, statement FROM pg_prepared_statements ORDER BY name;
+
 -- \bind (extended query protocol)
-
 SELECT 1 \bind \g
 SELECT $1 \bind 'foo' \g
 SELECT $1, $2 \bind 'foo' 'bar' \g
@@ -58,6 +78,9 @@ SELECT foo \bind \g
 SELECT 1 \; SELECT 2 \bind \g
 -- bind error
 SELECT $1, $2 \bind 'foo' \g
+-- bind_named error
+\bind_named stmt2 'baz' \g
+\bind_named stmt3 'baz' \g
 
 -- \gset
 
@@ -990,9 +1013,11 @@ select \if false \\ (bogus \else \\ 42 \endif \\ forty_two;
 	\pset fieldsep | `nosuchcommand` :foo :'foo' :"foo"
 	\a
 	SELECT $1 \bind 1 \g
+	\bind_named stmt1 1 2 \g
 	\C arg1
 	\c arg1 arg2 arg3 arg4
 	\cd arg1
+	\close stmt1
 	\conninfo
 	\copy arg1 arg2 arg3 arg4 arg5 arg6
 	\copyright
@@ -1020,6 +1045,7 @@ select \if false \\ (bogus \else \\ 42 \endif \\ forty_two;
 	\lo_list
 	\o arg1
 	\p
+	SELECT 1 \parse
 	\password arg1
 	\prompt arg1 arg2
 	\pset arg1 arg2
-- 
2.39.3 (Apple Git-146)

From 3d76fe73c7adf182c0989dc197e38394c8f50f57 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Wed, 24 Jul 2024 14:01:17 +0900
Subject: psql: Refactor status of extended protocol commands

Rather than three separate boolean flags, this groups the commands to be
handled with a single enum, able to control which libpq API to call.
---
 src/bin/psql/command.c  |  8 ++--
 src/bin/psql/common.c   | 89 ++++++++++++++++++++++-------------------
 src/bin/psql/settings.h | 17 +++++---
 3 files changed, 62 insertions(+), 52 deletions(-)

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index f1731e527c2..42eb6756404 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -498,7 +498,7 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
 		}
 
 		pset.bind_nparams = nparams;
-		pset.bind_flag = true;
+		pset.send_mode = PSQL_SEND_EXTENDED_QUERY_PARAMS;
 	}
 	else
 		ignore_slash_options(scan_state);
@@ -534,7 +534,7 @@ exec_command_bind_named(PsqlScanState scan_state, bool active_branch,
 		else
 		{
 			pset.stmtName = opt;
-			pset.bind_flag = true;
+			pset.send_mode = PSQL_SEND_EXTENDED_QUERY_PREPARED;
 
 			/* set of parameters */
 			while ((opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false)))
@@ -728,7 +728,7 @@ exec_command_close(PsqlScanState scan_state, bool active_branch, const char *cmd
 		else
 		{
 			pset.stmtName = opt;
-			pset.close_flag = true;
+			pset.send_mode = PSQL_SEND_EXTENDED_CLOSE;
 			status = PSQL_CMD_SEND;
 		}
 	}
@@ -2214,7 +2214,7 @@ exec_command_parse(PsqlScanState scan_state, bool active_branch,
 		else
 		{
 			pset.stmtName = opt;
-			pset.parse_flag = true;
+			pset.send_mode = PSQL_SEND_EXTENDED_PARSE;
 			status = PSQL_CMD_SEND;
 		}
 	}
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index e6e57c63dbe..6b359871fe6 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1274,33 +1274,28 @@ sendquery_cleanup:
 		pset.gsavepopt = NULL;
 	}
 
-	/* clean up after \bind or \bind_named */
-	if (pset.bind_flag)
+	/* clean up after extended protocol queries */
+	switch (pset.send_mode)
 	{
-		for (i = 0; i < pset.bind_nparams; i++)
-			free(pset.bind_params[i]);
-		free(pset.bind_params);
-		free(pset.stmtName);
-		pset.bind_params = NULL;
-		pset.stmtName = NULL;
-		pset.bind_flag = false;
-	}
-
-	/* clean up after \parse */
-	if (pset.parse_flag)
-	{
-		free(pset.stmtName);
-		pset.stmtName = NULL;
-		pset.parse_flag = false;
-	}
-
-	/* clean up after \close */
-	if (pset.close_flag)
-	{
-		free(pset.stmtName);
-		pset.stmtName = NULL;
-		pset.close_flag = false;
+		case PSQL_SEND_EXTENDED_CLOSE:	/* \close */
+			free(pset.stmtName);
+			break;
+		case PSQL_SEND_EXTENDED_PARSE:	/* \parse */
+			free(pset.stmtName);
+			break;
+		case PSQL_SEND_EXTENDED_QUERY_PARAMS:	/* \bind */
+		case PSQL_SEND_EXTENDED_QUERY_PREPARED:	/* \bind_named */
+			for (i = 0; i < pset.bind_nparams; i++)
+				free(pset.bind_params[i]);
+			free(pset.bind_params);
+			free(pset.stmtName);
+			pset.bind_params = NULL;
+			break;
+		case PSQL_SEND_QUERY:
+			break;
 	}
+	pset.stmtName = NULL;
+	pset.send_mode = PSQL_SEND_QUERY;
 
 	/* reset \gset trigger */
 	if (pset.gset_prefix)
@@ -1474,7 +1469,7 @@ ExecQueryAndProcessResults(const char *query,
 						   const printQueryOpt *opt, FILE *printQueryFout)
 {
 	bool		timing = pset.timing;
-	bool		success;
+	bool		success = false;
 	bool		return_early = false;
 	instr_time	before,
 				after;
@@ -1487,22 +1482,32 @@ ExecQueryAndProcessResults(const char *query,
 	else
 		INSTR_TIME_SET_ZERO(before);
 
-	if (pset.bind_flag && pset.stmtName == NULL)	/* \bind */
-		success = PQsendQueryParams(pset.db, query,
-									pset.bind_nparams, NULL,
-									(const char *const *) pset.bind_params,
-									NULL, NULL, 0);
-	else if (pset.bind_flag && pset.stmtName != NULL)	/* \bind_named */
-		success = PQsendQueryPrepared(pset.db, pset.stmtName,
-									  pset.bind_nparams,
-									  (const char *const *) pset.bind_params,
-									  NULL, NULL, 0);
-	else if (pset.close_flag)	/* \close */
-		success = PQsendClosePrepared(pset.db, pset.stmtName);
-	else if (pset.parse_flag)	/* \parse */
-		success = PQsendPrepare(pset.db, pset.stmtName, query, 0, NULL);
-	else
-		success = PQsendQuery(pset.db, query);
+	switch (pset.send_mode)
+	{
+		case PSQL_SEND_EXTENDED_CLOSE:
+			success = PQsendClosePrepared(pset.db, pset.stmtName);
+			break;
+		case PSQL_SEND_EXTENDED_PARSE:
+			success = PQsendPrepare(pset.db, pset.stmtName, query, 0, NULL);
+			break;
+		case PSQL_SEND_EXTENDED_QUERY_PARAMS:
+			Assert(pset.stmtName == NULL);
+			success = PQsendQueryParams(pset.db, query,
+										pset.bind_nparams, NULL,
+										(const char *const *) pset.bind_params,
+										NULL, NULL, 0);
+			break;
+		case PSQL_SEND_EXTENDED_QUERY_PREPARED:
+			Assert(pset.stmtName != NULL);
+			success = PQsendQueryPrepared(pset.db, pset.stmtName,
+										  pset.bind_nparams,
+										  (const char *const *) pset.bind_params,
+										  NULL, NULL, 0);
+			break;
+		case PSQL_SEND_QUERY:
+			success = PQsendQuery(pset.db, query);
+			break;
+	}
 
 	if (!success)
 	{
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 485fca871c4..a22de8ef78e 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -62,6 +62,15 @@ typedef enum
 	PSQL_COMP_CASE_LOWER,
 } PSQL_COMP_CASE;
 
+typedef enum
+{
+	PSQL_SEND_QUERY,
+	PSQL_SEND_EXTENDED_CLOSE,
+	PSQL_SEND_EXTENDED_PARSE,
+	PSQL_SEND_EXTENDED_QUERY_PARAMS,
+	PSQL_SEND_EXTENDED_QUERY_PREPARED,
+} PSQL_SEND_MODE;
+
 typedef enum
 {
 	hctl_none = 0,
@@ -96,14 +105,10 @@ typedef struct _psqlSettings
 	char	   *gset_prefix;	/* one-shot prefix argument for \gset */
 	bool		gdesc_flag;		/* one-shot request to describe query result */
 	bool		gexec_flag;		/* one-shot request to execute query result */
-	bool		bind_flag;		/* one-shot request to use extended query
-								 * protocol */
+	PSQL_SEND_MODE send_mode;	/* one-shot request to send query with normal
+								 * or extended query protocol */
 	int			bind_nparams;	/* number of parameters */
 	char	  **bind_params;	/* parameters for extended query protocol call */
-	bool		close_flag;		/* one-shot request to close a prepared
-								 * statement using extended query protocol */
-	bool		parse_flag;		/* one-shot request to parse query using
-								 * extended query protocol */
 	char	   *stmtName;		/* prepared statement name used for extended
 								 * query protocol commands */
 	bool		crosstab_flag;	/* one-shot request to crosstab result */
-- 
2.39.3 (Apple Git-146)

From 9ec5b893114f63332be30f88f9a81402e044501e Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
Date: Wed, 18 Sep 2024 08:17:31 +0200
Subject: Clean psql extended state on extended command

When handling an extended command, bind_params and stmtName would be set
to NULL, leaking possibly allocated memory. On top of that, the
send_mode was not reset, leading to a possible state where we could
try to process an bind_named query with a NULL stmtName, leading to an
assert failure.

This patch reset the extended state to a correct state by freeing
allocated parameters and setting the send_mode back to default whenever
an extended extended backslash query is processed.
---
 src/bin/psql/command.c             | 12 +++----
 src/bin/psql/common.c              | 58 +++++++++++++++++++-----------
 src/bin/psql/common.h              |  1 +
 src/test/regress/expected/psql.out |  8 +++++
 src/test/regress/sql/psql.sql      |  5 +++
 5 files changed, 55 insertions(+), 29 deletions(-)

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 4dfc7b2d857..16fb7973d23 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -483,9 +483,7 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
 		int			nparams = 0;
 		int			nalloc = 0;
 
-		pset.bind_params = NULL;
-		pset.stmtName = NULL;
-
+		clean_extended_state();
 		while ((opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false)))
 		{
 			nparams++;
@@ -521,9 +519,7 @@ exec_command_bind_named(PsqlScanState scan_state, bool active_branch,
 		int			nparams = 0;
 		int			nalloc = 0;
 
-		pset.bind_params = NULL;
-		pset.stmtName = NULL;
-
+		clean_extended_state();
 		/* get the mandatory prepared statement name */
 		opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false);
 		if (!opt)
@@ -719,7 +715,7 @@ exec_command_close(PsqlScanState scan_state, bool active_branch, const char *cmd
 		char	   *opt = psql_scan_slash_option(scan_state,
 												 OT_NORMAL, NULL, false);
 
-		pset.stmtName = NULL;
+		clean_extended_state();
 		if (!opt)
 		{
 			pg_log_error("\\%s: missing required argument", cmd);
@@ -2205,7 +2201,7 @@ exec_command_parse(PsqlScanState scan_state, bool active_branch,
 		char	   *opt = psql_scan_slash_option(scan_state,
 												 OT_NORMAL, NULL, false);
 
-		pset.stmtName = NULL;
+		clean_extended_state();
 		if (!opt)
 		{
 			pg_log_error("\\%s: missing required argument", cmd);
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 066dccbd841..d66477873ee 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1275,27 +1275,7 @@ sendquery_cleanup:
 	}
 
 	/* clean up after extended protocol queries */
-	switch (pset.send_mode)
-	{
-		case PSQL_SEND_EXTENDED_CLOSE:	/* \close */
-			free(pset.stmtName);
-			break;
-		case PSQL_SEND_EXTENDED_PARSE:	/* \parse */
-			free(pset.stmtName);
-			break;
-		case PSQL_SEND_EXTENDED_QUERY_PARAMS:	/* \bind */
-		case PSQL_SEND_EXTENDED_QUERY_PREPARED: /* \bind_named */
-			for (i = 0; i < pset.bind_nparams; i++)
-				free(pset.bind_params[i]);
-			free(pset.bind_params);
-			free(pset.stmtName);
-			pset.bind_params = NULL;
-			break;
-		case PSQL_SEND_QUERY:
-			break;
-	}
-	pset.stmtName = NULL;
-	pset.send_mode = PSQL_SEND_QUERY;
+	clean_extended_state();
 
 	/* reset \gset trigger */
 	if (pset.gset_prefix)
@@ -2287,6 +2267,42 @@ uri_prefix_length(const char *connstr)
 	return 0;
 }
 
+/*
+ * Reset psql extended state
+ *
+ * Handling backslash command for extended protocol will change the
+ * send mode and allocate stmtName and bind params. This state needs
+ * to be cleaned when the query is processed or when a new extended
+ * command is processed, erasing the previous state.
+ */
+void
+clean_extended_state(void)
+{
+	int			i;
+
+	switch (pset.send_mode)
+	{
+		case PSQL_SEND_EXTENDED_CLOSE:	/* \close */
+			free(pset.stmtName);
+			break;
+		case PSQL_SEND_EXTENDED_PARSE:	/* \parse */
+			free(pset.stmtName);
+			break;
+		case PSQL_SEND_EXTENDED_QUERY_PARAMS:	/* \bind */
+		case PSQL_SEND_EXTENDED_QUERY_PREPARED: /* \bind_named */
+			for (i = 0; i < pset.bind_nparams; i++)
+				free(pset.bind_params[i]);
+			free(pset.bind_params);
+			free(pset.stmtName);
+			pset.bind_params = NULL;
+			break;
+		case PSQL_SEND_QUERY:
+			break;
+	}
+	pset.stmtName = NULL;
+	pset.send_mode = PSQL_SEND_QUERY;
+}
+
 /*
  * Recognized connection string either starts with a valid URI prefix or
  * contains a "=" in it.
diff --git a/src/bin/psql/common.h b/src/bin/psql/common.h
index 6efe12274fe..e3762a2c6c7 100644
--- a/src/bin/psql/common.h
+++ b/src/bin/psql/common.h
@@ -41,6 +41,7 @@ extern bool standard_strings(void);
 extern const char *session_username(void);
 
 extern void expand_tilde(char **filename);
+extern void clean_extended_state(void);
 
 extern bool recognized_connection_string(const char *connstr);
 
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 6aeb7cb9636..6f585fd6a13 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -132,6 +132,14 @@ SELECT $1, $2 \parse stmt3
  foo      | bar
 (1 row)
 
+-- Check multiple calls to bind_named
+\bind_named test
+\bind_named
+\bind_named: missing required argument
+\g
+ERROR:  there is no parameter $1
+LINE 1: SELECT $1, $2 
+               ^
 -- \close (extended query protocol)
 \close
 \close: missing required argument
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 0a2f8b46922..71f63b7f124 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -59,6 +59,11 @@ SELECT $1, $2 \parse stmt3
 \bind_named stmt2 'foo' \g
 \bind_named stmt3 'foo' 'bar' \g
 
+-- Check multiple calls to bind_named
+\bind_named test
+\bind_named
+\g
+
 -- \close (extended query protocol)
 \close
 \close ''
-- 
2.39.3 (Apple Git-146)

From 20637d0fdfb08ed7e79a241b82b1d4f9f9f34d8c Mon Sep 17 00:00:00 2001
From: Jelte Fennema-Nio <github-tech@jeltef.nl>
Date: Thu, 19 Sep 2024 10:50:53 +0200
Subject: [PATCH v1] psql: Add test for repeated \bind calls

---
 src/test/regress/expected/psql.out | 19 +++++++++++++++++++
 src/test/regress/sql/psql.sql      |  5 +++++
 2 files changed, 24 insertions(+)

diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index cf040fbd80..83a51c9682 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -173,6 +173,25 @@ SELECT $1, $2 \bind 'foo' 'bar' \g
  foo      | bar
 (1 row)
 
+-- second bind overwrites first
+select $1 as col \bind 1 \bind 2 \g
+ col 
+-----
+ 2
+(1 row)
+
+-- unless there's a \g in between
+select $1 as col \bind 1 \g \bind 2 \g
+ col 
+-----
+ 1
+(1 row)
+
+ col 
+-----
+ 2
+(1 row)
+
 -- errors
 -- parse error
 SELECT foo \bind \g
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 8de90c805c..b027e0eb7d 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -76,6 +76,11 @@ SELECT 1 \bind \g
 SELECT $1 \bind 'foo' \g
 SELECT $1, $2 \bind 'foo' 'bar' \g
 
+-- second bind overwrites first
+select $1 as col \bind 1 \bind 2 \g
+-- unless there's a \g in between
+select $1 as col \bind 1 \g \bind 2 \g
+
 -- errors
 -- parse error
 SELECT foo \bind \g
-- 
2.43.0

From e3d922c56371588527c2a2e22c5ae2dfd985b4c3 Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
Date: Mon, 23 Jun 2025 08:20:31 +0200
Subject: Rename \close to \close_prepared

\close is a new psql meta-command introduced in d55322b allowing to
close a prepared statement using extended protocol in psql. However, the
name close is ambiguous. At the SQL level, CLOSE is used to close an
existing cursor. At the protocol level, the close message can be used to
close either a statement or a portal.

This patch renames \close to \close_prepared to avoid this ambiguity and
make it clearer that this is used to close a prepared statement.
---
 doc/src/sgml/ref/psql-ref.sgml              |  8 ++++----
 doc/src/sgml/release-18.sgml                |  2 +-
 src/bin/psql/command.c                      | 12 ++++++------
 src/bin/psql/common.c                       |  2 +-
 src/bin/psql/help.c                         |  3 ++-
 src/bin/psql/tab-complete.in.c              |  2 +-
 src/test/regress/expected/psql.out          | 14 +++++++-------
 src/test/regress/expected/psql_pipeline.out |  6 +++---
 src/test/regress/sql/psql.sql               | 12 ++++++------
 src/test/regress/sql/psql_pipeline.sql      |  6 +++---
 10 files changed, 34 insertions(+), 33 deletions(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 570ef21d1fc..95f4cac2467 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1067,8 +1067,8 @@ INSERT INTO tbls1 VALUES ($1, $2) \parse stmt1
        </listitem>
       </varlistentry>
 
-      <varlistentry id="app-psql-meta-command-close">
-       <term><literal>\close</literal> <replaceable class="parameter">prepared_statement_name</replaceable></term>
+      <varlistentry id="app-psql-meta-command-close-prepared">
+       <term><literal>\close_prepared</literal> <replaceable class="parameter">prepared_statement_name</replaceable></term>
 
        <listitem>
         <para>
@@ -1081,7 +1081,7 @@ INSERT INTO tbls1 VALUES ($1, $2) \parse stmt1
          Example:
 <programlisting>
 SELECT $1 \parse stmt1
-\close stmt1
+\close_prepared stmt1
 </programlisting>
         </para>
 
@@ -3710,7 +3710,7 @@ testdb=&gt; <userinput>\setenv LESS -imx4F</userinput>
         All queries executed while a pipeline is ongoing use the extended
         query protocol. Queries are appended to the pipeline when ending with
         a semicolon. The meta-commands <literal>\bind</literal>,
-        <literal>\bind_named</literal>, <literal>\close</literal> or
+        <literal>\bind_named</literal>, <literal>\close_prepared</literal> or
         <literal>\parse</literal> can be used in an ongoing pipeline. While
         a pipeline is ongoing, <literal>\sendpipeline</literal> will append
         the current query buffer to the pipeline. Other meta-commands like
diff --git a/doc/src/sgml/release-18.sgml b/doc/src/sgml/release-18.sgml
index 662c7d8890f..66a6817a2be 100644
--- a/doc/src/sgml/release-18.sgml
+++ b/doc/src/sgml/release-18.sgml
@@ -2746,7 +2746,7 @@ Author: Michael Paquier <michael@paquier.xyz>
      <link
      linkend="app-psql-meta-command-bind-named"><literal>\bind_named</literal></link>,
      and <link
-     linkend="app-psql-meta-command-close"><literal>\close</literal></link>.
+     linkend="app-psql-meta-command-close-prepared"><literal>\close_prepared</literal></link>.
      </para>
      </listitem>
 
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 83e84a77841..9fcd2db8326 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -67,8 +67,8 @@ static backslashResult exec_command_C(PsqlScanState scan_state, bool active_bran
 static backslashResult exec_command_connect(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_cd(PsqlScanState scan_state, bool active_branch,
 									   const char *cmd);
-static backslashResult exec_command_close(PsqlScanState scan_state, bool active_branch,
-										  const char *cmd);
+static backslashResult exec_command_close_prepared(PsqlScanState scan_state,
+												   bool active_branch, const char *cmd);
 static backslashResult exec_command_conninfo(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_copy(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_copyright(PsqlScanState scan_state, bool active_branch);
@@ -330,8 +330,8 @@ exec_command(const char *cmd,
 		status = exec_command_connect(scan_state, active_branch);
 	else if (strcmp(cmd, "cd") == 0)
 		status = exec_command_cd(scan_state, active_branch, cmd);
-	else if (strcmp(cmd, "close") == 0)
-		status = exec_command_close(scan_state, active_branch, cmd);
+	else if (strcmp(cmd, "close_prepared") == 0)
+		status = exec_command_close_prepared(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "conninfo") == 0)
 		status = exec_command_conninfo(scan_state, active_branch);
 	else if (pg_strcasecmp(cmd, "copy") == 0)
@@ -728,10 +728,10 @@ exec_command_cd(PsqlScanState scan_state, bool active_branch, const char *cmd)
 }
 
 /*
- * \close -- close a previously prepared statement
+ * \close_prepared -- close a previously prepared statement
  */
 static backslashResult
-exec_command_close(PsqlScanState scan_state, bool active_branch, const char *cmd)
+exec_command_close_prepared(PsqlScanState scan_state, bool active_branch, const char *cmd)
 {
 	backslashResult status = PSQL_CMD_SKIP_LINE;
 
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index b53cd8ab698..d2c0a49c46c 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -2628,7 +2628,7 @@ clean_extended_state(void)
 
 	switch (pset.send_mode)
 	{
-		case PSQL_SEND_EXTENDED_CLOSE:	/* \close */
+		case PSQL_SEND_EXTENDED_CLOSE:	/* \close_prepared */
 			free(pset.stmtName);
 			break;
 		case PSQL_SEND_EXTENDED_PARSE:	/* \parse */
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index db6adec8b69..a2e009ab9be 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -331,7 +331,8 @@ slashUsage(unsigned short int pager)
 	HELP0("  \\bind [PARAM]...       set query parameters\n");
 	HELP0("  \\bind_named STMT_NAME [PARAM]...\n"
 		  "                         set query parameters for an existing prepared statement\n");
-	HELP0("  \\close STMT_NAME       close an existing prepared statement\n");
+	HELP0("  \\close_prepared STMT_NAME\n"
+		  "                         close an existing prepared statement\n");
 	HELP0("  \\endpipeline           exit pipeline mode\n");
 	HELP0("  \\flush                 flush output data to the server\n");
 	HELP0("  \\flushrequest          send request to the server to flush its output buffer\n");
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 2c0b4f28c14..908eef97c6e 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1875,7 +1875,7 @@ psql_completion(const char *text, int start, int end)
 	static const char *const backslash_commands[] = {
 		"\\a",
 		"\\bind", "\\bind_named",
-		"\\connect", "\\conninfo", "\\C", "\\cd", "\\close", "\\copy",
+		"\\connect", "\\conninfo", "\\C", "\\cd", "\\close_prepared", "\\copy",
 		"\\copyright", "\\crosstabview",
 		"\\d", "\\da", "\\dA", "\\dAc", "\\dAf", "\\dAo", "\\dAp",
 		"\\db", "\\dc", "\\dconfig", "\\dC", "\\dd", "\\ddp", "\\dD",
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index cf48ae6d0c2..236eba2540e 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -160,12 +160,12 @@ LINE 1: SELECT $1, $2
  foo4     | bar4
 (1 row)
 
--- \close (extended query protocol)
-\close
-\close: missing required argument
-\close ''
-\close stmt2
-\close stmt2
+-- \close_prepared (extended query protocol)
+\close_prepared
+\close_prepared: missing required argument
+\close_prepared ''
+\close_prepared stmt2
+\close_prepared stmt2
 SELECT name, statement FROM pg_prepared_statements ORDER BY name;
  name  |   statement    
 -------+----------------
@@ -4666,7 +4666,7 @@ bar 'bar' "bar"
 	\C arg1
 	\c arg1 arg2 arg3 arg4
 	\cd arg1
-	\close stmt1
+	\close_prepared stmt1
 	\conninfo
 	\copy arg1 arg2 arg3 arg4 arg5 arg6
 	\copyright
diff --git a/src/test/regress/expected/psql_pipeline.out b/src/test/regress/expected/psql_pipeline.out
index e78e6bfa0ad..a0816fb10b6 100644
--- a/src/test/regress/expected/psql_pipeline.out
+++ b/src/test/regress/expected/psql_pipeline.out
@@ -564,7 +564,7 @@ SELECT $1 \bind \sendpipeline
 SELECT $1 \bind 1 \sendpipeline
 SELECT $1 \parse a
 \bind_named a 1 \sendpipeline
-\close a
+\close_prepared a
 \flushrequest
 \getresults
 ERROR:  bind message supplies 0 parameters, but prepared statement "" requires 1
@@ -572,7 +572,7 @@ ERROR:  bind message supplies 0 parameters, but prepared statement "" requires 1
 SELECT $1 \bind 1 \sendpipeline
 SELECT $1 \parse a
 \bind_named a 1 \sendpipeline
-\close a
+\close_prepared a
 -- Sync allows pipeline to recover.
 \syncpipeline
 \getresults
@@ -580,7 +580,7 @@ Pipeline aborted, command did not run
 SELECT $1 \bind 1 \sendpipeline
 SELECT $1 \parse a
 \bind_named a 1 \sendpipeline
-\close a
+\close_prepared a
 \flushrequest
 \getresults
  ?column? 
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 1a8a83462f0..e2e31245439 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -68,11 +68,11 @@ SELECT $1, $2 \parse stmt3
 -- Multiple \g calls mean multiple executions
 \bind_named stmt2 'foo3' \g \bind_named stmt3 'foo4' 'bar4' \g
 
--- \close (extended query protocol)
-\close
-\close ''
-\close stmt2
-\close stmt2
+-- \close_prepared (extended query protocol)
+\close_prepared
+\close_prepared ''
+\close_prepared stmt2
+\close_prepared stmt2
 SELECT name, statement FROM pg_prepared_statements ORDER BY name;
 
 -- \bind (extended query protocol)
@@ -1035,7 +1035,7 @@ select \if false \\ (bogus \else \\ 42 \endif \\ forty_two;
 	\C arg1
 	\c arg1 arg2 arg3 arg4
 	\cd arg1
-	\close stmt1
+	\close_prepared stmt1
 	\conninfo
 	\copy arg1 arg2 arg3 arg4 arg5 arg6
 	\copyright
diff --git a/src/test/regress/sql/psql_pipeline.sql b/src/test/regress/sql/psql_pipeline.sql
index 5945eca1ef7..6788dceee2e 100644
--- a/src/test/regress/sql/psql_pipeline.sql
+++ b/src/test/regress/sql/psql_pipeline.sql
@@ -306,21 +306,21 @@ SELECT $1 \bind \sendpipeline
 SELECT $1 \bind 1 \sendpipeline
 SELECT $1 \parse a
 \bind_named a 1 \sendpipeline
-\close a
+\close_prepared a
 \flushrequest
 \getresults
 -- Pipeline is aborted.
 SELECT $1 \bind 1 \sendpipeline
 SELECT $1 \parse a
 \bind_named a 1 \sendpipeline
-\close a
+\close_prepared a
 -- Sync allows pipeline to recover.
 \syncpipeline
 \getresults
 SELECT $1 \bind 1 \sendpipeline
 SELECT $1 \parse a
 \bind_named a 1 \sendpipeline
-\close a
+\close_prepared a
 \flushrequest
 \getresults
 \endpipeline
-- 
2.49.0