title: Postgresql and dblink: how do I do an UPDATE FROM?
body: <h3>Here's what works already, but it's using a loop:</h3>
  <p>(I am updating the <code>nickname, slug</code> field on the remote table for each row in a local table)</p>
  <pre><code>DECLARE
  row_ record;
  rdbname_ varchar;
  ....
  /* select from local */
  FOR row_ IN SELECT rdbname, objectvalue1 as keyhash, cvalue1 as slug, cvalue2 as nickname
      FROM bme_tag
      where rdbname = rdbname_
      and   tagtype = 'NAME'
      and  wkseq = 0
  
      LOOP
          /* update remote */
          PERFORM dblink_exec('sysdb',
              format(
                  'update bme_usergroup
                  set nickname = %L
                  ,slug = %L
                  where rdbname = %L
                  and   wkseq = 0 
                  and   keyhash = %L'
                  , row_.nickname, row_.slug, row_.rdbname, row_.keyhash)
              );
  
  END LOOP;
  </code></pre>
  <p>Now, what I would like to do instead is to do a bulk <code>UPDATE (remote) FROM (local)</code></p>
  <pre><code>PERFORM dblink_exec('sysdb',
  'update (remote)bme_usergroup
  set    nickname = bme_tag.cvalue2, slug=bme_tag.cvalue1
  from   (local).bme_tag s
  where  bme_usergroup.rdbname        = %L
  and    bme_usergroup.wkseq          = 0
  and    bme_usergroup.keyhash        = s.keyhash
  and    bme_usergroup.rdbname        = s.rdbname
  )
  </code></pre>
  <p>I've gotten this far by looking a various solutions (<a href="https://stackoverflow.com/questions/6083132/postgresql-insert-into-select">postgresql: INSERT INTO ... (SELECT * ...)</a>)  and I know how to separate the remote and local tables of the query in the context of <code>SELECT</code>, <code>DELETE</code> and even <code>INSERT/SELECT</code>.  And I can do that direct update with bind variables too.  But how about <code>UPDATE FROM</code>?</p>
  <p><strong>If it's not possible, should I look into Postgres's <code>FOREIGN TABLE</code> or something similar?</strong></p>
  <p>The local and remote db are both on the same Postgres server.  One additional bit of information, if it matters, is that either database may be dropped and restored separately from the other, and I'd prefer a lightweight solution that doesn't take a lot of configuration each time to reestablish communication.</p>