sampling_context.sql

/**********************************************************************************************
**  Sampling Context - Tri-gram Search Objects
**  Generated from template by generate_entity_schema.py
**  
**  Note: This file creates the base view WITHOUT embeddings.
**        If the entity has embeddings, also install semantic-{entity}.sql to create
**        the embeddings table and semantic search functions.
**********************************************************************************************/
drop view if exists authority.sampling_context cascade;

create or replace view authority.sampling_context as  select
    t.sampling_context_id,
    t.sampling_context as label,
    authority.immutable_unaccent(lower(t.sampling_context)) as norm_label,
    t.description  from public.tbl_sample_group_sampling_contexts as t;
create index if not exists tbl_sample_group_sampling_contexts_norm_trgm
  on public.tbl_sample_group_sampling_contexts
    using gin ( (authority.immutable_unaccent(lower(sampling_context))) gin_trgm_ops );
/***************************************************************************************************
 ** Procedure  authority.fuzzy_sampling_context
 ** What       Trigram fuzzy search function using pg_trgm similarity
 ** Usage      SELECT * FROM authority.fuzzy_sampling_context('query text', 10); ****************************************************************************************************/

drop function if exists authority.fuzzy_sampling_context(text, integer) cascade;

create or replace function authority.fuzzy_sampling_context(
  p_text text,
  p_limit integer default 10) returns table (
  sampling_context_id integer,
  label text,
  name_sim double precision
) language sql stable
as $$
  with params as (
    select authority.immutable_unaccent(lower(p_text))::text as q
  )  select
    s.sampling_context_id,
    s.label,
    greatest(
      case when s.norm_label = pq.q then 1.0
          else similarity(s.norm_label, pq.q)
      end, 0.0001
    ) as name_sim
  from authority.sampling_context as s
  cross join params pq  where s.norm_label % pq.q  order by name_sim desc, s.label
  limit p_limit;
$$;