PostgreSQL存储过程返回数据集实例

这里用一个实例来演示PostgreSQL存储过程如何返回数据集。1 首先准备数据表//member_category
create table member_category（id serial, name text, discount_rate real, base_integral integer）;
alter table member_category add primary key（id）;
alter table member_category add check（name<>""）;//member
create table member（id serial, member_num text, name text, category_id integer, account numeric（16,2）, integral integer, phone text, birthday date, qq integer, email text, status integer, address text, tip text, start_date date, valid_date integer, password text, creator integer, store_name text）;
alter table member add primary key（id）;
alter table member add foreign key（creator） references employee;
alter table member add foreign key（category_id） references member_category;
alter table member add onaccount int;alter table member add  onaccount int;
alter table member add  store_name text;2 插入测试数据insert into member_category（name, discount_rate, base_integral） values（"白金会员", 6.5, 10000）;
insert into member_category（name, discount_rate, base_integral） values（"高级会员", 7.5, 1000）;
insert into member_category（name, discount_rate, base_integral） values（"中级会员", 8.5, 100）;
insert into member_category（name, discount_rate, base_integral） values（"普通会员", 9.5, 10）;insert into member（member_num, name, category_id, account, integral, phone, birthday, qq, email, onaccount, status, address, tip, start_date, valid_date, password, store_name） values（"1000001", "wuyilun", 1, 100000.00, 100000, 18814117777, "1990-12-12", 12345678, "123456@qq.com", 0, 1, "B3-440", "超白金会员,一切免单", "2014-01-15", 1000000, 12345, "华南理工门店"）;
insert into member（member_num, name, category_id, account, integral, phone, birthday, qq, email, onaccount, status, address, tip, start_date, valid_date, password, store_name） values（"1000002", "李小路", 2, 1000.00, 100000, 188141177234, "1990-12-12", 12345678, "123456@qq.com", 0, 1, "B3-444", "...", "2014-01-15", 1000000, 12345, "华南理工门店"）;
insert into member（member_num, name, category_id, account, integral, phone, birthday, qq, email, onaccount, status, address, tip, start_date, valid_date, password, store_name） values（"1000003", "洪金包", 3, 1000.00, 100000, 18814117234, "1990-12-12", 12345678, "123456@qq.com", 0, 1, "B3-443", "...", "2014-01-15", 1000000, 12345, "华南理工门店"）;
insert into member（member_num, name, category_id, account, integral, phone, birthday, qq, email, onaccount, status, address, tip, start_date, valid_date, password, store_name） values（"1000004", "成龙", 4, 100.00, 100000, 18814117723, "1990-12-12", 12345678, "123456@qq.com", 0, 1, "B3-442", "...", "2014-01-15", 1000000, 12345, "华南理工门店"）;
insert into member（member_num, name, category_id, account, integral, phone, birthday, qq, email, onaccount, status, address, tip, start_date, valid_date, password, store_name） values（"1000005", "范兵兵", 4, 100.00, 100000, 18814117327, "1990-12-12", 12345678, "123456@qq.com", 0, 1, "B3-441", "...", "2014-01-15", 1000000, 12345, "华南理工门店"）;3 创建存储过程--调用存储过程f_get_member_info, 返回会员的所有信息
--memberType:会员类型 status:会员状态 findCondition:查询条件（卡号/电话/姓名） store_name:商店名称
create or replace function f_get_member_info（memberType int, status int, findCondition text, store_name text） returns setof record as
$$
declare
rec record;
begin
for rec in EXECUTE "select m.member_num, m.name, m_t.name, m_t.discount_rate, m.account, m.integral, m.phone, m.birthday, m.qq, m.email, m.onAccount, m.status, m.address, m.tip, m.start_date, m.valid_date, m.store_name from member m, member_category m_t where m.category_id = m_t.id and m_t.id = "|| memberType ||" and m.status = "|| status ||" and m.store_name = """|| store_name ||""" and （m.member_num like ""%"|| findCondition ||"%"" or m.name like ""%"|| findCondition ||"%"" or m.phone like ""%"|| findCondition ||"%""）;" loop
return next rec;
end loop;
return;
end
$$
language "plpgsql";4 调用存储过程--调用存储过程f_get_member_info示例
select * from f_get_member_info（4, 1, "", "华南理工门店"） as member（member_num text,mname text,name text,discount_rate real,account numeric（16,2）,integral int,phone text,birthday date,qq int,email text,onAccount int,status int,address text,tip text,start_date date,valid_date int,store_nam text）;5 测试结果------------------------------------华丽丽的分割线------------------------------------CentOS 6.3环境下yum安装PostgreSQL 9.3 http://www.linuxidc.com/Linux/2014-05/101787.htmPostgreSQL缓存详述 http://www.linuxidc.com/Linux/2013-07/87778.htmWindows平台编译 PostgreSQL http://www.linuxidc.com/Linux/2013-05/85114.htmUbuntu下LAPP（Linux+Apache+PostgreSQL+PHP）环境的配置与安装 http://www.linuxidc.com/Linux/2013-04/83564.htmUbuntu上的phppgAdmin安装及配置 http://www.linuxidc.com/Linux/2011-08/40520.htmCentOS平台下安装PostgreSQL9.3 http://www.linuxidc.com/Linux/2014-05/101723.htmPostgreSQL配置Streaming Replication集群 http://www.linuxidc.com/Linux/2014-05/101724.htm如何在CentOS 7/6.5/6.4 下安装PostgreSQL 9.3 与 phpPgAdmin http://www.linuxidc.com/Linux/2014-12/110108.htm------------------------------------华丽丽的分割线------------------------------------PostgreSQL 的详细介绍：请点这里
PostgreSQL 的下载地址：请点这里本文永久更新链接地址