postgres 9.2 supports json columns. what would be best way to extend postgres to automatically index based on a json colum.
for example,
create table obj(
obj_id bigserial primary key,
col1 varchar(20),
col2 date,
obj json
);
create index obj_col1 on obj( col1 );
create index obj_col2 on obj( col2 );
when the record is inserted or updated, col1 and col2 are set from the obj column.
essentially the rule is that any attribute, aside from primary key and json column itself, are automatically set from json object.
as a correllary, renaming col1 would also update the json attribute, renaming col1 there to the new name.
could be done using catalogue and execute statements, but would be slow as sin.
could be done by creating triggers for each table, but that's both tedious and error prone.
cannot be accomplished through inheritance, since triggers cannot be inherited.
is an extension the best way to go? what would it even look like?
ends up cannot write extension to do this since pg does not support triggers on CREATE TABLE or ALTER TABLE.
the only suggestion for a kludge would be log for DDL, monitor the log, and then run a procedure. while this can be done, what we really need are triggers on CREATE and ALTER TABLE.
