PostgreSQL stored procedure returns data set instance

Here is an example that demonstrates how the PostgreSQL stored procedure returns a dataset.

1 Prepare the Data sheet first

 < pre name = "code" class = "SQL" > / / 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 test data

Insert into member_category(name, discount_rate, base_integral) values(' platinum member ', 6.5, 10000);
Insert into member_category(name, discount_rate, base_integral) values(' prime member ', 7.5, 1000);
Insert into member_category(name, discount_rate, base_integral) values(' intermediate member ', 8.5, 100);
Insert into member_category(name, discount_rate, base_integral) values(' general members ', 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, '[email protected]', 0, 1, 'b3-440 ',' super platinum member, all free of charge ', '2014-01-15', 1000000, 12345, 'south China technology store ');
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', 'li xiaolu ', 2, 1000.00, 100000, 188141177234, '1990-12-12', 12345678, '[email protected]', 0, 1,' b3-444 ', '... ', '2014-01-15', 1000000, 12345, 'south China institute of technology store ');
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', 'hongjinbao ', 3, 1000.00, 100000, 18814117234, '1990-12-12', 12345678, '[email protected]', 0, 1,' b3-443 ', '... ', '2014-01-15', 1000000, 12345, 'south China institute of technology store ');
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', 'Jackie chan ', 4, 100.00, 100000, 18814117723, '1990-12-12', 12345678, '[email protected]', 0, 1, 'b3-442 ', '... ', '2014-01-15', 1000000, 12345, 'south China institute of technology store ');
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', 'fanbing ', 4, 100.00, 100000, 18814117327, '1990-12-12', 12345678, '[email protected]', 0, 1,' b3-441 ', '... ', '2014-01-15', 1000000, 12345, 'south China institute of technology store ');


Create stored procedures
Call the stored procedure f_get_member_info to return all information about the member
--memberType: member status: member status: findCondition: query condition (card number/phone number/name) store_name: 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;
The begin
For rec in EXECUTE 'select m.mbber_num, m.name, m_t.name, m_t.discount_rate, m.account, m.i. M.valid_date, m.sdre_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.status = '|| store_name |0 |1 'and (m.mumber_num like '%' |2 |3 findCondition | | '%' or m.n ame like ' '%' | | findCondition | | '%', 'or m.p hone like' '%' | | findCondition | | '%'); 'loop
Return next rec;
End loop;
The return;
The end
$$
Language 'PLPGSQL';

4 call the stored procedure
-- example of calling the stored procedure f_get_member_info
Select * from f_get_member_info(4, 1, ", 'south China technology store ') 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 The text, the start_date date, valid_date int, store_nam text);

5 test results




The PostgreSQL stored procedure returns an instance of the dataset