Using the Built-In and Custom Ranking Algorithms of PostgreSQL for Full-Text Searches

Method 1: Using Tsvector

create table tbl (    
shop_id int8 primary key, -- The shop ID
tags text -- Multi-value type, lavel 1: score 1, label 2: score 2, …
national_foot baths:0.99,national_dining:0.1,entertaining_KTV:0.45
create table tbl_weight (    
tagid int primary key, -- The Tag ID
tagname name, -- The tag name
desc text, -- The label description
weight float8 -- The tag weight
);
create index idx_tbl_weight_1 on tbl_weight (tagname);
create or replace function text_to_tsvector(text) returns tsvector as 
$$

select array_to_tsvector(array_agg(substring(id,'(.+):'))) from unnest(regexp_split_to_array($1, ',')) as t(id);
$$
language sql strict immutable;

postgres=# select text_to_tsvector('abc:1.1,bc:100,c:293');
text_to_tsvector
------------------
'abc' 'bc' 'c'
(1 row)
create index idx_tbl_1 on tbl using gin (text_to_tsvector(tags));
postgres=# select substring('bc:1.1,abc:100,c:293','[^,]?abc:([\d\.]+)') ;    
substring
-----------
100
(1 row)

postgres=# select substring('abc:1.1,bc:100,c:293','[^,]?abc:([\d\.]+)') ;
substring
-----------
1.1
(1 row)
postgres=# select substring('abc:1.1,bc:100,c:293','[^,]?adbc:([\d\.]+)') ;    
substring
-----------

(1 row)

postgres=# select substring('abc:1.1,bc:100,c:293','[^,]?adbc:([\d\.]+)') is null;
?column?
----------
t
(1 row)
select
create or replace function cat_ranking(tsquery) returns float8 as 
$$

declare

begin
for each x in array (contains_element) loop
search hit element's score.
search hit element's weight.
cat ranking and increment
end loop;
return res;
end;
$$
language plpgsql strict;

Method 2: Using a Multi-Dimensional Array

Find the location according to the element, find the location according to the tag, and obtain the score from score[] according to the location.  

postgres=# select array_position(array[1,2,null,null,2,2,3,1],null);
array_position
----------------
3
(1 row)

postgres=# select array_positions(array[1,2,null,null,2,2,3,1],null);
array_positions
-----------------
{3,4}
(1 row)

postgres=# select array_positions(array[1,2,null,null,2,2,3,1],2);
array_positions
-----------------
{2,5,6}
(1 row)

Find the element at a certain location

array[i]

postgres=# select (array[1,2,null,null,2,2,3,1])[1];
array
-------
1
(1 row)

postgres=# select (array[1,2,null,null,2,2,3,1])[3];
array
-------

(1 row)

postgres=# select (array[1,2,null,null,2,2,3,1])[5];
array
-------
2
(1 row)

Append an element

array_append

Replace an element

array_replace

Delete an element

array_remove: Note that if the same elements exist, they will be deleted (if the same scores exist, it must be noted that the element needs to be deleted by deleting the location)

postgres=# select array_remove(array[1,2,null,null,2,2,3,1],2);
array_remove
-------------------
{1,NULL,NULL,3,1}
(1 row)

Delete the element at a certain location

postgres=# create or replace function array_remove(anyarray,int[]) returns anyarray as
$$

select array(select $1[i] from (select id from generate_series(1,array_length($1,1)) t(id) where id <> all( $2) ) t(i))
$$
language sql strict;
CREATE FUNCTION
postgres=# select array_remove(array[1,2,null,null,2,2,3,1],array[1,2]);
array_remove
---------------------
{NULL,NULL,2,2,3,1}
(1 row)

postgres=# select array_remove(array[1,2,null,null,2,2,3,1],array[3,5]);
array_remove
------------------
{1,2,NULL,2,3,1}
(1 row)
create table tbl (    
shop_id int8 primary key, -- the ID of the shop
tags text[], -- array, label 1, label 2…
scores float8[] -- array, score 1, score 2…
);

create index idx_tbl_1 on tbl using gin(tags);
national_foot baths, national_dining, entertainment_KTV

0.99,0.1,0.45
create table tbl_weight (    
tagid int primary key, -- The tag ID
tagname name, -- The tag name
desc text, -- The label description
weight float8 -- The tag weight
);

create index idx_tbl_weight_1 on tbl_weight (tagname);

Original Source

--

--

--

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Installing Java with SDKMAN on Raspberry Pi

The Art of being a Product Owner

Commands And Directives of Assembly Programming Language

Function composition in C++14

Number of Pairs with Given Sum

Add and Removing Data from ECC Accumulators

Always there’s the next

How to use Github Jupyter Notebook in Medium Blogs?

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

More from Medium

Setup hot standby PostgreSQL

Use docker-compose to run PostgreSQL

MySQL vs PostgreSQL

PostgreSQL Cheat Sheet

postgresql cheat sheet