创建下面的函数:CREATE FUNCTION "public"."usp_cleanreport" (integer, varchar, integer, integer) RETURNS integer AS
$body$
begin
--Category_ID integer, Report_Type varchar [], Report_Year integer, Report_Month integer
if $2='Age'
then
delete from stagereport where Category_ID=$1 and Report_Year=$3 and Report_Month=$4;
elsif $2='Balance'
then
delete from stbalancereport where Category_ID=$1 and Report_Year=$3 and Report_Month=$4;
elsif $2='Inventory'
then
delete from ststockreport where Category_ID=$1 and Report_Year=$3 and Report_Month=$4;
end if;select 1;END;
$body$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;调用函数时 select usp_cleanreport(3,'Balance',2006,10);
提示:ERROR: DELETE is not allowed in a non-volatile function
CONTEXT: SQL statement "delete from stbalancereport where Category_ID= $1 and Report_Year= $2 and Report_Month= $3 "
PL/pgSQL function "usp_cleanreport" line 8 at SQL statement请问应如何解决? 谢谢!
$body$
begin
--Category_ID integer, Report_Type varchar [], Report_Year integer, Report_Month integer
if $2='Age'
then
delete from stagereport where Category_ID=$1 and Report_Year=$3 and Report_Month=$4;
elsif $2='Balance'
then
delete from stbalancereport where Category_ID=$1 and Report_Year=$3 and Report_Month=$4;
elsif $2='Inventory'
then
delete from ststockreport where Category_ID=$1 and Report_Year=$3 and Report_Month=$4;
end if;select 1;END;
$body$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;调用函数时 select usp_cleanreport(3,'Balance',2006,10);
提示:ERROR: DELETE is not allowed in a non-volatile function
CONTEXT: SQL statement "delete from stbalancereport where Category_ID= $1 and Report_Year= $2 and Report_Month= $3 "
PL/pgSQL function "usp_cleanreport" line 8 at SQL statement请问应如何解决? 谢谢!
解决方案 »
- 关于一个access转mysql语句的问题
- MySql 登录失败 (Error No.1045)
- PostgreSQL查询赋值
- 关于数据库备份的问题?
- 是否可以关闭binlog
- 数据表使用auto_increment后插入出错的问题
- mysql使用left jion ,right join多表连接时速度奇慢
- 我的mysql 用localhost 可以访问,但是用ip地址就不行了,甚至用127.0.0.1都不行,怎么办?
- mysql表修复
- 哪位大神帮我看一下这个mysql语句写的有问题吗?
- 请问SQL和MYSQL有什么区别呢?学哪个难点,学哪个吃香点呢?谢谢回答啊
- mysql 5.0.17建数据库时出问题,请高手指教!
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;
你定义为STABLE 类型了
应该为volatile类型
具体原因请看手册