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" <alvhe...@2ndquadrant.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >