postgresql - Postgresql 中的字符串替换源自一个额外的字符串数组
问题描述
假设您有两个必须保持原样的替换表和另一个包含名称主体的表。我怎样才能得到所有可能的替换?
Substitution Table
--------------------------------------
word subs_list
MOUNTAIN MOUNTAIN, MOUNT, MT, MTN
HOUSE HAUS, HOUSE
VIEW VU, VIEW
Synonyms table
-------------------------------------------------
EDUCATION SCHOOL, UNIVERSITY, COLLEGE, TRAINING
FOOD STORE, FOOD, CAFE
STORE FOOD, STORE, MARKET
REFRIGERATION FOODLOCKER, FREEZE, FRIDGE
names table
------------------------------------------------
MOUNT VU FOOD USA
MOUNTAIN VU STORE CA
注意:我知道只有一个替换表是可取的,但是两个替换表必须保留,因为它们的用途比上面解释的要多,这些表已经在使用中。此外,两个表中的替换列表只是一个 varchar,字符串用逗号分隔
考虑到前面的问题,问题是生成通过替换派生的可能名称。例如,名称MOUNT VU FOOD USA
应该分解为MOUNTAIN VIEW FOOD USA
and MOUNTAIN VIEW STORE USA
,同样的方式将适用于第二个。
我已经能够以错误的顺序获得替换,并且在功能上都在一起,有一种方法可以获取一个数组作为输出,替换后生成的不同名称?到目前为止,我已经创建了这个替换功能:
create or replace function replace_companies_array(i_sentence IN VARCHAR) returns VARCHAR[] AS $p_replaced$
DECLARE
p_replaced VARCHAR[];
subs RECORD;
flag boolean:= True;
cur_s CURSOR(i_sentence VARCHAR)
FOR SELECT w.input, coalesce(x.word, w.input) as word, count(*) OVER (PARTITION BY w.input) as counter
FROM regexp_split_to_table(trim(i_sentence), '\s') as w(input)
LEFT JOIN (
select s.word, trim(s1.token) as token
from subs01 s
cross join unnest(string_to_array(s.subs_list, ',')) s1(token)
union
select sy.word, trim(s2.token) as token
from syns01 sy
cross join unnest(string_to_array(sy.syn_list, ',')) s2(token)
) as x on lower(trim(w.input)) = lower(x.token)
order by counter;
BEGIN
OPEN cur_s(i_sentence);
LOOP
--fetch row into the substitutions
FETCH cur_s INTO subs;
--Exit when no more rows to fetch
EXIT WHEN NOT FOUND;
SELECT REGEXP_REPLACE(i_sentence,'(^|[^a-z0-9])' || subs.input || '($|[^a-z0-9])','\1' || UPPER(subs.word) || '\2','g')
INTO i_sentence;
END LOOP;
p_replaced:=array_append(p_replaced, i_sentence);
RETURN p_replaced;
END;
$p_replaced$ LANGUAGE plpgsql;
非常感谢您的贡献
解决方案
我没有设法得到最终结果,但我非常接近它!
从句子:MOUNT VU FOOD USA
,我得到{"MOUNTAIN VIEW MARKET USA","MOUNTAIN VIEW STORE USA","MOUNTAIN VIEW CAFE USA","MOUNTAIN VIEW FOOD USA"}
这是我重新创建同义词和替换表的所有脚本:
DROP TABLE IF EXISTS subs01;
DROP TABLE IF EXISTS syns01;
CREATE TABLE subs01 (word VARCHAR(20), subs_list VARCHAR(200));
CREATE TABLE syns01 (word VARCHAR(20), syn_list VARCHAR(200));
INSERT INTO subs01 (word, subs_list) VALUES ('MOUNTAIN', 'MOUNTAIN, MOUNT, MT, MTN'),('HOUSE', 'HAUS, HOUSE'),('VIEW', 'VU, VIEW');
INSERT INTO syns01 (word, syn_list) VALUES ('EDUCATION', 'SCHOOL, UNIVERSITY, COLLEGE, TRAINING'),('FOOD', 'STORE, FOOD, CAFE'),('STORE', 'FOOD, STORE, MARKET'),('REFRIGERATION', 'FOODLOCKER, FREEZE, FRIDGE');
我决定将工作分为两个阶段:
换成以下词语:
CREATE OR REPLACE function substitute_words (i_sentence IN VARCHAR) returns VARCHAR AS $p_substituted$ DECLARE --p_substituted VARCHAR; subs_cursor CURSOR FOR select su.word, trim(s2.token) as token from subs01 su cross join unnest(string_to_array(su.subs_list, ',')) s2(token); subs_record record; BEGIN OPEN subs_cursor; LOOP FETCH subs_cursor INTO subs_record; EXIT WHEN NOT FOUND; RAISE NOTICE 'INFO : TOKEN (%) ',subs_record.token ; IF i_sentence LIKE '%'|| subs_record.token || '%' THEN RAISE NOTICE '-- FOUND : TOKEN (%) ',subs_record.token ; SELECT replace (i_sentence, subs_record.token, subs_record.word) INTO i_sentence; END IF; END LOOP; CLOSE subs_cursor; RETURN i_sentence; END $p_substituted$ LANGUAGE plpgsql;
用同义词替换已知单词:
CREATE OR REPLACE function synonymize_sentence (i_sentence IN VARCHAR) returns TABLE (sentence_result VARCHAR) AS $p_syn$ DECLARE syn_cursor CURSOR FOR select su.word, trim(s2.token) as token from syns01 su cross join unnest(string_to_array(su.syn_list, ',')) s2(token); syn_record record; BEGIN CREATE TEMPORARY TABLE record_syn (result VARCHAR(200)) ON COMMIT DROP; INSERT INTO record_syn (result) SELECT i_sentence; OPEN syn_cursor; LOOP FETCH syn_cursor INTO syn_record; EXIT WHEN NOT FOUND; RAISE NOTICE 'INFO : WORD (%) ',syn_record.word ; INSERT INTO record_syn (result) SELECT replace (result, syn_record.word, syn_record.token) FROM record_syn where result LIKE '%'|| syn_record.word || '%'; END LOOP; CLOSE syn_cursor; RETURN QUERY SELECT distinct result FROM record_syn; END; $p_syn$ LANGUAGE plpgsql;
然后,为了生成结果数组,我执行以下语句:
SELECT ARRAY(SELECT synonymize_sentence (substitute_words ('MOUNT VU FOOD USA')));
推荐阅读
- python - Django 使用 .value 过滤数据库并调用外键
- go - 数据竞赛无法理解
- c - C 代码的 x86 反汇编生成:orq $0x0, %(rsp)
- apache-spark - 将 pyspark 列转换为列表
- r - 如何引用 R 中的所有其他列?
- python-3.x - 如果出现超过 1 次,如何将子标签移动到母标签之后?
- python - ValueError 视图没有返回 HttpResponse 对象。它返回 None 而不是
- javascript - 为什么 window.setTimeout 返回错误?
- reactjs - react-native undefined 不是对象
- css - 返回 true 的 React 组件将仅接受通过内联样式的样式