title: Merge two tables with same column names, add counters
body: <p>I have two tables with the same columns, the first column is the name and the second is a count. I would like to merge these tables, so that each name appears with the added count of the two tables:</p>
  
  <pre><code>Table1:           Table2:            Result Table:
  NAME   COUNT      NAME   COUNT       NAME   COUNT
  name1  1          name3  3           name1  1
  name2  2          name4  4           name2  2
  name3  3          name5  5           name3  6
  name4  4          name6  6           name4  8
                                       name5  5
                                       name6  6
  </code></pre>
  
  <p>As of the moment I have created a pretty ugly structure to execute this, and would like to know if it is possible to get the results in a more elegant way.</p>
  
  <p>What I have so far (Table1 is test1 and Table2 is test2):</p>
  
  <pre><code>create table test1 ( name varchar(40), count integer);
  create table test2 ( name varchar(40), count integer);
  create table test3 ( name varchar(40), count integer);
  create table test4 ( name varchar(40), count integer);
  create table test5 ( name varchar(40), count integer);
  
  insert into test4 (name, count) select *  from test1;
  insert into test4 (name, count) select *  from test2;
  insert into test3 (name , count) select t1.name, t1.count + t2.count 
  from test1 t1 inner join test2 t2 on t1.name = t2.name;
  select merge_db(name, count) from test3;
  insert into test5 (name, count) (select name, max(count) from test4 group by name);
  
  
  CREATE FUNCTION merge_db(key varchar(40), data integer) RETURNS VOID AS
      $$ -- souce: http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql
      BEGIN
          LOOP
              -- first try to update the key
              UPDATE test4 SET count = data WHERE name = key;
              IF found THEN
                  RETURN;
              END IF;-- not there, so try to insert the key -- if someone else inserts the same key concurrently,        -- we could get a unique-key failure
              BEGIN
                  INSERT INTO test4(name,count) VALUES (key, data);
                  RETURN;
              EXCEPTION WHEN unique_violation THEN-- do nothing, and loop to try the UPDATE again
              END;
          END LOOP;
      END;
      $$
      LANGUAGE plpgsql;
  </code></pre>