PostgreSQL每日一贴--PL/pgSQL存储过程示例

代码样例

--1. psql客户端调用存储过程时打印调试信息 RAISE NOTICE
--2. 调用函数  perform function_name(...)
--3. 声明变量
--4. 返回值
--5. 替换变量
drop table if exists test_table;
CREATE TABLE test_table(start_time timestamp, end_time timestamp, str_flag varchar(200));

CREATE OR REPLACE FUNCTION test_function() RETURNS double precision AS $$
DECLARE
    start_time timestamp;
	end_time timestamp;
	time_consuming double precision;
BEGIN
    time_consuming := -1;
    
	select clock_timestamp() into start_time;
	RAISE NOTICE 'Quantity here is %', start_time;
	
	perform pg_sleep(3);
	
	select clock_timestamp() into end_time;
	RAISE NOTICE 'Quantity here is %', end_time;
	
	select round(EXTRACT(EPOCH from end_time)-EXTRACT(EPOCH from start_time)) into time_consuming;
	
    insert into test_table values(start_time, end_time, 'TEST_CLOCK_TIMESTAMP');
	
	return time_consuming;
END;
$$ LANGUAGE plpgsql;

测试结果

postgres=> select * from test_function();
NOTICE:  Quantity here is 2015-01-24 20:38:50.38032
NOTICE:  Quantity here is 2015-01-24 20:38:53.384296
 test_function 
---------------
             3
(1 row)


postgres=> select * from test_table;
        start_time         |          end_time          |       str_flag       
---------------------------+----------------------------+----------------------
 2015-01-24 20:38:50.38032 | 2015-01-24 20:38:53.384296 | TEST_CLOCK_TIMESTAMP
(1 row)


postgres=>