postgreSQL存储过程函数的使用方法

CREATE OR REPLACE FUNCTION pagination_info(kw character varying, querycate bigint, flg bigint, lmt bigint, intpg bigint)
  RETURNS SETOF record AS
$BODY$DECLARE
kw alias for $1;--关键词
querycate alias for $2;--查询的部门
flg alias for $3;--0表示按关键字查询,1表示非关键词查询,2表示默认查询
lmt alias for $4;--每页显示记录数
page alias for $5;--待显示页码
recs record;--返回的集合
rowcount integer;--记录总数
pagecount integer;--总页数
intpg integer;--当前页数
pagerecs record;--统计页面的记录集合
BEGIN
rowcount:=0;
pagecount:=0;
intpg:=page;
--根据flg开判断查询类型：
IF flg = 0 THEN
RAISE NOTICE 'select by keyword %',$1;
--采用关键字来查询：
--统计总记录数:
--select from government_info
select into rowcount count(*)as n from government_info WHERE title LIKE '%'||kw||'%';
pagecount:=(rowcount+lmt-1)/lmt;
IF intpg>pagecount THEN
intpg=pagecount;
END IF;

FOR recs IN SELECT info_id,title,title as classname,input_time,columnid,0 as rc,0 as pc FROM government_info WHERE title LIKE '%'||kw||'%' LIMIT lmt OFFSET (intpg-1)*lmt LOOP
recs.classname='';
recs.rc=rowcount;
recs.pc=pagecount;
--根据columnid来查询classname<开始>
DECLARE
cs record;
BEGIN
select into cs * from column_structure where classid=recs.columnid;
if not found then
else
recs.classname=cs.classname;
end if;
END;
--根据columnid查询<结束>
raise notice '%',recs.classname;
return next recs;--返回数据集
END LOOP;
ELSEIF flg = 2 THEN
RAISE NOTICE 'SELECT BY DEFAULT';
--统计总记录数:
--select from government_info
select into rowcount count(*)as n from government_info;
pagecount:=(rowcount+lmt-1)/lmt;
IF intpg>pagecount THEN
intpg=pagecount;
END IF;
--默认查询<开始>
FOR recs IN select info_id,title,title as classname,input_time,columnid,0 as rc,0 as pc from government_info order by input_time desc LIMIT lmt OFFSET (intpg-1)*lmt LOOP
recs.classname='';
recs.rc=rowcount;
recs.pc=pagecount;
--根据columnid来查询classname<开始>
DECLARE
cs record;
BEGIN
select into cs * from column_structure where classid=recs.columnid;
if not found then
else
recs.classname=cs.classname;
end if;
END;
--根据columnid查询<结束>
return next recs;--返回数据集
END LOOP;
raise notice '%',recs.classname;
--默认查询<结束>
ELSE
RAISE NOTICE 'select by departmen %',$2;
--根据所选部门查询<开始>
--判断parentid<开始>
DECLARE
--parentid integer;--上级ID
cs record;--从column_structure中查找parentid
BEGIN
SELECT INTO cs *FROM column_structure WHERE classid=querycate;
IF FOUND THEN
RAISE NOTICE 'I found the classid %',querycate;
IF cs.parentid=0 THEN
raise notice 'The cs parentid==0';
--统计总记录数:
--select from government_info
select into rowcount count(*)as n from government_info where parentid=querycate;
pagecount:=(rowcount+lmt-1)/lmt;
IF intpg>pagecount THEN
intpg=pagecount;
END IF;

