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 "]