psql: add \si, \sm, \st and \sr functions to show CREATE commands for indexes, matviews, triggers and tables

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 560eacc7f0c..c00af2be0ab 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -49,7 +49,11 @@
 typedef enum EditableObjectType
 {
 	EditableFunction,
-	EditableView
+	EditableView,
+	EditableMatview,
+	EditableIndex,
+	EditableTrigger,
+	EditableTable
 } EditableObjectType;
 
 /* local function declarations */
@@ -119,6 +123,14 @@ static backslashResult exec_command_setenv(PsqlScanState scan_state, bool active
 										   const char *cmd);
 static backslashResult exec_command_sf_sv(PsqlScanState scan_state, bool active_branch,
 										  const char *cmd, bool is_func);
+static backslashResult exec_command_sm(PsqlScanState scan_state, bool active_branch,
+									   const char *cmd);
+static backslashResult exec_command_si(PsqlScanState scan_state, bool active_branch,
+									   const char *cmd);
+static backslashResult exec_command_st(PsqlScanState scan_state, bool active_branch,
+									   const char *cmd);
+static backslashResult exec_command_sr(PsqlScanState scan_state, bool active_branch,
+									   const char *cmd);
 static backslashResult exec_command_t(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_T(PsqlScanState scan_state, bool active_branch);
 static backslashResult exec_command_timing(PsqlScanState scan_state, bool active_branch);
@@ -154,6 +166,8 @@ static bool do_shell(const char *command);
 static bool do_watch(PQExpBuffer query_buf, double sleep);
 static bool lookup_object_oid(EditableObjectType obj_type, const char *desc,
 							  Oid *obj_oid);
+static bool lookup_trigger_oid(const char *table_name, const char *trigger_name,
+				  Oid *obj_oid);
 static bool get_create_object_cmd(EditableObjectType obj_type, Oid oid,
 								  PQExpBuffer buf);
 static int	strip_lineno_from_objdesc(char *obj);
@@ -384,6 +398,14 @@ exec_command(const char *cmd,
 		status = exec_command_sf_sv(scan_state, active_branch, cmd, true);
 	else if (strcmp(cmd, "sv") == 0 || strcmp(cmd, "sv+") == 0)
 		status = exec_command_sf_sv(scan_state, active_branch, cmd, false);
+	else if (strcmp(cmd, "sm") == 0 || strcmp(cmd, "sm+") == 0)
+		status = exec_command_sm(scan_state, active_branch, cmd);
+	else if (strcmp(cmd, "si") == 0 || strcmp(cmd, "si+") == 0)
+		status = exec_command_si(scan_state, active_branch, cmd);
+	else if (strcmp(cmd, "st") == 0 || strcmp(cmd, "st+") == 0)
+		status = exec_command_st(scan_state, active_branch, cmd);
+	else if (strcmp(cmd, "sr") == 0 || strcmp(cmd, "sr+") == 0)
+		status = exec_command_sr(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "t") == 0)
 		status = exec_command_t(scan_state, active_branch);
 	else if (strcmp(cmd, "T") == 0)
@@ -2422,6 +2444,358 @@ exec_command_sf_sv(PsqlScanState scan_state, bool active_branch,
 	return status;
 }
 
+/*
+ * \sm -- show a matview's source code
+ */
+static backslashResult
+exec_command_sm(PsqlScanState scan_state, bool active_branch, const char *cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		bool		show_linenumbers = (strcmp(cmd, "sm+") == 0);
+		PQExpBuffer mview_buf;
+		char	   *mview;
+		Oid			view_oid = InvalidOid;
+
+		mview_buf = createPQExpBuffer();
+		mview = psql_scan_slash_option(scan_state,
+									   OT_WHOLE_LINE, NULL, true);
+
+		/*
+		 * TO-DO add version check
+		 */
+
+		if (!mview)
+		{
+			pg_log_error("matview name is required");
+			status = PSQL_CMD_ERROR;
+		}
+		else if (!lookup_object_oid(EditableMatview, mview, &view_oid))
+		{
+			/* error already reported */
+			status = PSQL_CMD_ERROR;
+		}
+		else if (!get_create_object_cmd(EditableMatview, view_oid, mview_buf))
+		{
+			/* error already reported */
+			status = PSQL_CMD_ERROR;
+		}
+		else
+		{
+			FILE	   *output;
+			bool		is_pager;
+
+			/* Select output stream: stdout, pager, or file */
+			if (pset.queryFout == stdout)
+			{
+				/* count lines in view to see if pager is needed */
+				int			lineno = count_lines_in_buf(mview_buf);
+
+				output = PageOutput(lineno, &(pset.popt.topt));
+				is_pager = true;
+			}
+			else
+			{
+				/* use previously set output file, without pager */
+				output = pset.queryFout;
+				is_pager = false;
+			}
+
+			if (show_linenumbers)
+			{
+				/* add line numbers, numbering all lines */
+				print_with_linenumbers(output, mview_buf->data, NULL);
+			}
+			else
+			{
+				/* just send the view definition to output */
+				fputs(mview_buf->data, output);
+			}
+
+			if (is_pager)
+				ClosePager(output);
+		}
+
+		if (mview)
+			free(mview);
+		destroyPQExpBuffer(mview_buf);
+	}
+	else
+		ignore_slash_whole_line(scan_state);
+
+	return status;
+}
+
+/*
+ * \si -- show an index's source code
+ */
+static backslashResult
+exec_command_si(PsqlScanState scan_state, bool active_branch, const char *cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		bool		show_linenumbers = (strcmp(cmd, "si+") == 0);
+		PQExpBuffer index_buf;
+		char	   *index;
+		Oid			index_oid = InvalidOid;
+
+		index_buf = createPQExpBuffer();
+		index = psql_scan_slash_option(scan_state,
+									   OT_WHOLE_LINE, NULL, true);
+
+		/*
+		 * TO-DO add version check
+		 */
+
+		if (!index)
+		{
+			pg_log_error("index name is required");
+			status = PSQL_CMD_ERROR;
+		}
+		else if (!lookup_object_oid(EditableIndex, index, &index_oid))
+		{
+			/* error already reported */
+			status = PSQL_CMD_ERROR;
+		}
+		else if (!get_create_object_cmd(EditableIndex, index_oid, index_buf))
+		{
+			/* error already reported */
+			status = PSQL_CMD_ERROR;
+		}
+		else
+		{
+			FILE	   *output;
+			bool		is_pager;
+
+			/* Select output stream: stdout, pager, or file */
+			if (pset.queryFout == stdout)
+			{
+				/* count lines in view to see if pager is needed */
+				int			lineno = count_lines_in_buf(index_buf);
+
+				output = PageOutput(lineno, &(pset.popt.topt));
+				is_pager = true;
+			}
+			else
+			{
+				/* use previously set output file, without pager */
+				output = pset.queryFout;
+				is_pager = false;
+			}
+
+			if (show_linenumbers)
+			{
+				/* add line numbers, numbering all lines */
+				print_with_linenumbers(output, index_buf->data, NULL);
+			}
+			else
+			{
+				/* just send the view definition to output */
+				fputs(index_buf->data, output);
+			}
+
+			if (is_pager)
+				ClosePager(output);
+		}
+
+		if (index)
+			free(index);
+		destroyPQExpBuffer(index_buf);
+	}
+	else
+		ignore_slash_whole_line(scan_state);
+
+	return status;
+}
+
+/*
+ * \st -- show a trigger's source code
+ * the format of \st arguments is "table_name TRIGGER trigger_name",
+ * because different tables can have triggers with the same name
+ */
+static backslashResult
+exec_command_st(PsqlScanState scan_state, bool active_branch, const char *cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		bool		show_linenumbers = (strcmp(cmd, "st+") == 0);
+		PQExpBuffer trigger_buf;
+		char 	   *table;
+		char 	   *tg_string;
+		char	   *trigger;
+		Oid			trigger_oid = InvalidOid;
+
+		trigger_buf = createPQExpBuffer();
+		table = psql_scan_slash_option(scan_state,
+										 OT_NORMAL, NULL, true);
+		tg_string = psql_scan_slash_option(scan_state,
+										 OT_NORMAL, NULL, true);
+		trigger = psql_scan_slash_option(scan_state,
+										 OT_NORMAL, NULL, true);
+
+		/*
+		 * TO-DO add version check
+		 */
+
+		if (!trigger)
+		{
+			pg_log_error("trigger name is required");
+			status = PSQL_CMD_ERROR;
+		}
+		else if (strcmp(tg_string, "TRIGGER") != 0) {
+			pg_log_error("wrong command format");
+			status = PSQL_CMD_ERROR;
+		}
+		else if (!table) {
+			pg_log_error("table name is required");
+			status = PSQL_CMD_ERROR;
+		}
+		else if (!lookup_trigger_oid(table, trigger, &trigger_oid))
+		{
+			/* error already reported */
+			status = PSQL_CMD_ERROR;
+		}
+		else if (!get_create_object_cmd(EditableTrigger, trigger_oid, trigger_buf))
+		{
+			/* error already reported */
+			status = PSQL_CMD_ERROR;
+		}
+		else
+		{
+			FILE	   *output;
+			bool		is_pager;
+
+			/* Select output stream: stdout, pager, or file */
+			if (pset.queryFout == stdout)
+			{
+				/* count lines in view to see if pager is needed */
+				int			lineno = count_lines_in_buf(trigger_buf);
+
+				output = PageOutput(lineno, &(pset.popt.topt));
+				is_pager = true;
+			}
+			else
+			{
+				/* use previously set output file, without pager */
+				output = pset.queryFout;
+				is_pager = false;
+			}
+
+			if (show_linenumbers)
+			{
+				/* add line numbers, numbering all lines */
+				print_with_linenumbers(output, trigger_buf->data, NULL);
+			}
+			else
+			{
+				/* just send the view definition to output */
+				fputs(trigger_buf->data, output);
+			}
+
+			if (is_pager)
+				ClosePager(output);
+		}
+
+		if (trigger)
+			free(trigger);
+		destroyPQExpBuffer(trigger_buf);
+	}
+	else
+		ignore_slash_whole_line(scan_state);
+
+	return status;
+}
+
+/*
+ * \sr -- show a tables's source code
+ */
+static backslashResult
+exec_command_sr(PsqlScanState scan_state, bool active_branch, const char *cmd)
+{
+	backslashResult status = PSQL_CMD_SKIP_LINE;
+
+	if (active_branch)
+	{
+		bool		show_linenumbers = (strcmp(cmd, "sr+") == 0);
+		PQExpBuffer table_buf;
+		char	   *table;
+		Oid			table_oid = InvalidOid;
+
+		table_buf = createPQExpBuffer();
+		table = psql_scan_slash_option(scan_state,
+									   OT_WHOLE_LINE, NULL, true);
+
+		/*
+		 * TO-DO add version check
+		 */
+
+		if (!table)
+		{
+			pg_log_error("table name is required");
+			status = PSQL_CMD_ERROR;
+		}
+		else if (!lookup_object_oid(EditableTable, table, &table_oid))
+		{
+			/* error already reported */
+			status = PSQL_CMD_ERROR;
+		}
+		else if (!get_create_object_cmd(EditableTable, table_oid, table_buf))
+		{
+			/* error already reported */
+			status = PSQL_CMD_ERROR;
+		}
+		else
+		{
+			FILE	   *output;
+			bool		is_pager;
+
+			/* Select output stream: stdout, pager, or file */
+			if (pset.queryFout == stdout)
+			{
+				/* count lines in view to see if pager is needed */
+				int			lineno = count_lines_in_buf(table_buf);
+
+				output = PageOutput(lineno, &(pset.popt.topt));
+				is_pager = true;
+			}
+			else
+			{
+				/* use previously set output file, without pager */
+				output = pset.queryFout;
+				is_pager = false;
+			}
+
+			if (show_linenumbers)
+			{
+				/* add line numbers, numbering all lines */
+				print_with_linenumbers(output, table_buf->data, NULL);
+			}
+			else
+			{
+				/* just send the view definition to output */
+				fputs(table_buf->data, output);
+			}
+
+			if (is_pager)
+				ClosePager(output);
+		}
+
+		if (table)
+			free(table);
+		destroyPQExpBuffer(table_buf);
+	}
+	else
+		ignore_slash_whole_line(scan_state);
+
+	return status;
+}
+
 /*
  * \t -- turn off table headers and row count
  */
