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

GemGuardian Tournament Game Testnet is now open!

Piloting a web browser with GraphQL

Postgres Does More(than you think)

Write a Story And Be Featured in Front of 100,000 People

Where’s Ruby?

Creating a BIP-70 Payment Request

Rotate / Flip video on Linux with ffmpeg

Which Python package manager should you use?

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

Aggregation ElasticSearch — Java API Client

PostgreSQL Log Based Replication

Using Debezium Source Connector and JDBC Sink with Kafka Connect on AWS RDS PostgreSQL

Postgres — Logical Replication and long running transactions