You pretty much have the view trick. Its a set of dynamically generated rules for views created to allow inserts into the view which do cascaded inserts into the parent tables. It's not an entirely general solution to invoking arbitrary plsql but on the other hand it makes building up content types from metadata quite easy. The end result is anyone creating a content revision only has to do a normal insert statement.

Its all in acs-content-repository/sql/{postgresql,oracle}/content-type.sql and in particular content_type__create_type, content_type__create_attribute, content_type__refresh_view.

In addition the cms system has code for building forms and performing the necessary dml for adding and editing content. It needs a little extension work since it does not support multiple forms per content type but it does work reasonably well already. So if you actually supply all the metadata thats needed you don't end up writing any dml let alone plsql.

Some of the work I am doing now will expose that API so that packages using the CR but not CMS will actually be able to use the cms automatic form generation/dml but not the full blown CMS api.

I looked at query-writer but I didn't really understand it completely. It would be useful if you had more sample code somewhere (although I guess since the metadata is all in the DB that's not so straightforward as it sounds right?)