PostgreSQL stored procedure returns a dataset instance, and postgresql Stored Procedure
Here we use an instance to demonstrate how to return data sets in the PostgreSQL stored procedure.
1. Prepare the data table first.
<pre name="code" class="sql">//member_categorycreate 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<>'');//membercreate 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 ('Senior 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 ('ordinary 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 ('000000', 'wuyilun ', 1, 1000001, 100000.00 14117777, '2017-12-12', 100000,188, '1970 @ qq.com ', 0, 1, 'b3-000000', 'Super platinum member, all ticket-free', '2017-01-15 ', 123456 45, 'South China tech outlet '); 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 ('123', 'lilu ', 2, 1000002, 1000.00, 100000,188 141177234, '2017-12-12', 1990, '2017 @ qq.com ', 0, 1, 'b3-100 ','... ', '2014-01-15', 2014 45, 'South China tech outlet '); 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 ('000000', 'hongjinbao ', 3, 1000003, 1000.00 14117234, '2017-12-12 ', 12345678, '1970 @ qq.com ', 0, 1, 'b3-1000 ','... ', '2014-01-15', 2014 45, 'South China tech outlet '); 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 ('000000', 'Jackie Chan ', 1000004, 4,100.00 14117723, '2017-12-12', 100000,188, '2017 @ qq.com ', 0, 1, 'b3-123456 ','... ', '2014-01-15', 2014 45, 'South China tech outlet '); 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 ('20170101', 'fan bing', 1000005, 4,100.00 14117327, '2017-12-12 ', 100000,188, '2017 @ qq.com ', 0, 1, 'b3-123456 ','... ', '2014-01-15', 2014 45, 'South China tech outlet ');
3. Create a stored procedure
-- Call the Stored Procedure f_get_member_info and return all member information -- memberType: member type 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 $ declarerec 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. Call the Stored Procedure
-- Call the Stored Procedure f_get_member_info example select * from f_get_member_info (4, 1, '', 'South China tech outlet ') as member (member_num text, mname text, name text, discount_rate real, account numeric (), 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