Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- select
- o.id,
- o.dbid,
- o.type,
- o.name,
- o.display_name,
- o.short_label,
- o.full_label,
- o.description,
- o.data_source_description,
- o.sourcelink,
- o.query,
- o.all_words,
- o.rank,
- array_agg(distinct(o.words)) words
- from (
- select
- i.id,
- i.dbid,
- i.type,
- i.name,
- i.display_name,
- i.short_label,
- i.full_label,
- i.description,
- i.data_source_description,
- i.sourcelink,
- i.query,
- i.all_words,
- i.rank,
- (regexp_matches(ts_headline(i.all_words, i.query, 'HighlightAll=true'), '<b>(.*?)</b>', 'g'))[1] words
- from (
- select
- case
- when column_id is not null then column_id
- when dataset_id is not null then dataset_id
- when set_id is not null then set_id
- end as id
- , id as dbid
- , type
- , coalesce (name, '') as name
- , coalesce (display_name, '') as display_name
- , coalesce (short_label, '') as short_label
- , coalesce (full_label, '') as full_label
- , coalesce (description, '') as description
- , coalesce (data_source_description, '') as data_source_description
- , coalesce (link, '') as sourcelink
- , query
- , all_words
- , ts_rank_cd (search, query) as rank
- from
- dataset_search ds
- , to_tsquery (:query) query
- where
- search @@ query
- and product_ids && :product_id
- and group_ids && :group_ids
- and type = any(:types)
- order by
- rank desc
- limit :limit
- offset :offset
- ) i
- ) o
- group by
- o.id,
- o.dbid,
- o.type,
- o.name,
- o.display_name,
- o.short_label,
- o.full_label,
- o.description,
- o.data_source_description,
- o.sourcelink,
- o.query,
- o.all_words,
- o.rank
- order by
- o.rank desc
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement