title: How to populate many-to-many table from two others on a string column
body: <p>I am familiar with SQL but haven't used it enough and I am completely new to PostgreSQL.</p>
  
  <p>My problem is that I have two tables, one of which has a column with a string values that contains indices referencing other table, these are joined in a string, and I want to generate a third table that would have indices for many-to-many relation.</p>
  
  <p><strong>Table 1:</strong></p>
  
  <pre><code>tags: {
      id: bigint,
      tag: character varying(10),
      connected: character varying(10),
      meaning: character varying(120),
      CONSTRAINT: tags_pk PRIMARY KEY (id)
  }
  </code></pre>
  
  <p><strong>Table 2:</strong></p>
  
  <pre><code>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)
  }
  </code></pre>
  
  <p>These are not actually SQL but should give you the idea of the tables.</p>
  
  <p>The <code>words.lemmas</code> contains something like: <code>noun:p:m</code>, where each part splitter by <code>:</code> is a unique <code>tags.tag</code> name.</p>
  
  <p>I want to split this <code>lemmas</code> column and then for each tag name find the tag <code>id</code> and write a row into many-to-many table with <code>word.id</code> and <code>tags.id</code>.</p>
  
  <p>Could someone give me any suggestions on how to achieve this?</p>
  
  <p>I have tried writing FOR LOOP in a SQL Console in pgAdmin, but it says `incorrect syntax near FOR. I have no idea how to do this with SELECT</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Dummy code I was trying to do but still could not get it running:</p>
  
  <pre><code>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;
  </code></pre>