@@ -4805,16 +5179,32 @@ lookup_object_oid(EditableObjectType obj_type, const char *desc,
 			break;
 
 		case EditableView:
+		case EditableMatview:
+		case EditableIndex:
+		case EditableTable:
 
 			/*
-			 * Convert view name (possibly schema-qualified) to OID.  Note:
-			 * this code doesn't check if the relation is actually a view.
-			 * We'll detect that in get_create_object_cmd().
+			 * Convert name (possibly schema-qualified) to OID.  Note: this
+			 * code doesn't check if the relation is actually of the right
+			 * type. We'll detect that in get_create_object_cmd().
 			 */
 			appendPQExpBufferStr(query, "SELECT ");
 			appendStringLiteralConn(query, desc, pset.db);
 			appendPQExpBufferStr(query, "::pg_catalog.regclass::pg_catalog.oid");
 			break;
+
+		case EditableTrigger:
+
+			/*
+			 * Note: triggers of different databases can have the same name,
+			 * this function displays only the first result
+			 */
+			printfPQExpBuffer(query,
+							  "SELECT t.oid FROM pg_trigger t "
+							  "WHERE tgname = \'%s\' LIMIT 1",
+							  desc);
+			break;
+
 	}
 
 	if (!echo_hidden_command(query->data))
