创建触发器、函数
1、创建触发器
create or replace function addfun() returns trigger as $addfun$
begin
RETURN NULL;end;
$addfun$ LANGUAGE pluxsql;
CREATE TRIGGER triggername AFTER INSERT OR UPDATE OR DELETE ON mytabll FOR EACH ROW EXECUTE PROCEDURE addfun();
2、创建函数(存储过程)
(1) CREATE FUNCTION addfun(integer, integer) RETURNS integer
AS 'select $1 + $2;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
(2)CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
LANGUAGE SQL;
create or replace function addfun1() returns integer as $addfun$ begin return null;end;
$addfun$ LANGUAGE pluxsql;
(3)撤销存储过程的权限
revoke EXECUTE on function uxdb.myproc2(integer,integer) from ccc;
(4)存储过程赋权
grant EXECUTE on function uxdb.myprocl(integer,integer) to ccc;
(5)修改存储过程所有者
alter function uxdb.addfun() owner to ccc;
(6)删除存储过程
drop function myproc1 (int4,int4);
3、触发器
(1)触发器的启用、禁用
alter table mytabll disable trigger all;(禁用所有触发器)
alter table mytabll disable trigger addfun;(禁用名为addfun的触发器)
alter table mytabll enable trigger all;(启用所有触发器)
alter table mytabll enable trigger addfun;(启用名为addfun的触发器)
(2)触发器的删除
drop trigger addfun on tablename cascade | restrict;