PostgreSQL函数错误

Question

我需要一些帮助来解决我的PostgreSQL 9.1函数的问题：

CREATE OR REPLACE FUNCTION COMMISSION_MARGIN(Aiid NUMERIC) RETURNS NUMERIC AS $$
    DECLARE
           COMISSAO NUMERIC;
           TOTAL_SEM_IMPOSTO NUMERIC;
    BEGIN
            COMISSAO='"SELECT SUM(ILA.QUANTITY) FROM INVOICE_LINE_AGENT AS ILA
                     INNER JOIN ACCOUNT_INVOICE_LINE AS AIL ON ILA.INVOICE_LINE_ID = AIL.ID
                     INNER JOIN ACCOUNT_INVOICE AS AI ON AI.ID = AIL.INVOICE_ID
                     WHERE AI.ID = Aiid GROUP BY AI.ID"';

            TOTAL_SEM_IMPOSTO='"SELECT SUM(AIL.PRICE_SUBTOTAL) FROM INVOICE_LINE_AGENT AS ILA
                     INNER JOIN ACCOUNT_INVOICE_LINE AS AIL ON ILA.INVOICE_LINE_ID = AIL.ID
                     INNER JOIN ACCOUNT_INVOICE AS AI ON AI.ID = AIL.INVOICE_ID
                     WHERE AI.ID = Aiid GROUP BY AI.ID"';
            RETURN ((COMISSAO/TOTAL_SEM_IMPOSTO)*100);
    END;
$$ LANGUAGE plpgsql;

函数调用：

SELECT 
     COMMISSION_MARGIN(AI.ID) AS "Margin (%)"
FROM
    ACCOUNT_INVOICE AS AI
INNER JOIN OTHER_TABLE AS OT ON ......

但我收到了这样的信息：

ERROR:  column "SELECT SUM(ILA.QUANTITY) FROM INVOICE_LINE_AGENT AS ILA
  " doesn't exists
CONTEXT:  PL/pgSQL function "commission_margin" line 6 at attribution

Answer 1

您需要的是一个SELECT ... INTO，用于将sql查询返回的值放入这些变量中。类似于：

CREATE OR REPLACE FUNCTION COMMISSION_MARGIN(Aiid NUMERIC) RETURNS NUMERIC AS $$
    DECLARE
           COMISSAO NUMERIC;
           TOTAL_SEM_IMPOSTO NUMERIC;
    BEGIN
         SELECT SUM(ILA.QUANTITY) INTO COMISSAO 
           FROM INVOICE_LINE_AGENT AS ILA
                INNER JOIN ACCOUNT_INVOICE_LINE AS AIL 
                        ON ILA.INVOICE_LINE_ID = AIL.ID
                INNER JOIN ACCOUNT_INVOICE AS AI 
                        ON AI.ID = AIL.INVOICE_ID
          WHERE AI.ID = Aiid 
          GROUP BY AI.ID;

          SELECT SUM(ILA.PRICE_SUBTOTAL) INTO TOTAL_SEM_IMPOSTO 
            FROM INVOICE_LINE_AGENT AS ILA
                     INNER JOIN ACCOUNT_INVOICE_LINE AS AIL 
                             ON ILA.INVOICE_LINE_ID = AIL.ID
                     INNER JOIN ACCOUNT_INVOICE AS AI 
                             ON AI.ID = AIL.INVOICE_ID
           WHERE AI.ID = Aiid 
           GROUP BY AI.ID;

         RETURN ((COMISSAO/TOTAL_SEM_IMPOSTO)*100);
    END;
$$ LANGUAGE plpgsql;

作为一个加分，你应该在阅读异常处理时考虑一下。因为在使用SELECT ... INTO语句时，您可能会遇到一些问题，比如查询返回多个行，或者根本没有返回任何行。请参阅此处的文档：Executing a Query with a Single-Row Result

Answer 2

在很大程度上简化了，不运行两个单独的查询，而只使用一个SQL函数：

CREATE OR REPLACE FUNCTION COMMISSION_MARGIN(Aiid NUMERIC)
  RETURNS NUMERIC AS
$func$

SELECT (SUM(ILA.QUANTITY) * 100) / SUM(AIL.PRICE_SUBTOTAL)
FROM   INVOICE_LINE_AGENT   ILA
JOIN   ACCOUNT_INVOICE_LINE AIL ON ILA.INVOICE_LINE_ID = AIL.ID
JOIN   ACCOUNT_INVOICE      AI  ON AI.ID = AIL.INVOICE_ID
WHERE  AI.ID = Aiid

$func$ LANGUAGE sql;