what is best way to extend postgresql to index json objects?

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.


It's not clear to me how you derive "col1" from your json. Presumably it depends on your json.

Anyway - the best approach is to write a script that writes your triggers for you, and another that writes the column-rename operation for you. You supply whatever parameters are necessary and it spits out plpgsql code.

Oh - you may find http://pgtap.org/ useful too - you'll want to test all this.

Need Your Help

PHP - get base64 img string decode and save as jpg (resulting empty image )

php image codeigniter save base64

hi i'm actually sending a base64 image string trough ajax to a php script which just decodes string and save content as .jpg file.

get rss feeds for weather forecast


I am trying to get weather forecast details for 3 days for user location. I cant find any content providers providing weather forecast details with weather code. So, anyone please suggest me which ...