TAG 21
作者
digoal
日期
2016-05-27
标签
PostgreSQL , Oracle , 兼容性 , orafce , 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的编程能力还是非常强的，纂改的想象空间还很大，大家自由发挥。
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
