title: update many to many records with postgresql / plpgsql
body: <p>I have a web based system that has several tables (postgres/pgsql) that hold many to many relationships such as;
  table x
  column_id1 smallint FK
  column_id2 smallint FK</p>
  
  <p>In this scenario the update is made based on column_id2</p>
  
  <p>At first to update these records we would run the following function;</p>
  
  <pre><code>-- edited to protect the innocent
  CREATE FUNCTION associate_id1_with_id2(integer[], integer) RETURNS integer
  AS $_$
  DECLARE
      a alias for $1;
      b alias for $2;
      i integer;
  BEGIN
      delete from tablex where user_id = b;
      FOR i IN array_lower(a,1) .. array_upper(a,1) LOOP
          INSERT INTO tablex (
              column_id2,
              column_id1)
          VALUES (
              b,
              a[i]);
      end loop;
  RETURN i;
  END;
  $_$ 
  LANGUAGE plpgsql;
  </code></pre>
  
  <p>that seemed sloppy and now with the addition of auditing it really shows.</p>
  
  <p>What I am trying to do now is only delete and insert the necessary rows.</p>
  
  <p>I have been trying various forms of the following with no luck</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION associate_id1_with_id2(integer[], integer) RETURNS integer
  AS $_$
  DECLARE
      a alias for $1;
      b alias for $2;
  c varchar;
      i integer;
  BEGIN
      c = array_to_string($1,',');
  
      INSERT INTO tablex (
              column_id2,
              column_id1)
              (
      SELECT column_id2, column_id1
      FROM tablex
              WHERE column_id2 = b
      AND column_id1 NOT IN (c)
      );
  
      DELETE FROM tablex
      WHERE column_id2 = b
      AND column_id1 NOT IN (c);
  
  RETURN i;
  END;
  $_$
  LANGUAGE plpgsql;
  </code></pre>
  
  <p>depending on the version of the function I'm attempting there are various errors such as explicit type casts (i'm guessing it doesnt like c being varchar?) for the current version.</p>
  
  <p>first off, is my approach correct or is there a more elegant solution given there are a couple tables which this type of handling is required?  If not could you please point me in the right direction?</p>
  
  <p>if this is the right approach could you please assist with the array conversion for the NOT IN portion of the where clause?</p>