qwertyu0988 | 0.333333

(1 row)

select t <-> 'q0987wertyu0988', t from test_trgm order by t <-> 'q0987wertyu0988' limit 2;

?column? | t

----------+-------------

0.411765 | qwertyu0988

0.5 | qwertyu0987

(2 rows)

create index trgm_idx on test_trgm using gist (t gist_trgm_ops);

set enable_seqscan=off;

select t,similarity(t,'qwertyu0988') as sml from test_trgm where t % 'qwertyu0988' order by sml desc, t;

qwertyu0988 | 0.333333

(1 row)

explain (costs off)

select t <-> 'q0987wertyu0988', t from test_trgm order by t <-> 'q0987wertyu0988' limit 2;

QUERY PLAN

---------------------------------------------------

Limit

-> Index Scan using trgm_idx on test_trgm

Order By: (t <-> 'q0987wertyu0988'::text)

(3 rows)

select t <-> 'q0987wertyu0988', t from test_trgm order by t <-> 'q0987wertyu0988' limit 2;

?column? | t

----------+-------------

0.411765 | qwertyu0988

0.5 | qwertyu0987

(2 rows)

drop index trgm_idx;

create index trgm_idx on test_trgm using gin (t gin_trgm_ops);

set enable_seqscan=off;

CREATE OR REPLACE FUNCTION similarity_op(text,text)

RETURNS bool

AS 'MODULE_PATHNAME'

LANGUAGE C STRICT STABLE;

LANGUAGE C STRICT STABLE; -- stable because depends on trgm_limit

CREATE OPERATOR % (

LEFTARG = text,

JOIN = contjoinsel

);

CREATE OR REPLACE FUNCTION similarity_dist(text,text)

RETURNS float4

AS 'MODULE_PATHNAME'

LANGUAGE C STRICT IMMUTABLE;

CREATE OPERATOR <-> (

LEFTARG = text,

RIGHTARG = text,

PROCEDURE = similarity_dist,

COMMUTATOR = '<->'

);

-- gist key

CREATE OR REPLACE FUNCTION gtrgm_in(cstring)

RETURNS gtrgm

AS 'MODULE_PATHNAME'

LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION gtrgm_distance(internal,text,int,oid)

RETURNS float8

AS 'MODULE_PATHNAME'

LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION gtrgm_compress(internal)

RETURNS internal

AS 'MODULE_PATHNAME'

FOR TYPE text USING gist

AS

OPERATOR 1 % (text, text),

OPERATOR 2 <-> (text, text) FOR ORDER BY pg_catalog.float_ops,

FUNCTION 1 gtrgm_consistent (internal, text, int, oid, internal),

FUNCTION 2 gtrgm_union (bytea, internal),

FUNCTION 3 gtrgm_compress (internal),

FUNCTION 4 gtrgm_decompress (internal),

FUNCTION 5 gtrgm_penalty (internal, internal, internal),

FUNCTION 6 gtrgm_picksplit (internal, internal),

FUNCTION 7 gtrgm_same (gtrgm, gtrgm, internal),

FUNCTION 8 gtrgm_distance (internal, text, int, oid),

STORAGE gtrgm;

-- support functions for gin

select t,similarity(t,'qwertyu0988') as sml from test_trgm where t %'qwertyu0988' order by sml desc, t;

select t,similarity(t,'gwertyu0988') as sml from test_trgm where t %'gwertyu0988' order by sml desc, t;

select t,similarity(t,'gwertyu1988') as sml from test_trgm where t %'gwertyu1988' order by sml desc, t;

select t <->'q0987wertyu0988', t from test_trgm order by t <->'q0987wertyu0988' limit 2;

select t,similarity(t,'qwertyu0988') as sml from test_trgm where t %'qwertyu0988' order by sml desc, t;

select t,similarity(t,'gwertyu0988') as sml from test_trgm where t %'gwertyu0988' order by sml desc, t;

select t,similarity(t,'gwertyu1988') as sml from test_trgm where t %'gwertyu1988' order by sml desc, t;

select t <->'q0987wertyu0988', t from test_trgm order by t <->'q0987wertyu0988' limit 2;

select t <->'q0987wertyu0988', t from test_trgm order by t <->'q0987wertyu0988' limit 2;

#defineLPADDING2

#defineSimilarityStrategyNumber1

#defineDistanceStrategyNumber2

typedefchartrgm[3];

TRGM*generate_trgm(char*str,intslen);

float4cnt_sml(TRGM*trg1,TRGM*trg2);

PG_FUNCTION_INFO_V1(gin_extract_trgm);