@@ -4827,7 +5217,61 @@ lookup_object_oid(EditableObjectType obj_type, const char *desc,
 		*obj_oid = atooid(PQgetvalue(res, 0, 0));
 	else
 	{
-		minimal_error_message(res);
+		if (obj_type == EditableTrigger)
+		{
+			/*
+			 * Printing error message for triggers
+			 */
+			pg_log_error("trigger does not exist");
+		}
+		else
+		{
+			minimal_error_message(res);
+		}
+		result = false;
+	}
+
+	PQclear(res);
+	destroyPQExpBuffer(query);
+
+	return result;
+}
+
+/*
+ * Triggers use a separate lookup_oid function, because
+ * trigger names are not unique and multiple tables can have
+ * a trigger of the same name, so we must also look up table's oid
+ */
+static bool
+lookup_trigger_oid(const char *table_name, const char *trigger_name,
+				  Oid *obj_oid)
+{
+	bool		result = true;
+	PQExpBuffer query = createPQExpBuffer();
+	PGresult   *res;
+	Oid			table_oid = InvalidOid;
+
+	if (!lookup_object_oid(EditableTable, table_name, &table_oid)) {
+		return false;
+	}
+
+	printfPQExpBuffer(query,
+					"SELECT t.oid FROM pg_trigger t "
+					"LEFT JOIN pg_class c ON t.tgrelid = c.oid "
+					"WHERE t.tgname = \'%s\' AND c.oid = %u",
+					trigger_name, table_oid);
+
+	if (!echo_hidden_command(query->data))
+	{
+		destroyPQExpBuffer(query);
+		return false;
+	}
+	res = PQexec(pset.db, query->data);
+	if (PQresultStatus(res) == PGRES_TUPLES_OK && PQntuples(res) == 1)
+		*obj_oid = atooid(PQgetvalue(res, 0, 0));
+	else
+	{
+		pg_log_error("trigger does not exist");
 		result = false;
 	}
 
@@ -4910,6 +5354,45 @@ get_create_object_cmd(EditableObjectType obj_type, Oid oid,
 								  oid);
 			}
 			break;
+		case EditableMatview:
+			printfPQExpBuffer(query,
+							  "SELECT m.definition, relname, relkind, amname, nspname, reloptions "
+							  "FROM pg_catalog.pg_class c "
+							  "LEFT JOIN pg_matviews m ON c.relname = m.matviewname "
+							  "LEFT JOIN pg_am a on c.relam = a.oid "
+							  "LEFT JOIN pg_namespace s ON c.relnamespace = s.oid "
+							  "WHERE c.oid = %u ",
+							  oid);
+			break;
+		case EditableIndex:
+			printfPQExpBuffer(query,
+							  "SELECT i.indexdef, relkind, relname "
+							  "FROM pg_catalog.pg_class c "
+							  "LEFT JOIN pg_indexes i ON c.relname = i.indexname "
+							  "WHERE c.oid = %u ",
+							  oid);
+			break;
+
+		case EditableTrigger:
+			printfPQExpBuffer(query,
+							  "SELECT pg_catalog.pg_get_triggerdef(%u)",
+							  oid);
+			break;
+
+		case EditableTable:
+			printfPQExpBuffer(query,
+							  "SELECT relname, relkind, nspname, spcname, reloptions, "
+							  "relnatts, relpersistence, reloftype, "
+							  "relispartition, pg_get_partkeydef(c.oid), "
+							  "pg_get_expr(c.relpartbound, c.oid, true), "
+							  "amname, typname "
+							  "FROM pg_class c LEFT JOIN pg_type t ON t.oid = c.reloftype "
+							  "LEFT JOIN pg_namespace s ON c.relnamespace = s.oid "
+							  "LEFT JOIN pg_am a on c.relam = a.oid "
+							  "LEFT JOIN pg_tablespace tbs on c.reltablespace = tbs.oid "
+							  "WHERE c.oid = %u",
+							  oid);
+			break;
 	}
 
 	if (!echo_hidden_command(query->data))
