62758


before triggerを用いてupdateを禁止したりdefault値をinsertしたりしたい。

[code]

    create or replace function not_update() returns trigger as $$
    begin
      execute format('new.%I = old.%I',  TG_ARGV[0], TG_ARGV[0]);
      return new;
    end
    $$ language plpgsql security definer;
    
[/code]

これは試したがシンタックスエラーを吐いた


CC BY-SA 4.0
2020-01-31T10:48:30.023
62682
2020-02-04T05:10:32.783
35654
question
sql postgresql
plpgsqlでrecordのカラムを動的に指定してその値を書き換えたい

[code]

    -- トリガーハンドラをつくる trigger_not_update参照
    create or replace function not_update(col text) returns void as $$
    begin
      execute format(
        'create or replace function not_update_%s() returns trigger as $_$'
        'begin'
        '  raise warning ''%% %%'', current_user, session_user;'
        '  if current_user = session_user then' -- superuser can update
        '    return new;'
        '  end if;'
        '  new.%I = old.%I;'
        '  return new;'
        'end'
        '$_$ language plpgsql security definer;', $1, $1, $1);
    end
    $$ language plpgsql security definer;
    
    -- トリガーハンドラをつくる trigger_insert_default参照
    create or replace function insert_default(tabl text, col text) returns void as $$
    begin
      execute format(
        'create or replace function insert_default_%1$s_%2$s() returns trigger as $_$'
        'declare'
        '  vt text;'
        '  vr record;'
        'begin'
        '  raise warning ''%% %%'', current_user, session_user;'
        '  if current_user = session_user then' -- superuser can insert anything
        '    return new;'
        '  end if;'
        '  select pg_get_expr(d.adbin, d.adrelid) from pg_attribute a'
        '    left join pg_attrdef d on a.attnum = d.adnum and a.attrelid = d.adrelid'
        '    where a.attrelid = ''data.%1$s''::regclass and a.attnum > 0 and a.attname = %2$L'
        '  into vt;' -- default値を表すtext
        '  execute format(''select %%s as v'', vt) into vr;' -- default値
        '  new.%2$s = vr.v;'
        '  return new;'
        'end'
        '$_$ language plpgsql security definer;', $1, $2);
    end
    $$ language plpgsql security definer;
    
    
    create or replace function trigger_not_update(tabl text, col text) returns void as $$
    begin
      execute format('select not_update(%L);', $2);
      execute format(
        'create trigger %s_not_update_%s '
        'before update on %I '
        'for each row execute procedure not_update_%s();', $1, $2, $1, $2);
    end
    $$ language plpgsql security definer;
    
    create or replace function trigger_insert_default(tabl text, col text) returns void as $$
    begin
      execute format('select insert_default(%L, %L);', $1, $2);
      execute format(
        'create trigger %1$s_insert_default_%2$s '
        'before insert on %1$I '
        'for each row execute procedure insert_default_%1$s_%2$s();', $1, $2);
    end
    $$ language plpgsql security definer;
    
    create or replace function trigger_default(tabl text, col text) returns void as $$
    begin
      execute format('select trigger_not_update(%L, %L);', $1, $2);
      execute format('select trigger_insert_default(%L, %L);', $1, $2);
    end
    $$ language plpgsql security definer;
    
    select trigger_default('users', 'id');
    select trigger_default('users', 'created');
    
[/code]


CC BY-SA 4.0
2020-02-04T05:10:32.783
62758
2020-02-04T05:10:32.783
35654
62682
answer