Re: Add CREATE support to event triggers

<p dir="ltr">Hello<p dir="ltr">I don't like this direction. What we can do with JSON from plpgsql? More, JSON is not
toorobust format against some future changes.<p dir="ltr">Regards<p dir="ltr">Pavel<div class="gmail_quote">Dne
8.1.201421:43 "Alvaro Herrera" <<a href="mailto:alvherre@2ndquadrant.com">alvherre@2ndquadrant.com</a>>
napsal(a):<brtype="attribution" /><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc
solid;padding-left:1ex">Alvaro Herrera escribió:<br /> > Robert Haas escribió:<br /> ><br /> > > I think
thisdirection has some potential.  I'm not sure it's right in<br /> > > detail.  The exact scheme you propose
abovewon't work if you want to<br /> > > leave out the schema name altogether, and more generally it's not<br />
>> going to help very much with anything other than substituting in<br /> > > identifiers.  What if you
wantto add a column called satellite_id to<br /> > > every table that gets created, for example?  What if you
wantto make<br /> > > the tables UNLOGGED?  I don't see how that kind of things is going to<br /> > > work
atall cleanly.<br /> ><br /> > Thanks for the discussion.  I am building some basic infrastructure to<br /> >
makethis possible, and will explore ideas to cover these oversights<br /> > (not posting anything concrete yet
becauseI expect several iterations<br /> > to crash and burn before I have something sensible to post).<br /><br />
Here'sa working example.  Suppose the user runs<br /><br /> CREATE SCHEMA IF NOT EXISTS "some schema" AUTHORIZATION
"someguy";<br /><br /> In an event trigger, the function pg_event_trigger_get_creation_commands()<br /> returns the
followingJSON blob:<br /><br /> {"authorization":{"authorization_role":"some guy",<br />                  
"output":"AUTHORIZATION%i{authorization_role}"},<br />  "if_not_exists":"IF NOT EXISTS",<br />  "name":"some
schema",<br/>  "output":"CREATE SCHEMA %{if_not_exists} %i{name} %{authorization}"}<br /><br /> wherein I have chosen
tohave a JSON element with the hardcoded name of<br /> "output" which is what needs to be expanded; for each %{}
parameter<br/> found in it, there is an equally-named element in the JSON blob.  This<br /> can be a string, a NULL, or
anotherJSON object.<br /><br /> If it's a string, it expands to that value; if it's an object,<br /> recursively an
"output"element is expanded in the same way, and the<br /> expanded string is used.<br /><br /> If there's a NULL
elementwhen expanding an object, the whole thing<br /> expands to empty.  For example, if no AUTHORIZATION<br /> clause
isspecified, "authorization" element is still there, but the<br /> "authorization_role" element within it is NULL, and
sothe whole<br /> AUTHORIZATION clause expands to empty and the resulting command contains<br /> no authorization
clause. This is useful to support the case that<br /> someone doesn't have an AUTHORIZATION clause in the CREATE
SCHEMA<br/> command, and the event trigger injects one simply by setting the<br /> authorization_role to some role
name.<br/><br /> IF NOT EXISTS is handled by defining it to either the string IF NOT<br /> EXISTS or to empty if no
suchclause was specified.<br /><br /> The user can modify elements in the JSON to get a different version of<br /> the
command. (I reckon the "output" can also be modified, but this is<br /> probably a bad idea in most/all cases.  I don't
thinkthere's a need to<br /> prohibit this explicitely.)  Also, someone might define "if_not_exists"<br /> to something
completelyunrelated, but that would be their own fault.<br /> (Maybe we can have some cross-check that the
if_not_existselement in<br /> JSON cannot be anything other than "IF NOT EXISTS" or the empty string;<br /> and that
the"output" element remains the same at expansion time than it<br /> was at generation time.  Perhaps we should even
hidethe "output"<br /> element from the user completely and only add them to the JSON at time<br /> of expansion.  Not
sureit's worth the trouble.)<br /><br /> There is another function,<br />
pg_event_trigger_expand_creation_command(json),which will expand the<br /> above JSON blob and return the following
text:<br/><br /> CREATE SCHEMA IF NOT EXISTS "some schema" AUTHORIZATION "some guy"<br /><br /> Note the identifiers
areproperly quoted (there are quotes in the JSON<br /> blob, but they correspond to JSON's own delimiters).  I have
defineda<br /> 'i' modifier to have %i{} elements, which means that the element is an<br /> identifier which might need
quoting.<br/><br /> I have also defined a %d{} modifier that means to use the element to<br /> expand a
possibly-qualifieddotted name.  (There would be no "output"<br /> element in this case.)  This is to support the case
whereyou have<br /><br /> CREATE TABLE public.foo<br /> which results in<br /> {"table_name":{"schema":"public",<br />
              "relname":"foo"}}<br /><br /> and you want to edit the "table_name" element in the root JSON and set<br
/>the schema to something else (perhaps NULL), so in the event trigger<br /> after expansion you can end up with
"CREATETABLE foo" or "CREATE TABLE<br /> private.foo" or whatever.<br /><br /> Most likely there are some more rules
thatwill need to be created, but<br /> so far this looks sensible.<br /><br /> I'm going to play some more with the
%d{}stuff, and also with the idea<br /> of representing table elements such as columns and constraints as an<br />
array. In the meantime please let me know whether this makes sense.<br /><br /> --<br /> Álvaro Herrera              
 <ahref="http://www.2ndQuadrant.com/" target="_blank">http://www.2ndQuadrant.com/</a><br /> PostgreSQL Development,
24x7Support, Training & Services<br /><br /><br /> --<br /> Sent via pgsql-hackers mailing list (<a
href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br/> To make changes to your
subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-hackers"
target="_blank">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/></blockquote></div>