PostgreSQL 函数的使用

发表于 | 分类于 | 浏览量:

创建修改函数

参数类型不同为不同函数

返回值为数字类型

1
2
3
4
5
6
7
CREATE OR REPLACE FUNCTION cal_avg_time(IN starttime TIMESTAMP, IN endtime TIMESTAMP)
  RETURNS NUMERIC
AS $$
SELECT avg(costtime) avgtime
FROM data_statistics
WHERE f_starttime BETWEEN starttime AND endtime
$$ LANGUAGE SQL;

返回值为table类型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE OR REPLACE FUNCTION total_sucfail(IN starttime TIMESTAMP, IN endtime TIMESTAMP)
  RETURNS TABLE(solutions TEXT, sucnum BIGINT, failnum BIGINT)
AS $$
SELECT
  solutions,
  SUM(CASE WHEN f_status = 100
    THEN 1
      ELSE 0 END)  sucnum,
  SUM(CASE WHEN f_status = 100
    THEN 0
      ELSE 1 END)  failnum
FROM data_statistics
WHERE f_starttime BETWEEN starttime AND endtime
  AND f_finishtime BETWEEN starttime AND endtime
GROUP BY solutions
$$ LANGUAGE SQL;

查看函数

1
2
3
4
5
6
dbname=# \df
 Schema |     Name      |                   Result data type                   |                            Argument data types                             |  Type  
--------+---------------+------------------------------------------------------+----------------------------------------------------------------------------+--------
 public | cal_avg_time  | numeric                                              | starttime timestamp without time zone, endtime timestamp without time zone | normal
 public | total_sucfail | TABLE(solutions text, sucnum bigint, failnum bigint) | starttime timestamp without time zone, endtime timestamp without time zone | normal
(2 rows)

调用函数

1
2
SELECT cal_avg_time('2018-04-01 00:00', '2018-04-02 00:00');
SELECT total_sucfail('2018-04-01 00:00', '2018-04-02 00:00');

当参数类型为Date时，传入YYYY-MM-DD HH:MM:SS格式字符串不会报错，但是只有YYYY-MM-DD生效

删除函数

1
2
dbname=# DROP FUNCTION cal_avg_time(timestamp,timestamp);
dbname=# DROP FUNCTION total_sucfail(timestamp,timestamp);

参考资料:
官方手册
SQL优化（四） PostgreSQL存储过程
Postgresql中存储过程(函数)调用存储过程(函数)时应用注意的问题