for recs in select info_id,title,title as classname,input_time,columnid,0 as rc,0 as pc from government_info where parentid=querycate order by input_time desc LIMIT lmt OFFSET (intpg-1)*lmt loop
recs.classname='';
recs.rc=rowcount;
recs.pc=pagecount;
--根据columnid来查询classname<开始>
DECLARE
cs record;
BEGIN
select into cs * from column_structure where classid=recs.columnid;
if not found then
else
recs.classname=cs.classname;
end if;
END;
--根据columnid查询<结束>*/
return next recs;--返回数据集
end loop;
ELSE
raise notice 'The cs parentid not 0';
--统计总记录数:
--select from government_info
select into rowcount count(*)as n from government_info where columnid in (select classid from column_structure where parentpath like '%,'||querycate||',%' or parentid=querycate or classid=querycate);
pagecount:=(rowcount+lmt-1)/lmt;
IF intpg>pagecount THEN
intpg=pagecount;
END IF;

for recs in select info_id,title,title as classname,input_time,columnid,0 as rc,0 as pc from government_info where columnid in (select classid from column_structure where parentpath like '%,'||querycate||',%' or parentid=querycate or classid=querycate) order by input_time desc LIMIT lmt OFFSET (intpg-1)*lmt loop
recs.classname='';
recs.rc=rowcount;
recs.pc=pagecount;
--根据columnid来查询classname<开始>
DECLARE
cs record;
BEGIN
select into cs * from column_structure where classid=recs.columnid;
if not found then
else
recs.classname=cs.classname;
end if;
END;
--根据columnid查询<结束>*/
--raise notice 'title:%',recs.title;
return next recs;--返回数据集
end loop;
END IF;--Parentid=0的判断
END IF;--FOUND
END;
--判断parentid<结束>
--根据所选部门查询<结束>
END IF;

RAISE NOTICE 'rowcount:%,pagecount:%',rowcount,pagecount;
return;--默认为空

END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
ALTER FUNCTION pagination_info(kw character varying, querycate bigint, flg bigint, lmt bigint, intpg bigint) OWNER TO postgres;

使用方法如下：
select * from pagination_info('',2,2,16,1) as (info_id integer,title character varying,classname character varying,input_time timestamp,classid bigint,rc integer,pc integer)--按部门id查询
select * from pagination_info('',15,1,16,1) as (info_id integer,title character varying,classname character varying,input_time timestamp,classid bigint,rc integer,pc integer)--按部门id查询
select * from pagination_info('s',1,0,16,1) as (info_id integer,title character varying,classname character varying,input_time timestamp,classid bigint,rc integer,pc integer)--按部门id查询

-------------------------------------------------------postgreSQL 8.0+版(以上)-----------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION pagination_info(character varying, bigint, bigint, bigint, bigint) RETURNS SETOF record AS
'DECLARE
kw alias for $1;--关键词
querycate alias for $2;--查询的部门
flg alias for $3;--0表示按关键字查询,1表示非关键词查询,2表示默认查询
lmt alias for $4;--每页显示记录数
page alias for $5;--待显示页码
recs record;--返回的集合
rowcount integer;--记录总数
pagecount integer;--总页数
intpg integer;--当前页数
pagerecs record;--统计页面的记录集合
BEGIN
rowcount:=0;
pagecount:=0;
intpg:=page;
--根据flg开判断查询类型.：
IF flg = 0 THEN
RAISE NOTICE ''select by keyword %'',$1;
--采用关键字来查询：
--统计总记录数:
--select from government_info
select into rowcount count(*)as n from government_info WHERE title LIKE ''%''||kw||''%'';
pagecount:=(rowcount+lmt-1)/lmt;
IF intpg>pagecount THEN
intpg=pagecount;
END IF;

