Hi,
I've attached a patch that implements \si, \sm, \st and \sr functions
that show the CREATE command for indexes, matviews, triggers and tables.
The functions are implemented similarly to the existing sf/sv functions
with some modifications.
For triggers, I've decided to change input format to "table_name TRIGGER
trigger_name", as multiple tables are allowed to have a trigger of the
same name. Because we need to verify not only the name of the trigger,
but also the name of the table, I've implemented a separate function
lookup_trigger_oid that takes an additional argument.
Triggers and indexes use pg_catalog.pg_get_triggerdef() and
pg_indexes.indexdef, while tables and matviews have separate queries for
reconstruction. Get_create_object_cmd also runs three additional queries
for tables, to get information on constraints, parents and columns.
There is also the question, if this functionality should be realised on
the server instead of the client, but some people may think that changes
to the language are "not the postgres way". However, server realisation
may have some advantages, such as independence from the client and
server version.
Best regards,
Alexandra Pervushina.
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;
