PostgreSQL Stored Procedure return dataset instance
Here we use an instance to demonstrate how to return a dataset from a PostgreSQL stored procedure.
1. Prepare the data table 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, 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 & nbsp; onaccount int;
Alter table member add & nbsp; 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 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, address, tip, start_date, valid_date, password, store_name) values ('000000', 'wuyilun ', 1, 1000001, 100000.00 14117777, '2017-12-12', 100000,188, '2017 @ qq.com ', 0, 1, 'b3-000000', 'platinum member, all tickets-free ', '2017-01-15', 440 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', 'hongjin Bao ', 3, 1000003, 1000.00 14117234, '2017-12-12', 100000,188, '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 ('20170901', 'Jackie Chan ', 1000004, 4,100.00 14117723, '2017-12-12', 100000,188, '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 ('20140901', 'fan bing', 1000005, 4,100.00 14117327, '2017-12-12 ', 100000,188, '2017 @ qq.com', 0, 1, 'b3-100 ', '... ', '2014-01-15', 2014 45, 'South China tech outlet ');
3. Create a stored procedure
-- Call the Stored Procedure f_get_member_info to 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
$
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_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
-- Example of calling the Stored Procedure f_get_member_info
Select * from f_get_member_info (4, 1, '', 'South China tech 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
------------------------------------ Lili split line ------------------------------------
Install PostgreSQL 6.3 on yum in CentOS 9.3
PostgreSQL cache details
Compiling PostgreSQL on Windows
Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu
Install and configure phppgAdmin on Ubuntu
Install PostgreSQL9.3 on CentOS
Configure a Streaming Replication cluster in PostgreSQL
How to install PostgreSQL 7/6 and phpPgAdmin in CentOS 5/6. 4
------------------------------------ Lili split line ------------------------------------
PostgreSQL details: click here
PostgreSQL: click here
This article permanently updates the link address: