[Looking back over old threads]
On 22 July 2015 at 22:00, Dean Rasheed <[email protected]> wrote:
> This appears to be missing support for view options (WITH CHECK OPTION
> and security_barrier), so editing a view with either of those options
> will cause them to be stripped off.
It seems like this issue was never addressed, and it needs to be fixed for 9.6.
Here is a rough patch based on the way pg_dump handles this. It still
needs a bit of polishing -- in particular I think fmtReloptionsArray()
(copied from pg_dump) should probably be moved to string_utils.c so
that it can be shared between pg_dump and psql. Also, I'm not sure
that's the best name for it -- I think appendReloptionsArray() is a
more accurate description of what is does.
Regards,
Dean
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
new file mode 100644
index 4fa7760..96bc64d
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -72,6 +72,7 @@ static bool lookup_object_oid(EditableOb
Oid *obj_oid);
static bool get_create_object_cmd(EditableObjectType obj_type, Oid oid,
PQExpBuffer buf);
+static bool fmtReloptionsArray(PQExpBuffer buffer, const char *reloptions);
static int strip_lineno_from_objdesc(char *obj);
static int count_lines_in_buf(PQExpBuffer buf);
static void print_with_linenumbers(FILE *output, char *lines,
@@ -3274,12 +3275,51 @@ get_create_object_cmd(EditableObjectType
* CREATE for ourselves. We must fully qualify the view name to
* ensure the right view gets replaced. Also, check relation kind
* to be sure it's a view.
+ *
+ * Starting with 9.2, views may have reloptions (security_barrier)
+ * and from 9.4 onwards they may also have WITH [LOCAL|CASCADED]
+ * CHECK OPTION. These are not part of the view definition
+ * returned by pg_get_viewdef() and so need to be retrieved
+ * separately. Materialized views (introduced in 9.3) may have
+ * arbitrary storage parameter reloptions.
*/
- printfPQExpBuffer(query,
- "SELECT nspname, relname, relkind, pg_catalog.pg_get_viewdef(c.oid, true) FROM "
- "pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n "
- "ON c.relnamespace = n.oid WHERE c.oid = %u",
- oid);
+ if (pset.sversion >= 90400)
+ {
+ printfPQExpBuffer(query,
+ "SELECT nspname, relname, relkind, "
+ "pg_catalog.pg_get_viewdef(c.oid, true), "
+ "array_remove(array_remove(c.reloptions,'check_option=local'),'check_option=cascaded') AS reloptions, "
+ "CASE WHEN 'check_option=local' = ANY (c.reloptions) THEN 'LOCAL'::text "
+ "WHEN 'check_option=cascaded' = ANY (c.reloptions) THEN 'CASCADED'::text ELSE NULL END AS checkoption "
+ "FROM pg_catalog.pg_class c "
+ "LEFT JOIN pg_catalog.pg_namespace n "
+ "ON c.relnamespace = n.oid WHERE c.oid = %u",
+ oid);
+ }
+ else if (pset.sversion >= 90200)
+ {
+ printfPQExpBuffer(query,
+ "SELECT nspname, relname, relkind, "
+ "pg_catalog.pg_get_viewdef(c.oid, true), "
+ "c.reloptions AS reloptions, "
+ "NULL AS checkoption "
+ "FROM pg_catalog.pg_class c "
+ "LEFT JOIN pg_catalog.pg_namespace n "
+ "ON c.relnamespace = n.oid WHERE c.oid = %u",
+ oid);
+ }
+ else
+ {
+ printfPQExpBuffer(query,
+ "SELECT nspname, relname, relkind, "
+ "pg_catalog.pg_get_viewdef(c.oid, true), "
+ "NULL AS reloptions, "
+ "NULL AS checkoption "
+ "FROM pg_catalog.pg_class c "
+ "LEFT JOIN pg_catalog.pg_namespace n "
+ "ON c.relnamespace = n.oid WHERE c.oid = %u",
+ oid);
+ }
break;
}
@@ -3304,6 +3344,8 @@ get_create_object_cmd(EditableObjectType
char *relname = PQgetvalue(res, 0, 1);
char *relkind = PQgetvalue(res, 0, 2);
char *viewdef = PQgetvalue(res, 0, 3);
+ char *reloptions = PQgetvalue(res, 0, 4);
+ char *checkoption = PQgetvalue(res, 0, 5);
/*
* If the backend ever supports CREATE OR REPLACE
@@ -3328,11 +3370,30 @@ get_create_object_cmd(EditableObjectType
break;
}
appendPQExpBuffer(buf, "%s.", fmtId(nspname));
- appendPQExpBuffer(buf, "%s AS\n", fmtId(relname));
- appendPQExpBufferStr(buf, viewdef);
+ appendPQExpBufferStr(buf, fmtId(relname));
+
+ /* reloptions, if not an empty array "{}" */
+ if (reloptions != NULL && strlen(reloptions) > 2)
+ {
+ appendPQExpBufferStr(buf, "\n WITH (");
+ if (!fmtReloptionsArray(buf, reloptions))
+ {
+ psql_error("Could not parse reloptions array\n");
+ result = false;
+ }
+ appendPQExpBufferStr(buf, ")");
+ }
+
+ /* View definition from pg_get_viewdef (a SELECT query) */
+ appendPQExpBuffer(buf, " AS\n%s", viewdef);
+
/* Get rid of the semicolon that pg_get_viewdef appends */
if (buf->len > 0 && buf->data[buf->len - 1] == ';')
buf->data[--(buf->len)] = '\0';
+
+ /* WITH [LOCAL|CASCADED] CHECK OPTION */
+ if (checkoption && checkoption[0] != '\0')
+ appendPQExpBuffer(buf, "\n WITH %s CHECK OPTION", checkoption);
}
break;
}
@@ -3353,6 +3414,74 @@ get_create_object_cmd(EditableObjectType
}
/*
+ * Format a reloptions array and append it to the given buffer.
+ *
+ * Note: this logic should generally match the backend's flatten_reloptions()
+ * (in adt/ruleutils.c).
+ *
+ * Returns false if the reloptions array could not be parsed (in which case
+ * nothing will have been appended to the buffer), or true on success.
+ */
+static bool
+fmtReloptionsArray(PQExpBuffer buffer, const char *reloptions)
+{
+ char **options;
+ int noptions;
+ int i;
+
+ if (!parsePGArray(reloptions, &options, &noptions))
+ {
+ if (options)
+ free(options);
+ return false;
+ }
+
+ for (i = 0; i < noptions; i++)
+ {
+ char *option = options[i];
+ char *name;
+ char *separator;
+ char *value;
+
+ /*
+ * Each array element should have the form name=value. If the "=" is
+ * missing for some reason, treat it like an empty value.
+ */
+ name = option;
+ separator = strchr(option, '=');
+ if (separator)
+ {
+ *separator = '\0';
+ value = separator + 1;
+ }
+ else
+ value = "";
+
+ if (i > 0)
+ appendPQExpBufferStr(buffer, ", ");
+ appendPQExpBuffer(buffer, "%s=", fmtId(name));
+
+ /*
+ * In general we need to quote the value; but to avoid unnecessary
+ * clutter, do not quote if it is an identifier that would not need
+ * quoting. (We could also allow numbers, but that is a bit trickier
+ * than it looks --- for example, are leading zeroes significant? We
+ * don't want to assume very much here about what custom reloptions
+ * might mean.)
+ */
+ if (strcmp(fmtId(value), value) == 0)
+ appendPQExpBufferStr(buffer, value);
+ else
+ appendStringLiteralConn(buffer, value, pset.db);
+ }
+
+ if (options)
+ free(options);
+
+ return true;
+}
+
+/*
* 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
* this kluge because we're too lazy to parse \ef's function or \ev's view
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
