PostgreSQL to call the stored procedure and return the dataset instance _postgresql

Here is an example to illustrate how the PostgreSQL stored procedure returns a dataset.

1. Prepare data table First

 code as follows:

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, p Hone text, Birthday date, qq integer, email text, status integer, address text, tip text, start_date date, valid_date inte Ger, 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

 code as follows:

Insert into Member_category (name, discount_rate, base_integral) VALUES (' Platinum Members ', 6.5, 10000);
Insert into Member_category (name, discount_rate, base_integral) VALUES (' Senior member ', 7.5, 1000);
Insert into Member_category (name, discount_rate, base_integral) VALUES (' Intermediate members ', 8.5, 100);
Insert into Member_category (name, discount_rate, base_integral) VALUES (' Ordinary members ', 9.5, 10);

Insert into member (Member_num, name, category_id, account, integral, phone, birthday, QQ, email, onaccount, status, Addres S, Tip, start_date, valid_date, password, store_name) VALUES (' 1000001 ', ' Wuyilun ', 1, 100000.00, 100000, 18814117777, ' 199 0-12-12 ', 12345678, ' 123456@qq.com ', 0, 1, ' b3-440 ', ' super Platinum Member, all exempt from single ', ' 2014-01-15 ', 1000000, 12345, ' South China Store ');
Insert into member (Member_num, name, category_id, account, integral, phone, birthday, QQ, email, onaccount, status, Addres S, Tip, start_date, valid_date, password, store_name) VALUES (' 1000002 ', ' Li Xiao ', 2, 1000.00, 100000, 188141177234, ' 1990-12- ', 12345678, ' 123456@qq.com ', 0, 1, ' b3-444 ', ' ... ', ' 2014-01-15 ', 1000000, 12345, ' South China Store ');
Insert into member (Member_num, name, category_id, account, integral, phone, birthday, QQ, email, onaccount, status, Addres S, Tip, start_date, valid_date, password, store_name) VALUES (' 1000003 ', ' Hong Jin package ', 3, 1000.00, 100000, 18814117234, ' 1990-12-1 2 ', 12345678, ' 123456@qq.com ', 0, 1, ' b3-443 ', ' ... ', ' 2014-01-15 ', 1000000, 12345, ' South China Store ');
Insert into member (Member_num, name, category_id, account, integral, phone, birthday, QQ, email, onaccount, status, Addres S, Tip, start_date, valid_date, password, store_name) VALUES (' 1000004 ', ' Jackie ', 4, 100.00, 100000, 18814117723, ' 1990-12-12 ') , 12345678, ' 123456@qq.com ', 0, 1, ' b3-442 ', ' ... ', ' 2014-01-15 ', 1000000, 12345, ' South China Store ');
Insert into member (Member_num, name, category_id, account, integral, phone, birthday, QQ, email, onaccount, status, Addres S, Tip, start_date, valid_date, password, store_name) VALUES (' 1000005 ', ' Fan ', 4, 100.00, 100000, 18814117327, ' 1990-12-12 ', 12345678, ' 123456@qq.com ', 0, 1, ' b3-441 ', ' ... ', ' 2014-01-15 ', 1000000, 12345, ' South China Store ');

3. Create a stored procedure

 code as follows:

--Call the stored procedure f_get_member_info to return all information about the member
--membertype: Member Type Status: Member State findcondition: Query condition (card number/telephone/name) Store_name: Store Name
Create or Replace function f_get_member_info (membertype int, status int, findcondition text, store_name text) returns Seto F 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_ca Tegory 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. Calling stored Procedures

 code as follows:

--Invoke Stored Procedure F_get_member_info sample
SELECT * from F_get_member_info (4, 1, ', ' South China Store ') as member (Member_num text,mname text,name text,discount_rate UNT 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. Test results