1 返回结果集的存储过程
-- drop FUNCTION getall();
CREATE or REPLACE FUNCTION getall()
RETURNS SETOF users AS
$BODY$
SELECT * from users;
$BODY$
language SQL; -- 这里使用的SQL
2 返回单个数值的的存储过程
-- drop FUNCTION adduser2(CHARACTER VARYING);
CREATE or REPLACE FUNCTION adduser2(usernamezll varchar)
RETURNS int2 AS
$body$
BEGIN
insert into users(username) VALUES(usernamezll);
RETURN 5;
end
$body$
language plpgsql; -- 这里使用的是plpgsql
3 返回表联查的结果集
-- 先创建一个表联查之后的符合类型，将此类型作为函数的返回类型
CREATE TYPE fenshu_u as (id int,kemu VARCHAR,fenshu int,userid INT,username VARCHAR);
CREATE function getfenshu() RETURNS SETOF fenshu_u AS
$$
declare
rec fenshu_u%rowtype;
BEGIN
for rec in SELECT t2.*,t1.username from fenshu as t2 LEFT JOIN users t1 on t2.userid=t1.id WHERE t2.userid>10 loop
return next rec;
end loop;
return;
END
$$ LANGUAGE plpgsql;
