Postgresql: 动态SQL语句中不能使用Select into?
我的数据库版本是 PostgreSQL 8.4.7 。下面是出错的存储过程:
CREATE or Replace FUNCTION func_getnextid(
tablename varchar(240),
idname varchar(20) default 'id')
RETURNS integer AS $funcbody$
Declare
sqlstring varchar(240);
currentId integer;
Begin
sqlstring:= 'select max("' || idname || '") into currentId from "' || tablename || '";';
EXECUTE sqlstring;
if currentId is NULL or currentId = 0 then
return 1;
else
return currentId + 1;
end if;
End;
$funcbody$ LANGUAGE plpgsq
执行后出现这样的错误:
SQL error:
ERROR:EXECUTE of SELECT ... INTO is not implemented
CONTEXT:PL/pgSQL function "func_getnextbigid" line 6 at EXECUTE statement
改成这样的就对了:
CREATE or Replace FUNCTION func_getnextid(
tablename varchar(240),
idname varchar(20) default 'id')
RETURNS integer AS $funcbody$
Declare
sqlstring varchar(240);
currentId integer;
Begin
sqlstring:= 'select max("' || idname || '") from "' || tablename || '";';
EXECUTE sqlstring into currentId;
if currentId is NULL or currentId = 0 then
return 1;
else
return currentId + 1;
end if;
End;
$funcbody$ LANGUAGE plpgsql;
页:
[1]