PostgreSQL Function Error

Question

I need some help to solve a problem with my PostgreSQL 9.1 function:

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;

Function call:

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

But I receive this message:

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

You write a string, but don't call the query you write. I'd recommend reading the documentation of functions some more. You have to execute your queries and then grab the results from them. Have a look at some of the other functions here on SO to get an idea. — Angelo Fuchs
– Angelo Fuchs, Commented Jul 29, 2014 at 18:14

Erwin Brandstetter · Accepted Answer · 2014-07-29 18:55:25Z

2

Largely simplify, don't run two separate queries and just use an SQL function instead:

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;

answered Jul 29, 2014 at 18:55

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1468044 Over a year ago

Thank you so much Erwin. I will also try this one.

Ihor Romanchenko · Accepted Answer · 2014-07-29 20:36:04Z

2

What you need is a SELECT ... INTO to put the values that your sql query returns into that variables. Something like:

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;

As a plus you should consider in reading about exception treatment. Because when you use the SELECT ... INTO statements you may encounter some problems as if your query returns more than one rows or no rows at all. See the docs here: Executing a Query with a Single-Row Result

edited Jul 29, 2014 at 20:36

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

answered Jul 29, 2014 at 18:16

Jorge Campos

23.8k7 gold badges65 silver badges95 bronze badges

1 Comment

user1468044 Over a year ago

Thank you Jorge, i will try this and let you know if it works there. By the way we both from Brazil. =0)

Collectives™ on Stack Overflow

PostgreSQL Function Error

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related