@@ -4924,8 +5407,80 @@ get_create_object_cmd(EditableObjectType obj_type, Oid oid,
 		switch (obj_type)
 		{
 			case EditableFunction:
+			case EditableTrigger:
 				appendPQExpBufferStr(buf, PQgetvalue(res, 0, 0));
 				break;
+			case EditableIndex:
+				{
+					char	   *indexdef = PQgetvalue(res, 0, 0);
+					char	   *relkind = PQgetvalue(res, 0, 1);
+					char	   *relname = PQgetvalue(res, 0, 2);
+
+					/*
+					 * Check if relation is an index
+					 */
+					if (relkind[0] != 'i' && relkind[0] != 'I')
+					{
+						pg_log_error("\"%s\" is not an index",
+									 relname);
+						result = false;
+						break;
+					}
+					appendPQExpBufferStr(buf, indexdef);
+					break;
+				}
+			case EditableMatview:
+				{
+					char	   *viewdef = PQgetvalue(res, 0, 0);
+					char	   *relname = PQgetvalue(res, 0, 1);
+					char	   *relkind = PQgetvalue(res, 0, 2);
+					char	   *amname = PQgetvalue(res, 0, 3);
+					char	   *spcname = PQgetvalue(res, 0, 4);
+					char	   *reloptions = PQgetvalue(res, 0, 5);
+
+					switch (relkind[0])
+					{
+						case RELKIND_MATVIEW:
+							appendPQExpBufferStr(buf, "CREATE MATERIALIZED VIEW ");
+							break;
+						default:
+							pg_log_error("\"%s\" is not a matview",
+										 relname);
+							result = false;
+							break;
+					}
+					appendPQExpBuffer(buf, "%s", relname);
+
+					/*
+					 * add access method
+					 */
+					if (!PQgetisnull(res, 0, 3))
+					{
+						appendPQExpBuffer(buf, "\n    USING %s", amname);
+					}
+
+					/* reloptions, if not an empty array "{}" */
+					if (reloptions != NULL && strlen(reloptions) > 2)
+					{
+						appendPQExpBufferStr(buf, "\n    WITH (");
+						if (!appendReloptionsArray(buf, reloptions, "",
+												   pset.encoding,
+												   standard_strings()))
+						{
+							pg_log_error("could not parse reloptions array");
+							result = false;
+						}
+						appendPQExpBufferChar(buf, ')');
+					}
+
+
+					if (!PQgetisnull(res, 0, 4))
+					{
+						appendPQExpBuffer(buf, "\n    TABLESPACE %s", spcname);
+					}
+					appendPQExpBuffer(buf, "\n    AS\n   %s", viewdef);
+				}
+				break;
 
 			case EditableView:
 				{
@@ -4946,7 +5501,7 @@ get_create_object_cmd(EditableObjectType obj_type, Oid oid,
 					{
 #ifdef NOT_USED
 						case RELKIND_MATVIEW:
-							appendPQExpBufferStr(buf, "CREATE OR REPLACE MATERIALIZED VIEW ");
+							appendPQExpBufferStr(buf, "CREATE MATERIALIZED VIEW ");
 							break;
 #endif
 						case RELKIND_VIEW:
@@ -4988,7 +5543,291 @@ get_create_object_cmd(EditableObjectType obj_type, Oid oid,
 										  checkoption);
 				}
 				break;
+			case EditableTable:
+				{
+					char	   *relname = PQgetvalue(res, 0, 0);
+					char	   *relkind = PQgetvalue(res, 0, 1);
+					char	   *nspname = PQgetvalue(res, 0, 2);
+					char	   *spcname = PQgetvalue(res, 0, 3);
+					char	   *reloptions = PQgetvalue(res, 0, 4);
+					int			relnatts = atoi(PQgetvalue(res, 0, 5));
+					char	   *relpersistence = PQgetvalue(res, 0, 6);
+					char	   *reloftype = PQgetvalue(res, 0, 7);
+					char	   *relispartition = PQgetvalue(res, 0, 8);
+					char	   *partkeydef = PQgetvalue(res, 0, 9);
+					char	   *relpartbound = PQgetvalue(res, 0, 10);
+					char	   *amname = PQgetvalue(res, 0, 11);
+					int			numParents;
+
+					PQExpBuffer column_info = createPQExpBuffer();
+					PQExpBuffer constraint_info = createPQExpBuffer();
+					PQExpBuffer parent_info = createPQExpBuffer();
+
+					PGresult   *colres;
+					PGresult   *conres;
+					PGresult   *parres;
+
+					/*
+					 * Check if relation is a table
+					 */
+					if (relkind[0] != 'r' && relkind[0] != 't' && relkind[0] != 'p')
+					{
+						pg_log_error("\"%s\" is not a table",
+									 relname);
+						result = false;
+						break;
+					}
+
+					/*
+					 * Constructing select statements for information about
+					 * columns, constraints and parents of a table
+					 */
+
+					printfPQExpBuffer(column_info,
+									  "SELECT attname, pg_catalog.format_type(atttypid, NULL), collname, attnotnull, atthasdef, pg_get_expr(d.adbin, d.adrelid) "
+									  "FROM pg_attribute a LEFT JOIN pg_type t on a.atttypid = t.oid "
+									  "LEFT JOIN pg_attrdef d "
+									  "ON d.adrelid = a.attrelid AND d.adnum = a.attnum "
+									  "LEFT JOIN pg_collation c ON a.attcollation = c.oid "
+									  "WHERE attrelid = %u AND attnum >= 1 "
+									  "ORDER BY attnum ASC ",
+									  oid);
+					if (!echo_hidden_command(column_info->data))
+					{
+						result = false;
+						break;
+					}
+
+					printfPQExpBuffer(constraint_info,
+									  "SELECT con.conname, pg_get_constraintdef(con.oid) "
+									  "FROM pg_catalog.pg_constraint con "
+									  "INNER JOIN pg_catalog.pg_class rel "
+									  "ON rel.oid = con.conrelid "
+									  "WHERE rel.oid = %u ;",
+									  oid);
+					if (!echo_hidden_command(constraint_info->data))
+					{
+						result = false;
+						break;
+					}
+
+					printfPQExpBuffer(parent_info,
+									  "SELECT relname, nspname "
+									  "FROM pg_class c LEFT JOIN pg_inherits i ON i.inhparent = c.oid "
+									  "LEFT JOIN pg_namespace n ON c.relnamespace = n.oid "
+									  "WHERE inhrelid = %u ORDER BY inhseqno ASC",
+									  oid);
+					if (!echo_hidden_command(parent_info->data))
+					{
+						result = false;
+						break;
+					}
+
+					colres = PQexec(pset.db, column_info->data);
+					conres = PQexec(pset.db, constraint_info->data);
+					parres = PQexec(pset.db, parent_info->data);
+
+					if (PQresultStatus(colres) != PGRES_TUPLES_OK)
+					{
+						minimal_error_message(colres);
+						result = false;
+						break;
+					}
+					if (PQresultStatus(conres) != PGRES_TUPLES_OK)
+					{
+						minimal_error_message(conres);
+						result = false;
+						break;
+					}
+					if (PQresultStatus(parres) != PGRES_TUPLES_OK)
+					{
+						minimal_error_message(parres);
+						result = false;
+						break;
+					}
+
+					numParents = PQntuples(parres);
+
+					appendPQExpBuffer(buf, "CREATE ");
+					switch (relpersistence[0])
+					{
+						case 'u':
+							appendPQExpBuffer(buf, "UNLOGGED ");
+							break;
+						case 't':
+							appendPQExpBuffer(buf, "TEMPORARY ");
+							break;
+						default:
+							break;
+					}
+					appendPQExpBuffer(buf, "TABLE %s.%s", nspname, relname);
+
+					/*
+					 * if typed, add type name
+					 */
+					if (strcmp(reloftype, "0") != 0)
+					{
+						char	   *typname = PQgetvalue(res, 0, 13);
+
+						appendPQExpBuffer(buf, "\n    OF %s", typname);
+					}
+
+					if (relispartition[0] == 't')
+					{
+
+						/*
+						 * Partition can only have one parent.
+						 */
+						if (numParents != 1)
+						{
+							pg_log_error("Invalid number of parents %d for table %s\n", numParents, relname);
+							result = false;
+						}
+
+						appendPQExpBuffer(buf, " PARTITION OF %s", PQgetvalue(parres, 0, 0));
+					}
+
+					/*
+					 * adding column info
+					 */
+
+					for (int column = 0; column < relnatts; column++)
+					{
+						char	   *column_name = PQgetvalue(colres, column, 0);
+						char	   *data_type = PQgetvalue(colres, column, 1);
+						char	   *collation_name = PQgetvalue(colres, column, 2);
+						char	   *not_null = PQgetvalue(colres, column, 3);
+						char	   *has_default = PQgetvalue(colres, column, 4);
+						char	   *default_value = PQgetvalue(colres, column, 5);
+
+						if (column == 0)
+							appendPQExpBuffer(buf, " (\n    ");
+						else
+							appendPQExpBuffer(buf, ",\n    ");
+						appendPQExpBuffer(buf, "%s", column_name);
+						if (strcmp(reloftype, "0") == 0)
+							appendPQExpBuffer(buf, " %s", data_type);
+
+						/*
+						 * check if not null
+						 */
+						if (strcmp(not_null, "t") == 0)
+							appendPQExpBuffer(buf, " NOT NULL");
+
+						/*
+						 * check for default value
+						 */
+						if (strcmp(has_default, "t") == 0)
+							appendPQExpBuffer(buf, " DEFAULT %s", default_value);
+						if (strcmp(collation_name, "default") != 0 && !PQgetisnull(colres, column, 2))
+							appendPQExpBuffer(buf, " COLLATE %s", collation_name);
+					}
+
+					/*
+					 * add constraints, if any
+					 */
+					if (PQntuples(conres))
+					{
+						appendPQExpBufferStr(buf, ",\n");
+					}
+					else
+					{
+						appendPQExpBufferStr(buf, "\n");
+					}
+					for (int i = 0; i < PQntuples(conres); i++)
+					{
+						char	   *constraint_name = PQgetvalue(conres, i, 0);
+						char	   *constraint_def = PQgetvalue(conres, i, 1);
+
+						appendPQExpBuffer(buf, "    CONSTRAINT %s %s", constraint_name, constraint_def);
+						if (i != PQntuples(conres) - 1)
+							appendPQExpBuffer(buf, ",");
+						appendPQExpBuffer(buf, "\n");
+					}
+					appendPQExpBufferStr(buf, " ) ");
+
+					/*
+					 * add patition bounds
+					 */
+					if (relispartition[0] == 't')
+					{
+						appendPQExpBuffer(buf, "%s", relpartbound);
+					}
+					appendPQExpBufferStr(buf, "\n");
+
+					/*
+					 * add parent info
+					 */
+					if (numParents > 0 && relispartition[0] == 'f')
+					{
+						appendPQExpBuffer(buf, "INHERITS ");
+						for (int i = 0; i < numParents; i++)
+						{
+							char	   *parent_name = PQgetvalue(parres, i, 0);
+							char	   *parent_namespace = PQgetvalue(parres, i, 1);
+
+							if (strcmp(nspname, parent_namespace) != 0)
+							{
+								appendPQExpBuffer(buf, "%s.", parent_namespace);
+							}
+							appendPQExpBuffer(buf, "%s", parent_name);
+							if (i != numParents - 1)
+							{
+								appendPQExpBuffer(buf, ", ");
+							}
+						}
+						appendPQExpBuffer(buf, "\n");
+					}
+
+					/*
+					 * if partitioned, add definition
+					 */
+					if (relkind[0] == 'p')
+					{
+						appendPQExpBuffer(buf, "PARTITION BY %s\n", partkeydef);
+					}
+
+					/*
+					 * add access method
+					 */
+					if (!PQgetisnull(res, 0, 12))
+					{
+						appendPQExpBuffer(buf, "USING %s\n", amname);
+					}
+
+					/*
+					 * reloptions, if not an empty array "{}"
+					 */
+					if (reloptions != NULL && strlen(reloptions) > 2)
+					{
+						appendPQExpBufferStr(buf, " WITH (");
+						if (!appendReloptionsArray(buf, reloptions, "",
+												   pset.encoding,
+												   standard_strings()))
+						{
+							pg_log_error("could not parse reloptions array\n");
+							result = false;
+						}
+						appendPQExpBufferStr(buf, ")\n");
+					}
+
+					if (!PQgetisnull(res, 0, 3))
+					{
+						appendPQExpBuffer(buf, " TABLESPACE %s\n", spcname);
+					}
+
+					PQclear(colres);
+					PQclear(conres);
+					PQclear(parres);
+
+					destroyPQExpBuffer(column_info);
+					destroyPQExpBuffer(constraint_info);
+					destroyPQExpBuffer(parent_info);
+
+				}
+				break;
 		}
+
 		/* Make sure result ends with a newline */
 		if (buf->len > 0 && buf->data[buf->len - 1] != '\n')
 			appendPQExpBufferChar(buf, '\n');
@@ -5005,6 +5844,7 @@ get_create_object_cmd(EditableObjectType obj_type, Oid oid,
 	return result;
 }
 
+
 /*
  * If the given argument of \ef or \ev ends with a line number, delete the line
  * number from the argument string and return it as an integer.  (We need
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index eb018854a5c..186a1cf2efa 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1491,7 +1491,7 @@ psql_completion(const char *text, int start, int end)
 		"\\p", "\\password", "\\prompt", "\\pset",
 		"\\q", "\\qecho",
 		"\\r",
-		"\\s", "\\set", "\\setenv", "\\sf", "\\sv",
+		"\\s", "\\set", "\\setenv", "\\sf", "\\sv", "\\si", "\\sm", "\\sr", "\\st",
 		"\\t", "\\T", "\\timing",
 		"\\unset",
 		"\\x",
@@ -3890,6 +3890,21 @@ psql_completion(const char *text, int start, int end)
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_routines, NULL);
 	else if (TailMatchesCS("\\sv*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views, NULL);
+	else if (TailMatchesCS("\\si*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes, NULL);
+	else if (TailMatchesCS("\\sm*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL);
+	else if (TailMatchesCS("\\sr*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
+	else if (TailMatchesCS("\\st*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
+	else if (MatchesCS("\\st*", MatchAny))
+		COMPLETE_WITH("TRIGGER");
+	else if (MatchesCS("\\st*", MatchAny, "TRIGGER"))
+	{
+		completion_info_charp = prev2_wd;
+		COMPLETE_WITH_QUERY(Query_for_trigger_of_table);
+	}
 	else if (TailMatchesCS("\\cd|\\e|\\edit|\\g|\\gx|\\i|\\include|"
 						   "\\ir|\\include_relative|\\o|\\out|"
 						   "\\s|\\w|\\write|\\lo_import"))
diff --git a/src/test/regress/expected/si_st_sm_sr.out b/src/test/regress/expected/si_st_sm_sr.out
new file mode 100644
index 00000000000..7934d273089
--- /dev/null
+++ b/src/test/regress/expected/si_st_sm_sr.out
@@ -0,0 +1,156 @@
+--
+-- SI_ST_SM_SR
+-- Test cases for recreating CREATE commands
+--
+CREATE TABLE smtest (id int NOT NULL PRIMARY KEY, type text NOT NULL, amt numeric NOT NULL);
+CREATE VIEW smtestv AS SELECT type, sum(amt) AS totamt FROM smtest GROUP BY type;
+CREATE MATERIALIZED VIEW smtestm AS SELECT type, sum(amt) AS totamt FROM smtest GROUP BY type WITH NO DATA;
+CREATE MATERIALIZED VIEW smtestvm AS SELECT * FROM smtestv ORDER BY type;
+\sm smtestm
+CREATE MATERIALIZED VIEW smtestm
+    USING heap
+    TABLESPACE public
+    AS
+    SELECT smtest.type,
+    sum(smtest.amt) AS totamt
+   FROM smtest
+  GROUP BY smtest.type;
+\sm smtestvm
+CREATE MATERIALIZED VIEW smtestvm
+    USING heap
+    TABLESPACE public
+    AS
+    SELECT smtestv.type,
+    smtestv.totamt
+   FROM smtestv
+  ORDER BY smtestv.type;
+DROP TABLE smtest CASCADE;
+NOTICE:  drop cascades to 3 other objects
+DETAIL:  drop cascades to view smtestv
+drop cascades to materialized view smtestvm
+drop cascades to materialized view smtestm
+create table pkeys (pkey1 int4 not null, pkey2 text not null);
+create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
+create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
+create trigger check_fkeys_pkey_exist
+	before insert or update on fkeys
+	for each row
+	execute function
+	check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
+create trigger check_fkeys2_pkey_exist
+	before insert or update on fkeys2
+	for each row
+	execute procedure
+	check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
+create trigger check_fkeys2_pkey_exist
+	before insert or update on fkeys
+	for each row
+	execute procedure
+	check_primary_key ('fkey21', 'fkey22');
+\st fkeys TRIGGER check_fkeys_pkey_exist
+CREATE TRIGGER check_fkeys_pkey_exist BEFORE INSERT OR UPDATE ON public.fkeys FOR EACH ROW EXECUTE FUNCTION check_primary_key('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2')
+\st fkeys2 TRIGGER check_fkeys2_pkey_exist
+CREATE TRIGGER check_fkeys2_pkey_exist BEFORE INSERT OR UPDATE ON public.fkeys2 FOR EACH ROW EXECUTE FUNCTION check_primary_key('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2')
+\st fkeys TRIGGER check_fkeys2_pkey_exist
+CREATE TRIGGER check_fkeys2_pkey_exist BEFORE INSERT OR UPDATE ON public.fkeys FOR EACH ROW EXECUTE FUNCTION check_primary_key('fkey21', 'fkey22')
+DROP TABLE pkeys;
+DROP TABLE fkeys;
+DROP TABLE fkeys2;
+create table idxtable (a int, b int, c text);
+create index idx on idxtable using hash (a);
+create index idx2 on idxtable (c COLLATE "POSIX");
+\si idx
+CREATE INDEX idx ON public.idxtable USING hash (a)
+\si idx2
+CREATE INDEX idx2 ON public.idxtable USING btree (c COLLATE "POSIX")
+drop index idx;
+drop index idx2;
+drop table idxtable;
+CREATE TABLE collate_test (
+    a int,
+    b text COLLATE "C" NOT NULL
+);
+\sr collate_test
+CREATE TABLE public.collate_test (
+    a integer,
+    b text NOT NULL COLLATE C
+ ) 
+DROP TABLE collate_test;
+CREATE TABLE ptif_test (a int, b int) PARTITION BY range (a);
+CREATE TABLE ptif_test0 PARTITION OF ptif_test
+  FOR VALUES FROM (minvalue) TO (0) PARTITION BY list (b);
+\sr ptif_test
+CREATE TABLE public.ptif_test (
+    a integer,
+    b integer
+ ) 
+PARTITION BY RANGE (a)
+\sr ptif_test0
+CREATE TABLE public.ptif_test0 PARTITION OF ptif_test (
+    a integer,
+    b integer
+ ) FOR VALUES FROM (MINVALUE) TO (0)
+PARTITION BY LIST (b)
+DROP TABLE ptif_test0;
+DROP TABLE ptif_test;
+CREATE TABLE srtest0 (aa TEXT);
+CREATE TABLE srtest1 (bb TEXT) INHERITS (srtest0);
+CREATE TABLE srtest2 (cc TEXT) INHERITS (srtest0);
+CREATE TABLE srtest3 (dd TEXT) INHERITS (srtest1, srtest2, srtest0);
+NOTICE:  merging multiple inherited definitions of column "aa"
+NOTICE:  merging multiple inherited definitions of column "aa"
+\sr srtest0
+CREATE TABLE public.srtest0 (
+    aa text
+ ) 
+\sr srtest1
+CREATE TABLE public.srtest1 (
+    aa text,
+    bb text
+ ) 
+INHERITS srtest0
+\sr srtest2
+CREATE TABLE public.srtest2 (
+    aa text,
+    cc text
+ ) 
+INHERITS srtest0
+\sr srtest3
+CREATE TABLE public.srtest3 (
+    aa text,
+    bb text,
+    cc text,
+    dd text
+ ) 
+INHERITS srtest1, srtest2, srtest0
+DROP TABLE srtest0 CASCADE;
+NOTICE:  drop cascades to 3 other objects
+DETAIL:  drop cascades to table srtest1
+drop cascades to table srtest2
+drop cascades to table srtest3
+CREATE TABLE srtest4 (id int, name text) WITH (fillfactor=10);
+\sr srtest4
+CREATE TABLE public.srtest4 (
+    id integer,
+    name text
+ ) 
+ WITH (fillfactor='10')
+DROP TABLE srtest4;
+CREATE TABLE constraint_test(
+   ID INT PRIMARY KEY     NOT NULL,
+   NAME           TEXT    NOT NULL,
+   AGE            INT     NOT NULL UNIQUE,
+   ADDRESS        CHAR(50),
+   SALARY         REAL    DEFAULT 50000.00
+);
+\sr constraint_test
+CREATE TABLE public.constraint_test (
+    id integer NOT NULL,
+    name text NOT NULL,
+    age integer NOT NULL,
+    address character,
+    salary real DEFAULT 50000.00,
+    CONSTRAINT constraint_test_age_key UNIQUE (age),
+    CONSTRAINT constraint_test_pkey PRIMARY KEY (id)
+ ) 
+DROP TABLE constraint_test;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 026ea880cde..3ffa4ead18d 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -121,3 +121,4 @@ test: fast_default
 
 # run stats by itself because its delay may be insufficient under heavy load
 test: stats
+test: si_st_sm_sr
\ No newline at end of file
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 979d9261197..11c21ffbdaf 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -201,3 +201,4 @@ test: explain
 test: event_trigger
 test: fast_default
 test: stats
+test: si_st_sm_sr
\ No newline at end of file
diff --git a/src/test/regress/sql/si_st_sm_sr.sql b/src/test/regress/sql/si_st_sm_sr.sql
new file mode 100644
index 00000000000..fa3272d4ebb
--- /dev/null
+++ b/src/test/regress/sql/si_st_sm_sr.sql
@@ -0,0 +1,83 @@
+--
+-- SI_ST_SM_SR
+-- Test cases for recreating CREATE commands
+--
+
+CREATE TABLE smtest (id int NOT NULL PRIMARY KEY, type text NOT NULL, amt numeric NOT NULL);
+CREATE VIEW smtestv AS SELECT type, sum(amt) AS totamt FROM smtest GROUP BY type;
+CREATE MATERIALIZED VIEW smtestm AS SELECT type, sum(amt) AS totamt FROM smtest GROUP BY type WITH NO DATA;
+CREATE MATERIALIZED VIEW smtestvm AS SELECT * FROM smtestv ORDER BY type;
+\sm smtestm
+\sm smtestvm
+DROP TABLE smtest CASCADE;
+
+create table pkeys (pkey1 int4 not null, pkey2 text not null);
+create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
+create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
+
+create trigger check_fkeys_pkey_exist
+	before insert or update on fkeys
+	for each row
+	execute function
+	check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
+create trigger check_fkeys2_pkey_exist
+	before insert or update on fkeys2
+	for each row
+	execute procedure
+	check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
+create trigger check_fkeys2_pkey_exist
+	before insert or update on fkeys
+	for each row
+	execute procedure
+	check_primary_key ('fkey21', 'fkey22');
+\st fkeys TRIGGER check_fkeys_pkey_exist
+\st fkeys2 TRIGGER check_fkeys2_pkey_exist
+\st fkeys TRIGGER check_fkeys2_pkey_exist
+DROP TABLE pkeys;
+DROP TABLE fkeys;
+DROP TABLE fkeys2;
+create table idxtable (a int, b int, c text);
+create index idx on idxtable using hash (a);
+create index idx2 on idxtable (c COLLATE "POSIX");
+\si idx
+\si idx2
+drop index idx;
+drop index idx2;
+drop table idxtable;
+
+CREATE TABLE collate_test (
+    a int,
+    b text COLLATE "C" NOT NULL
+);
+\sr collate_test
+DROP TABLE collate_test;
+CREATE TABLE ptif_test (a int, b int) PARTITION BY range (a);
+CREATE TABLE ptif_test0 PARTITION OF ptif_test
+  FOR VALUES FROM (minvalue) TO (0) PARTITION BY list (b);
+\sr ptif_test
+\sr ptif_test0
+DROP TABLE ptif_test0;
+DROP TABLE ptif_test;
+
+CREATE TABLE srtest0 (aa TEXT);
+CREATE TABLE srtest1 (bb TEXT) INHERITS (srtest0);
+CREATE TABLE srtest2 (cc TEXT) INHERITS (srtest0);
+CREATE TABLE srtest3 (dd TEXT) INHERITS (srtest1, srtest2, srtest0);
+\sr srtest0
+\sr srtest1
+\sr srtest2
+\sr srtest3
+DROP TABLE srtest0 CASCADE;
+CREATE TABLE srtest4 (id int, name text) WITH (fillfactor=10);
+\sr srtest4
+DROP TABLE srtest4;
+
+CREATE TABLE constraint_test(
+   ID INT PRIMARY KEY     NOT NULL,
+   NAME           TEXT    NOT NULL,
+   AGE            INT     NOT NULL UNIQUE,
+   ADDRESS        CHAR(50),
+   SALARY         REAL    DEFAULT 50000.00
+);
+\sr constraint_test
+DROP TABLE constraint_test;