时间戳

首页 > 解决方案 > 如何在字符串列上从另外两个填充多对多表

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

问题描述

我对 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并使用 and 将一行写入多对多表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;

标签： sqlpostgresql

解决方案

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;

小提琴

推荐阅读