[SQL] plpgsql의 트리거 기능에 여러 열을 업데이트

plpgsql의 트리거 기능에 여러 열을 업데이트

다음 스키마를 감안할 때 :

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;

내가보기를 업데이트 할 수 plpgsql에서 일반 트리거 기능을 만들려고 :

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();

광산의 실패 노력했다 :

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;

문제는 업데이트 문입니다. 내가 여기서 일 것입니다 구문을 마련 할 수없는입니다. 나는 성공적으로 PL / Perl로이를 구현하지만, plpgsql 전용 솔루션에 관심이있을 것입니다. 어떤 아이디어?

최신 정보

@Erwin Brandstetter 제안으로, 여기 내 PL / 펄 솔루션의 코드입니다. 나는 그의 제안의 일부를 통합.

create function f_tr_up() returns trigger as $$
  use strict;
  use warnings;
  my $target_table = quote_ident($_TD->{'table_name'}) =~ s/^([\w]+)_view$/$1/r;
  my $NEW = $_TD->{'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->{'id'});
  spi_exec_query($query);
return;
$$ language plperl;

해결법

1. ==============================

   1.게리의 대답은 기술적으로 정확 @, 그가 언급에 실패하는 동안 PostgreSQL을가하는이 양식을 지원합니다

   게리의 대답은 기술적으로 정확 @, 그가 언급에 실패하는 동안 PostgreSQL을가하는이 양식을 지원합니다

   UPDATE tbl
   SET (col1, col2, ...) = (expression1, expression2, ..)
   

   번 UPDATE에 대한 설명서를 읽어 보시기 바랍니다.

   아직 자신의 동적 SQL으로 수행하기가 까다로운. 지정하지 않았기 때문에, 나는보기가 기본 테이블과 같은 컬럼으로 구성되어 간단한 경우를 가정하고있다.

   CREATE VIEW tbl_view AS SELECT * FROM tbl;
   

   나는 그것이 빛을 만들기 위해 당신의 접근 방식에 대한 업데이트의 수를했다.

   UPDATE에 대한 트리거 기능 :

   CREATE OR REPLACE FUNCTION f_trg_up()
     RETURNS TRIGGER AS
   $func$
   DECLARE
      tbl  text := quote_ident(TG_TABLE_SCHEMA) || '.'
                || quote_ident(substring(TG_TABLE_NAME from '(.+)_view$'));
      cols text;
      vals text;
   BEGIN
      SELECT INTO cols, vals
             string_agg(quote_ident(attname), ', ')
            ,string_agg('x.' || quote_ident(attname), ', ')
      FROM   pg_attribute
      WHERE  attrelid = tbl::regclass
      AND    NOT attisdropped   -- no dropped (dead) columns
      AND    attnum > 0;        -- no system columns
   
      EXECUTE format('
      UPDATE %s t
      SET   (%s) = (%s)
      FROM  (SELECT ($1).*) x
      WHERE  t.id = ($2).id'
      , tbl, cols, vals) -- assuming unique "id" in every table
      USING NEW, OLD;
   
      RETURN NEW;
   END
   $func$ LANGUAGE plpgsql;
   

   INSERT에 대한 트리거 기능 :

   CREATE OR REPLACE FUNCTION f_trg_ins()
     RETURNS TRIGGER AS
   $func$
   DECLARE
       tbl text := quote_ident(TG_TABLE_SCHEMA) || '.'
                || quote_ident(substring(TG_TABLE_NAME from '(.+)_view$'));
   BEGIN
      EXECUTE 'INSERT INTO ' || tbl || ' SELECT ($1).*'
      USING NEW;
   
      RETURN NEW;  -- don't return NULL unless you know what you're doing
   END
   $func$ LANGUAGE plpgsql;
   

   트리거 :

   CREATE TRIGGER trg_instead_up
   INSTEAD OF UPDATE ON a_view
   FOR EACH ROW EXECUTE PROCEDURE f_trg_up();
   
   CREATE TRIGGER trg_instead_ins
   INSTEAD OF INSERT ON a_view
   FOR EACH ROW EXECUTE PROCEDURE f_trg_ins();
   

   SQL 바이올린은 INSERT와 UPDATE를 보여.

2. ==============================

   2.PostgreSQL는 세트 (COL1, COL2) = 선택 VAL1, val2만큼 구문을 사용하여 여러 열을 업데이트 지원하지 않는다.

   PostgreSQL는 세트 (COL1, COL2) = 선택 VAL1, val2만큼 구문을 사용하여 여러 열을 업데이트 지원하지 않는다.

   같은를 달성하기 위해 PostgreSQL을에 당신이 사용하는 거라고

   update target_table
   set col1 = d.val1,
       col2 = d.val2
   from source_table d
   where d.id = target_table.id
   

   이것은 당신이 반복 개별 필드에 사용하고있는 열 이름 목록이 필요합니다 같은 빌드로 동적 쿼리가 좀 더 복잡 할 것입니다. 난 당신이 배열 문자열을 다시 분할보다는 과정에 더 쉽게로 대신 string_agg의 array_agg 사용하는 것이 좋습니다 것입니다.

   PostgreSQL을 UPDATE 구문

   array_agg 기능에 대한 문서

from https://stackoverflow.com/questions/15343075/update-multiple-columns-in-a-trigger-function-in-plpgsql by cc-by-sa and MIT license