sql - 如何在字符串列上从另外两个表填充多对多表

Question

我对 SQL 很熟悉，但使用得还不够，我对 PostgreSQL 完全陌生。

我的问题是我有两个表，其中一个有一个包含字符串值的列，其中包含引用其他表的索引，它们连接在一个字符串中，我想生成第三个表，该表将具有许多索引-对多关系。

表 1:

tags: {
    id: bigint,
    tag: character varying(10),
    connected: character varying(10),
    meaning: character varying(120),
    CONSTRAINT: tags_pk PRIMARY KEY (id)
}

表 2:

words: {
    id: bigint,
    form: character varying(255),
    lemmas: character varying(255),
    original: character varying(255),
    tsv_form_text: tsvector,
    CONSTRAINT words_pkey PRIMARY KEY (id)
}

这些实际上不是 SQL，但应该让您了解表。

words.lemmas 包含如下内容:noun:p:m，其中由 : 分隔的每个部分都是唯一的 tags.tag 名称。

我想拆分此 lemmas 列，然后为每个标签名称找到标签 id 并用 word 将一行写入多对多表.id 和 tags.id。

有人可以就如何实现这一目标给我任何建议吗？

我曾尝试在 pgAdmin 的 SQL 控制台中编写 FOR LOOP，但它说“FOR 附近的语法不正确。我不知道如何使用 SELECT 执行此操作

更新

我尝试执行但仍然无法运行的伪代码:

CREATE OR REPLACE FUNCTION dummy_function()
RETURNS TABLE 
(
    word_id bigint,
    tag_id bigint
)
AS $$
DECLARE
    WORD_ID bigint;
    LEMMAS varchar(255)[];
    TAG_ID bigint;
BEGIN
    FOREACH word IN words LOOP
        LEMMAS = string_to_array(QuestionList_, ':')

        FOREACH lemma in ARRAY LEMMAS LOOP
        -- HERE I want to return word_id and tag_id that I would later insert into relations table
            RETURN QUERY word.id, SELECT tags.id FROM tags WHERE tag = lemma
        END LOOP
    END LOOP
    RETURN
END;
$$ LANGUAGE PLPGSQL;

Answer 1

unnest(string_to_array(..))方便而且相当快。演示

with words(id, lemmas) as (
  values 
     (1, 'as:df:gh')
    ,(2, 'zx:cv:bn')
), tags(id, tag) as (
  values 
     (11, 'as')
    ,(12, 'df')
    ,(13, 'gh')
    ,(14, 'zx')
    ,(15, 'cv')
    ,(16, 'bn')
)
select w.id wordId, t.id tagId
from (
   select id, unnest(string_to_array(lemmas, E':')) word
   from words
) w
join tags t on t.tag = w.word;

fiddle