title: Update multiple columns in a trigger function in plpgsql
body: <p>Given the following schema:</p>
  
  <pre><code>create table account_type_a (
    id SERIAL UNIQUE PRIMARY KEY,
    some_column VARCHAR
  );
  
  create table account_type_b (
    id SERIAL UNIQUE PRIMARY KEY,
    some_other_column VARCHAR
  );
  
  create view account_type_a view AS select * from account_type_a;
  create view account_type_b view AS select * from account_type_b;
  </code></pre>
  
  <p>I try to create a <em>generic</em> trigger function in plpgsql, which enables updating the view:</p>
  
  <pre><code>create trigger trUpdate instead of UPDATE on account_view_type_a
  for each row execute procedure updateAccount();    
  create trigger trUpdate instead of UPDATE on account_view_type_a
  for each row execute procedure updateAccount();
  </code></pre>
  
  <p>An unsuccessful effort of mine was:</p>
  
  <pre><code>create function updateAccount() returns trigger as $$
  declare
    target_table varchar := substring(TG_TABLE_NAME from '(.+)_view');
    cols varchar;
  begin
    execute 'select string_agg(column_name,$1) from information_schema.columns
             where table_name = $2' using ',', target_table into cols;
    execute 'update ' || target_table || ' set (' || cols || ') =  select ($1).*
             where id = ($1).id' using NEW;
    return NULL;
  end;
  $$ language plpgsql;
  </code></pre>
  
  <p>The problem is the <code>update</code> statement. I am unable to come up with a syntax that would work here. I have successfully implemented this in PL/Perl, but would be interested in a plpgsql-only solution.<br>
  Any ideas?</p>
  
  <p><strong>Update</strong></p>
  
  <p>As @Erwin Brandstetter suggested, here is the code for my PL/Perl solution. I incoporated some of his suggestions.</p>
  
  <pre><code>create function f_tr_up() returns trigger as $$
    use strict;
    use warnings;
    my $target_table = quote_ident($_TD-&gt;{'table_name'}) =~ s/^([\w]+)_view$/$1/r;
    my $NEW = $_TD-&gt;{'new'};
    my $cols = join(',', map { quote_ident($_) } keys $NEW);
    my $vals = join(',', map { quote_literal($_) } values $NEW);
    my $query = sprintf(
      "update %s set (%s) = (%s) where id = %d",
      $target_table,
      $cols,
      $vals,
      $NEW-&gt;{'id'});
    spi_exec_query($query);
  return;
  $$ language plperl;
  </code></pre>