Re: Add CREATE support to event triggers

Hello

I don't like this direction. What we can do with JSON from plpgsql? More,
JSON is not too robust format against some future changes.

Regards

Pavel
Dne 8.1.2014 21:43 "Alvaro Herrera" <alvherre(at)2ndquadrant(dot)com> napsal(a):

> Alvaro Herrera escribió:
> > Robert Haas escribió:
> >
> > > I think this direction has some potential. I'm not sure it's right in
> > > detail. The exact scheme you propose above won't work if you want to
> > > leave out the schema name altogether, and more generally it's not
> > > going to help very much with anything other than substituting in
> > > identifiers. What if you want to add a column called satellite_id to
> > > every table that gets created, for example? What if you want to make
> > > the tables UNLOGGED? I don't see how that kind of things is going to
> > > work at all cleanly.
> >
> > Thanks for the discussion. I am building some basic infrastructure to
> > make this possible, and will explore ideas to cover these oversights
> > (not posting anything concrete yet because I expect several iterations
> > to crash and burn before I have something sensible to post).
>
> Here's a working example. Suppose the user runs
>
> CREATE SCHEMA IF NOT EXISTS "some schema" AUTHORIZATION "some guy";
>
> In an event trigger, the function pg_event_trigger_get_creation_commands()
> returns the following JSON blob:
>
> {"authorization":{"authorization_role":"some guy",
> "output":"AUTHORIZATION %i{authorization_role}"},
> "if_not_exists":"IF NOT EXISTS",
> "name":"some schema",
> "output":"CREATE SCHEMA %{if_not_exists} %i{name} %{authorization}"}
>
> wherein I have chosen to have a JSON element with the hardcoded name of
> "output" which is what needs to be expanded; for each %{} parameter
> found in it, there is an equally-named element in the JSON blob. This
> can be a string, a NULL, or another JSON object.
>
> If it's a string, it expands to that value; if it's an object,
> recursively an "output" element is expanded in the same way, and the
> expanded string is used.
>
> If there's a NULL element when expanding an object, the whole thing
> expands to empty. For example, if no AUTHORIZATION
> clause is specified, "authorization" element is still there, but the
> "authorization_role" element within it is NULL, and so the whole
> AUTHORIZATION clause expands to empty and the resulting command contains
> no authorization clause. This is useful to support the case that
> someone doesn't have an AUTHORIZATION clause in the CREATE SCHEMA
> command, and the event trigger injects one simply by setting the
> authorization_role to some role name.
>
> IF NOT EXISTS is handled by defining it to either the string IF NOT
> EXISTS or to empty if no such clause was specified.
>
> The user can modify elements in the JSON to get a different version of
> the command. (I reckon the "output" can also be modified, but this is
> probably a bad idea in most/all cases. I don't think there's a need to
> prohibit this explicitely.) Also, someone might define "if_not_exists"
> to something completely unrelated, but that would be their own fault.
> (Maybe we can have some cross-check that the if_not_exists element in
> JSON cannot be anything other than "IF NOT EXISTS" or the empty string;
> and that the "output" element remains the same at expansion time than it
> was at generation time. Perhaps we should even hide the "output"
> element from the user completely and only add them to the JSON at time
> of expansion. Not sure it's worth the trouble.)
>
> There is another function,
> pg_event_trigger_expand_creation_command(json), which will expand the
> above JSON blob and return the following text:
>
> CREATE SCHEMA IF NOT EXISTS "some schema" AUTHORIZATION "some guy"
>
> Note the identifiers are properly quoted (there are quotes in the JSON
> blob, but they correspond to JSON's own delimiters). I have defined a
> 'i' modifier to have %i{} elements, which means that the element is an
> identifier which might need quoting.
>
> I have also defined a %d{} modifier that means to use the element to
> expand a possibly-qualified dotted name. (There would be no "output"
> element in this case.) This is to support the case where you have
>
> CREATE TABLE public.foo
> which results in
> {"table_name":{"schema":"public",
> "relname":"foo"}}
>
> and you want to edit the "table_name" element in the root JSON and set
> the schema to something else (perhaps NULL), so in the event trigger
> after expansion you can end up with "CREATE TABLE foo" or "CREATE TABLE
> private.foo" or whatever.
>
> Most likely there are some more rules that will need to be created, but
> so far this looks sensible.
>
> I'm going to play some more with the %d{} stuff, and also with the idea
> of representing table elements such as columns and constraints as an
> array. In the meantime please let me know whether this makes sense.
>
> --
> Álvaro Herrera http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>