主页 / dba / 问题 / 290693
Accepted
tucomax
Asked: 2021-04-29 22:23:14 +0800 CST

如何在 postgresql 中创建一个带有两个参数的函数以从列表中返回计数？

  • 772

我正在尝试使用表中的两个参数 year 和 code 执行一个函数，以返回给定代码和年份的计数。表是这样的：

CREATE TABLE orders (
    id INT NOT NULL, 
    code INT NOT NULL, 
    service_id INT NOT NULL, 
    status CHARACTER VARYING(50), 
    creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status_date TIMESTAMP,
    created_user INT
    );

我能够使用下面的函数返回计数，但是当我为年份和没有计数的代码提供值时，它返回 NULL 但应该返回零，我该怎么做？：

CREATE OR REPLACE FUNCTION yearly_orders
    (year INT, order_cat orders.code%type) RETURNS INT AS $$
    
DECLARE
    total INT DEFAULT 0;
    
BEGIN
    SELECT COUNT(orders.code) INTO total
    FROM orders
    WHERE status = 'Requested' AND EXTRACT(YEAR FROM creation) = year::INT AND orders.code = order_cat
    GROUP BY year, orders.code;
    RETURN total;
END;
$$LANGUAGE plpgsql;

数据类似于：

INSERT INTO orders 
(id, code, service_id, status, creation, status_date, created_user) 
VALUES 
(100,2394,558151,'Requested','2019-06-16 11:12','2019-06-18 14:08',1);
stored-procedures postgresql

2 个回答

  1. Best Answer

    这会起作用：

    CREATE OR REPLACE FUNCTION yearly_orders(_year int, _order_cat int)
  RETURNS int
  LANGUAGE sql STABLE AS
$func$
SELECT count(*)::int
FROM   orders o
WHERE  o.status = 'Requested'
AND    o.creation >= to_timestamp(_year::text, 'YYYY')
AND    o.creation <  to_timestamp((_year + 1)::text, 'YYYY')
AND    o.code = _order_cat;
$func$;

    从integerto的转换timestamp实际上取决于您传递的确切内容以及它应该意味着什么。您可能想要to_timestamp(_year::text, 'YY')...

    考虑一下 Akina 评论中的列表。
    开始阅读有关SQL 函数的手册。
    之后看看PL/pgSQL 函数。

    概述：

    • 2

  2. 看到问题已重新打开，我也会发布我的解决方案。

    “订单”表

    CREATE TABLE orders (
    id INT NOT NULL, 
    code INT NOT NULL, 
    service_id INT NOT NULL, 
    status CHARACTER VARYING(50), 
    creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status_date TIMESTAMP,
    created_user INT
    );

    记录

    INSERT INTO orders 
(id, code, service_id, status, creation, status_date, created_user) 
VALUES 
(100,2394,558151,'Requested','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Fulfilled','2018-06-16 11:12','2018-06-18 14:08',1),
(100,2394,558151,'Requested','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2395,558152,'Requested','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2396,558153,'Requested','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Requested','2019-06-16 11:12','2019-06-18 14:08',1);

    测试你的陈述

    SELECT EXTRACT(YEAR FROM creation) AS year, 
                        orders.code, 
                        COUNT(orders.code) 
                FROM orders
                WHERE status = 'Requested' AND EXTRACT(YEAR FROM creation) = 2019 AND orders.code = 2394
                GROUP BY year, orders.code
                ORDER BY year ASC

    退货

    年份 | 代码 | 数数
:--- | ---: | ----:
2019 | 第2394章 3

    功能

    CREATE OR REPLACE FUNCTION 
yearly_orders(inyear numeric DEFAULT 0, incode INT DEFAULT 0) 
RETURNS TABLE (retyear numeric, retcode bigint, retcout bigint) AS 
$$
DECLARE
    retcount int DEFAULT 0;
    retcode int DEFAULT 0;
    retyear numeric DEFAULT 0;
BEGIN
    RETURN QUERY SELECT CAST(EXTRACT(year FROM creation) as numeric), 
                        CAST(orders.code as bigint), 
                        COUNT(orders.code)
                FROM orders
                WHERE status = 'Requested' 
                AND EXTRACT(YEAR FROM creation) = inyear 
                AND orders.code = incode
                GROUP BY EXTRACT(year FROM creation), orders.code
                ORDER BY retyear ASC;

END;
$$
LANGUAGE plpgsql;

    执行功能

    select * from yearly_orders(2019, 2394);

    退货

    退休| 重新编码 | 撤退
------: | ------: | ------:
   2019 | 第2394章 3

    db<>小提琴

    可以在此处找到尝试此操作的 db<>fiddle 。

    • 1

相关问题