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

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 &nbsp;onaccount int;alter table member add &nbsp;store_name text;

2 Inserting 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 (' Advanced Members ', 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, ' [email protected] ', 0, 1, ' b3-440 ', ' super Platinum Member, all Free ', ' 2014-01-15 ', 1000000, 12345, ' scut 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, ' [email protected] ', 0, 1, ' b3-444 ', ' ... ', ' 2014-01-15 ', 1000000, 12345, ' scut 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 pack ', 3, 1000.00, 100000, 18814117234, ' 1990-12-1 2 ', 12345678, ' [email protected] ', 0, 1, ' b3-443 ', ' ... ', ' 2014-01-15 ', 1000000, 12345, ' scut 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 ', ' Dragon ', 4, 100.00, 100000, 18814117723, ' 1990-12-12 ' , 12345678, ' [email protected] ', 0, 1, ' b3-442 ', ' ... ', ' 2014-01-15 ', 1000000, 12345, ' scut 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, ' [email protected] ', 0, 1, ' b3-441 ', ' ... ', ' 2014-01-15 ', 1000000, 12345, ' scut store ');

3 Creating a Stored procedure

--Call the stored procedure F_get_member_info, return all information of the member

--membertype: Member Type status: Member Status findcondition: Query criteria (card number/Phone/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 a stored procedure

--Call the stored procedure f_get_member_info example SELECT * from F_get_member_info (4, 1, ', ' Scut stores ') 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 test Results

• This article is from: Linux Learning Tutorial Network

PostgreSQL stored procedure returns data set instance