PostgreSQL stored procedure returns a dataset instance, and postgresql Stored Procedure

Source: Internet
Author: User

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





Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.