将postgresql扩展到索引json对象的最佳方法是什么? [英] what is best way to extend postgresql to index json objects?

问题描述

postgres 9.2支持json列.什么是将postgres扩展为基于json列自动索引的最佳方法.

postgres 9.2 supports json columns. what would be best way to extend postgres to automatically index based on a json colum.

例如，

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 );

在插入或更新记录时，从obj列中设置col1和col2.

when the record is inserted or updated, col1 and col2 are set from the obj column.

基本上，规则是，除了主键和json列本身之外，任何属性都是从json对象自动设置的.

essentially the rule is that any attribute, aside from primary key and json column itself, are automatically set from json object.

作为别名，重命名col1也会更新json属性，将col1重命名为新名称.

as a correllary, renaming col1 would also update the json attribute, renaming col1 there to the new name.

可以使用catalogue和execute语句来完成，但会变得很慢.

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.

是extension的最佳选择吗?甚至会是什么样?

is an extension the best way to go? what would it even look like?

最终无法编写extension来执行此操作，因为pg不支持CREATE TABLE或ALTER TABLE上的触发器.

ends up cannot write extension to do this since pg does not support triggers on CREATE TABLE or ALTER TABLE.

唯一的建议是记录DDL日志，监视日志，然后运行一个过程.虽然可以做到，但我们真正需要的是CREATE和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.

推荐答案

我不清楚您是如何从json派生"col1"的.大概取决于您的json.

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

无论如何-最好的方法是编写一个脚本，该脚本为您编写触发器，而另一个脚本为您编写column-rename操作.您提供必要的任何参数，它会吐出plpgsql代码.

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.

哦-您可能会发现 http://pgtap.org/也很有用-您将要测试所有这个.

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

这篇关于将postgresql扩展到索引json对象的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！