PostgreSQL -如何在用户定义的函数中使用For循环和Return语句

dkqlctbz 于 2023-10-18 发布在 PostgreSQL

关注(0)|答案(2)|浏览(105)

我想计算重复的行，这样函数只给出冗余的计数，而不是整个计数，例如：当一行出现三次并且count函数返回3时，该行仅冗余2个额外的占用;因此，该函数应返回2，然后将冗余计数相加以获得重复行的总数。下面是我的代码

CREATE OR REPLACE FUNCTION count_duplicates()
 RETURNS SETOF INTEGER
 AS $BODY$
 BEGIN
     WITH total AS (
         SELECT brand, naira_price, city, state, phone_condition, color, COUNT(*) AS qty
         FROM phones
         GROUP BY brand, naira_price, city, state, phone_condition, color
         HAVING COUNT(*) > 1
     )

     FOR i IN (a.qty) as dup
     LOOP
         RETURN QUERY SUM(i-1) FROM total a;
     END LOOP;
 END;
 $BODY$ LANGUAGE plpgsql;

函数返回错误消息，而不是ERROR: syntax error at or near "FOR"LINE 12: FOR i IN (a.qty) as dup帮助是赞赏。

postgresql

来源：https://stackoverflow.com/questions/77267781/postgresql-how-to-use-for-loop-and-return-statement-in-a-user-defined-function

2条答案

按热度按时间

u3r8eeie1#

不能在一个语句中混合使用SQL和PL/pgSQL。但是根据你的描述，你想要这个查询：

SELECT brand, naira_price, city, state, phone_condition, color,
       COUNT(*) - 1 AS superfluous
FROM phones
GROUP BY brand, naira_price, city, state, phone_condition, color
HAVING COUNT(*) > 1;

赞(0）回复(0）举报 2023-10-18

00jrzges2#

有了@Laurenz Albe回答的线索--谢谢，我已经得到了我问题的答案：

WITH total AS (
    SELECT brand, naira_price, city, state, phone_condition, color,
        (COUNT(*) - 1) AS qty
    FROM phones
    GROUP BY brand, naira_price, city, state, phone_condition, color
    HAVING COUNT(*) > 1
)

SELECT SUM(a.qty) AS duplicate_rows FROM total a;

这将对表中的重复行求和，并返回单个求和值。

赞(0）回复(0）举报 2023-10-18

我来回答

PostgreSQL -如何在用户定义的函数中使用For循环和Return语句

2条答案

相关问题

热门标签

最新问答