时间戳

首页 > 解决方案 > Postgres / SQL中是否有办法从字符串中的字符是列名的列中替换字符串中的字符？

sql - Postgres / SQL中是否有办法从字符串中的字符是列名的列中替换字符串中的字符？

问题描述

我有一张名为 的表sentences，还有一张名为 的表logs。

该sentences表如下所示：

|------------|---------------------------------------|
|     id     |                sentence               |
|------------|---------------------------------------|
|     1      |     [var1] says hello!                |
|------------|---------------------------------------|
|     2      |     [var1] says [var2]!               |
|------------|---------------------------------------|
|     3      |     [var1] says [var2] and [var3]!    |
|------------|---------------------------------------|
|     4      |     [var4] says [var2] to [var1]!     |
|------------|---------------------------------------|

该logs表如下所示：

|------------|------------------|--------------|--------------|--------------|--------------|
|     id     |     sentenceId   |     var1     |     var2     |     var3     |     var4     |
|------------|------------------|--------------|--------------|--------------|--------------|
|     1      |         1        |     Sam      |              |              |              |
|------------|------------------|--------------|--------------|--------------|--------------|
|     2      |         2        |     Joe      |   what's up  |              |              |
|------------|------------------|--------------|--------------|--------------|--------------|
|     3      |         3        |     Tim      |     hey      | how are you  |              |
|------------|------------------|--------------|--------------|--------------|--------------|
|     4      |         4        |     Joe      |     hi       |              |    Tiffany   |
|------------|------------------|--------------|--------------|--------------|--------------|

我试图得到的结果是：

|------------|-----------------------------------------|
|  logs.id   |            sentences.sentence           |
|------------|-----------------------------------------|
|     1      |   [Sam] says hello!                     |
|------------|-----------------------------------------|
|     2      |   [Joe] says [what's up]!               |
|------------|-----------------------------------------|
|     3      |   [Tim] says [hey] and [how are you]!   |
|------------|-----------------------------------------|
|     4      |   [Tiffany] says [hi] to [Joe]          |
|------------|-----------------------------------------|

我不确定如何编写 SQL 查询以使数据库为我进行文本替换。

我可以使用内部连接从两个表中选择所有内容，然后在代码中循环并自己进行替换。IE：

SELECT logs.id, sentences.sentence, logs.var1, logs.var2, logs.var3, logs.var4 FROM logs INNER JOIN sentences ON logs.sentenceId = sentences.id

然后在代码中：

logs.forEach(log => log.sentence.replace(/\[(.*?)\]/g, ($matchedString, $columnName) => log[$columnName] ))

但如果可能的话，我希望数据库为我做这件事，这样我就不必选择比我需要的更多的数据。

标签： sqlpostgresql

解决方案

我会写一个函数来做到这一点：

create function replace_vars(p_sentence text, p_vars jsonb)
 returns text
as
$$
declare
  l_rec record;
  l_result text;
begin
  l_result := p_sentence;
  for l_rec in select * from jsonb_each_text(jsonb_strip_nulls(p_vars)) as x(var,value) 
  loop
     l_result := replace(l_result, l_rec.var, l_rec.value);
  end loop;
  return l_result;
end;
$$
language plpgsql;

然后你可以像这样使用它：

select s.id, s.sentence, replace_vars(s.sentence, to_jsonb(l)) new_sentence
from sentences s
  left join logs l on l.sentenceid = s.id;

在线示例

推荐阅读