with index as ( select setweight(to_tsvector(coalesce(:title,'')),'A') ||setweight(to_tsvector(coalesce(:keywords,'')),'B') ||to_tsvector(coalesce(:txt,'')) as fti from dual ), insert as ( insert into txt (object_id, fti) select o.object_id, i.fti from acs_objects o, index i where object_id = :object_id and not exists (select 1 from txt where object_id = o.object_id) ) update txt set fti = (select fti from index) where object_id = :object_id postgresql8.4 where fti @@ to_tsquery(:query) postgresql8.3 select txt.object_id from [join $from_clauses ","] $base_query [expr {[llength $where_clauses] > 0 ? " and " : ""}] [join $where_clauses " and "] order by ts_rank(fti,to_tsquery(:query)) desc $limit_clause $offset_clause postgresql8.4 select distinct(orig_object_id) from acs_permission.permission_p_recursive_array(array( select txt.object_id from [join $from_clauses ","] $base_query [expr {[llength $where_clauses] > 0 ? " and [join $where_clauses { and }]" : ""}] order by ts_rank(fti,to_tsquery(:query)) desc ), :user_id, 'read') $limit_clause $offset_clause postgresql8.3 select count(*) from [join $from_clauses ","] $base_query [expr {[llength $where_clauses] > 0 ? " and [join $where_clauses { and }]" : ""}] postgresql8.4 select count(distinct(orig_object_id)) from acs_permission.permission_p_recursive_array(array( select txt.object_id from [join $from_clauses ","] $base_query [expr {[llength $where_clauses] > 0 ? " and " : ""}] [join $where_clauses " and "] ), :user_id, 'read') postgresql8.3 select ts_headline(:txt,to_tsquery(:query)) postgresql8.2 select count(*) from [join $from_clauses ","] $base_query [expr {[llength $where_clauses] > 0 ? " and " : ""}] [join $where_clauses " and "]