FOR recs IN SELECT info_id,title,title as classname,input_time,columnid,0 as rc,0 as pc FROM government_info WHERE title LIKE ''%''||kw||''%'' LIMIT lmt OFFSET (intpg-1)*lmt LOOP
recs.classname='''';
recs.rc=rowcount;
recs.pc=pagecount;
--根据columnid来查询classname<开始>
DECLARE
cs record;
BEGIN
select into cs * from column_structure where classid=recs.columnid;
if not found then
else
recs.classname=cs.classname;
end if;
END;
--根据columnid查询<结束>
raise notice ''%'',recs.classname;
return next recs;--返回数据集
END LOOP;
ELSIF flg = 2 THEN
RAISE NOTICE ''SELECT BY DEFAULT'';
--统计总记录数:
--select from government_info
select into rowcount count(*)as n from government_info;
pagecount:=(rowcount+lmt-1)/lmt;
IF intpg>pagecount THEN
intpg=pagecount;
END IF;
--默认查询<开始>
FOR recs IN select info_id,title,title as classname,input_time,columnid,0 as rc,0 as pc from government_info order by input_time desc LIMIT lmt OFFSET (intpg-1)*lmt LOOP
recs.classname='''';
recs.rc=rowcount;
recs.pc=pagecount;
--根据columnid来查询classname<开始>
DECLARE
cs record;
BEGIN
select into cs * from column_structure where classid=recs.columnid;
if not found then
else
recs.classname=cs.classname;
end if;
END;
--根据columnid查询<结束>
return next recs;--返回数据集
END LOOP;
raise notice ''%'',recs.classname;
--默认查询<结束>
ELSE
RAISE NOTICE ''select by departmen %'',$2;
--根据所选部门查询<开始>
--判断parentid<开始>
DECLARE
--parentid integer;--上级ID
cs record;--从column_structure中查找parentid
BEGIN
SELECT INTO cs *FROM column_structure WHERE classid=querycate;
IF FOUND THEN
RAISE NOTICE ''I found the classid %'',querycate;
IF cs.parentid=0 THEN
raise notice ''The cs parentid==0'';
--统计总记录数:
--select from government_info
select into rowcount count(*)as n from government_info where parentid=querycate;
pagecount:=(rowcount+lmt-1)/lmt;
IF intpg>pagecount THEN
intpg=pagecount;
END IF;

for recs in select info_id,title,title as classname,input_time,columnid,0 as rc,0 as pc from government_info where parentid=querycate order by input_time desc LIMIT lmt OFFSET (intpg-1)*lmt loop
recs.classname='''';
recs.rc=rowcount;
recs.pc=pagecount;
--根据columnid来查询classname<开始>
DECLARE
cs record;
BEGIN
select into cs * from column_structure where classid=recs.columnid;
if not found then
else
recs.classname=cs.classname;
end if;
END;
--根据columnid查询<结束>*/
return next recs;--返回数据集
end loop;
ELSE
raise notice ''The cs parentid not 0'';
--统计总记录数:
--select from government_info
select into rowcount count(*)as n from government_info where columnid in (select classid from column_structure where parentpath like ''%,''||querycate||'',%'' or parentid=querycate or classid=querycate);
pagecount:=(rowcount+lmt-1)/lmt;
IF intpg>pagecount THEN
intpg=pagecount;
END IF;

for recs in select info_id,title,title as classname,input_time,columnid,0 as rc,0 as pc from government_info where columnid in (select classid from column_structure where parentpath like ''%,''||querycate||'',%'' or parentid=querycate or classid=querycate) order by input_time desc LIMIT lmt OFFSET (intpg-1)*lmt loop
recs.classname='''';
recs.rc=rowcount;
recs.pc=pagecount;
--根据columnid来查询classname<开始>
DECLARE
cs record;
BEGIN
select into cs * from column_structure where classid=recs.columnid;
if not found then
else
recs.classname=cs.classname;
end if;
END;
--根据columnid查询<结束>*/
--raise notice ''title:%'',recs.title;
return next recs;--返回数据集
end loop;
END IF;--Parentid=0的判断
END IF;--FOUND
END;
--判断parentid<结束>
--根据所选部门查询<结束>
END IF;

RAISE NOTICE ''rowcount:%,pagecount:%'',rowcount,pagecount;
return;--默认为空
END;'
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

---------------------------------------------------------------postgreSQL 7.4.16版---------------------------------------------------------------------------------------