示例表:
CREATE TABLE students
(
sno character varying(32) NOT NULL, --学号
sname character varying(64) NOT NULL, --姓名
sage integer, --年龄
scid character varying(32), -- 班级id
scname character varying(64), -- 班级名称
CONSTRAINT students_pkey PRIMARY KEY (sno)
)
这个表记录了学生的学号,姓名以及班级等信息。部分测试数据如下所示:
这里的需求是查出班级以及这个班里的学生,查询函数如下:
CREATE OR REPLACE FUNCTION getstudents(
OUT cid character varying,
OUT cnm character varying,
OUT sts character varying[])
RETURNS SETOF record AS
$BODY$
declare
sc record;
begin
for sc in (select distinct(scid),scname from students) loop
cid := sc.scid;
cnm := sc.scname;
select array(select sno||' '||sname||' '||sage from students where scid = sc.scid order by sage) into sts;
return next;
end loop;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION getstudents()
OWNER TO postgres;
查询:
select * from getStudents();
结果:
语法糖衣
以上是比较简单传统的方式,还有更加简单的方式,如下:
select scid, scname, array_agg(sno||' '||sname||' '||sage)as stus from students group by scid,scname;
array_agg是特殊的聚集函数,可以将属于同一个的group的记录放到一个数组中。很好用!