[Looking back over old threads] On 22 July 2015 at 22:00, Dean Rasheed <dean.a.rash...@gmail.com> 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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers