PostgreSQL Oracle 兼容性之 - 如何篡改插入值(例如NULL纂改为其他值)

背景

Oracle有个功能，可以将用户插入的NULL值替换成指定的值。

这个功能和default值的用意并不一样，default是指用户没有指定值时，使用default值代替。

例如

postgres=# alter table test alter column id set default 1;  
ALTER TABLE  
postgres=# create table t(id int, info text default 'abc');  
CREATE TABLE  
postgres=# insert into t values (1);  
INSERT 0 1  
postgres=# select * from t;  
 id | info   
----+------  
  1 | abc  
(1 row)  

当用户指定了NULL时，进去的就是NULL。

postgres=# insert into t values (1,NULL);  
INSERT 0 1  
postgres=# select * from t;  
 id | info   
----+------  
  1 | abc  
  1 |   
(2 rows)  

而NULL值的替换则是用户在给定了NULL值时可以替换为其他值。

PostgreSQL如何实现呢？

用触发器就可以实现了

postgres=# create or replace function tgf1() returns trigger as $$  
declare  
begin  
  if NEW.info is null then  
    NEW.info = (TG_ARGV[0])::text;  
  end if;  
  return NEW;   
end;  
$$ language plpgsql;  
CREATE FUNCTION  
  
postgres=# create trigger tg1 before insert on t for each row execute procedure tgf1('new_value');  
CREATE TRIGGER  
  
postgres=# insert into t values (3,NULL);  
INSERT 0 1  
postgres=# select * from t where id=3;  
 id |   info      
----+-----------  
  3 | new_value  
(1 row)  

甚至可以针对用户来设置不同的值

postgres=# create or replace function tgf1() returns trigger as $$  
declare  
begin  
  if NEW.info is null then  
    select case when current_user = 'test' then 'hello' else 'world' end into NEW.info;     
  end if;       
  return NEW;   
end;  
$$ language plpgsql;  
CREATE FUNCTION  
  
postgres=# insert into t values (5,NULL);  
INSERT 0 1  
postgres=# select * from t where id=5;  
 id | info    
----+-------  
  5 | world  
(1 row)  
  
postgres=# create role test superuser login;  
CREATE ROLE  
postgres=# \c postgres test  
  
postgres=# insert into t values (6,NULL);  
INSERT 0 1  
postgres=# select * from t where id=6;  
 id | info    
----+-------  
  6 | hello  
(1 row)  

是不是很好玩呢？

因为PLPGSQL的编程能力还是非常强的，纂改的想象空间还很大，大家自由发挥。

digoal’s 大量PostgreSQL文章入口