Datumgin_extract_trgm(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(gtrgm_in);

Datumgtrgm_in(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(gtrgm_consistent);

Datumgtrgm_consistent(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(gtrgm_distance);

Datumgtrgm_distance(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(gtrgm_union);

Datumgtrgm_union(PG_FUNCTION_ARGS);

}

}

cnt_sml_sign_common(TRGM*qtrg,BITVECPsign)

{

int4count=0;

int4k,

len=ARRNELEM(qtrg);

trgm*ptr=GETARR(qtrg);

int4tmp=0;

for (k=0;k<len;k++)

{

CPTRGM(((char*)&tmp),ptr+k);

count+=GETBIT(sign,HASHVAL(tmp));

}

returncount;

}

gtrgm_consistent(PG_FUNCTION_ARGS)

{

GISTENTRY*entry= (GISTENTRY*)PG_GETARG_POINTER(0);

text*query=PG_GETARG_TEXT_P(1);

StrategyNumberstrategy= (StrategyNumber)PG_GETARG_UINT16(2);

bool*recheck= (bool*)PG_GETARG_POINTER(4);

TRGM*key= (TRGM*)DatumGetPointer(entry->key);

TRGM*qtrg;

boolres= false;

boolres;

char*cache= (char*)fcinfo->flinfo->fn_extra;

qtrg= (TRGM*) (cache+MAXALIGN(VARSIZE(query)));

if (GIST_LEAF(entry))

{/* all leafs contains orig trgm */

float4tmpsml=cnt_sml(key,qtrg);

switch (strategy)

{

caseSimilarityStrategyNumber:

if (GIST_LEAF(entry))

{/* all leafs contains orig trgm */

float4tmpsml=cnt_sml(key,qtrg);

res= (*(int*)&tmpsml==*(int*)&trgm_limit||tmpsml>trgm_limit) ? true : false;

res= (*(int*)&tmpsml==*(int*)&trgm_limit||tmpsml>trgm_limit) ? true : false;

}

elseif (ISALLTRUE(key))

{/* non-leaf contains signature */

res= true;

}

{/* non-leaf contains signature */

int4count=cnt_sml_sign_common(qtrg,GETSIGN(key));

int4len=ARRNELEM(qtrg);

if (len==0)

res= false;

res= (((((float8)count) / ((float8)len))) >=trgm_limit) ? true : false;

}

break;

default:

elog(ERROR,"unrecognized strategy number: %d",strategy);

res= false;/* keep compiler quiet */

break;

}

elseif (ISALLTRUE(key))

{/* non-leaf contains signature */

res= true;

PG_RETURN_BOOL(res);

}

gtrgm_distance(PG_FUNCTION_ARGS)

{

GISTENTRY*entry= (GISTENTRY*)PG_GETARG_POINTER(0);

text*query=PG_GETARG_TEXT_P(1);

StrategyNumberstrategy= (StrategyNumber)PG_GETARG_UINT16(2);

TRGM*key= (TRGM*)DatumGetPointer(entry->key);

TRGM*qtrg;

float8res;

char*cache= (char*)fcinfo->flinfo->fn_extra;

if (cache==NULL||VARSIZE(cache)!=VARSIZE(query)||memcmp(cache,query,VARSIZE(query))!=0)

{

qtrg=generate_trgm(VARDATA(query),VARSIZE(query)-VARHDRSZ);

if (cache)

pfree(cache);

fcinfo->flinfo->fn_extra=MemoryContextAlloc(fcinfo->flinfo->fn_mcxt,

MAXALIGN(VARSIZE(query))+VARSIZE(qtrg));

cache= (char*)fcinfo->flinfo->fn_extra;

memcpy(cache,query,VARSIZE(query));

memcpy(cache+MAXALIGN(VARSIZE(query)),qtrg,VARSIZE(qtrg));

}

{/* non-leaf contains signature */

int4count=0;

int4k,

len=ARRNELEM(qtrg);

trgm*ptr=GETARR(qtrg);

BITVECPsign=GETSIGN(key);

int4tmp=0;

for (k=0;k<len;k++)

{

CPTRGM(((char*)&tmp),ptr+k);

count+=GETBIT(sign,HASHVAL(tmp));

}

res= (len==count) ? true : ((((((float4)count) / ((float4) (len-count)))) >=trgm_limit) ? true : false);

res= (len==0) ? false : ((((((float4)count) / ((float4)len))) >=trgm_limit) ? true : false);

qtrg= (TRGM*) (cache+MAXALIGN(VARSIZE(query)));

switch (strategy)

{

caseDistanceStrategyNumber:

if (GIST_LEAF(entry))

{/* all leafs contains orig trgm */

res=1.0-cnt_sml(key,qtrg);

}

elseif (ISALLTRUE(key))

{/* all leafs contains orig trgm */

res=0.0;

}

{/* non-leaf contains signature */

int4count=cnt_sml_sign_common(qtrg,GETSIGN(key));

int4len=ARRNELEM(qtrg);

res= (len==0) ?-1.0 :1.0- ((float8)count) / ((float8)len);

}

break;

default:

elog(ERROR,"unrecognized strategy number: %d",strategy);

res=0;/* keep compiler quiet */

break;

}

PG_RETURN_BOOL(res);

PG_RETURN_FLOAT8(res);

}

PG_MODULE_MAGIC;

PG_RETURN_FLOAT4(res);

}

PG_FUNCTION_INFO_V1(similarity_dist);

Datumsimilarity_dist(PG_FUNCTION_ARGS);

similarity_dist(PG_FUNCTION_ARGS)

{

float4res=DatumGetFloat4(DirectFunctionCall2(similarity,

PG_GETARG_DATUM(0),

PG_GETARG_DATUM(1)));

PG_RETURN_FLOAT4(1.0-res);

}

PG_FUNCTION_INFO_V1(similarity_op);

Datumsimilarity_op(PG_FUNCTION_ARGS);

similarity_op(PG_FUNCTION_ARGS)

{

float4res=DatumGetFloat4(DirectFunctionCall2(

similarity,

float4res=DatumGetFloat4(DirectFunctionCall2(similarity,

PG_GETARG_DATUM(0),

PG_GETARG_DATUM(1)

));

PG_GETARG_DATUM(1)));

PG_RETURN_BOOL(res >=trgm_limit);

}