Search code examples
sqlpostgresqlpgadmin

Postgresql Error - Function return type mismatch

I'm looking for some help with a SQL function I am defining under pgAdmin3 for PostGreSQL.

It is a simple function supposed to calculate a ratio given a certain id but when I try to add the function, I get an error message.
Here is the code for the function:

CREATE OR REPLACE FUNCTION data.func_net_exposure(id_fund_arg text)
RETURNS real AS

$BODY$

DECLARE
    net_exposure real;
    AUM smallint;

BEGIN
    AUM := (SELECT sum(cash_fund_total) from main.main_cash where id_fund = id_fund_arg); 
    net_exposure := (SELECT ROUND(sum(exposure_eur)/(100*AUM)) from main.main_inventory where id_fund = id_fund_arg); 
    return net_exposure;
END;

$BODY$

And here is the error message I get when I try to add the function:

An error has occurred:

13:13:54: Error: ERROR: return type mismatch in function declared to return real DETAIL: Function's final statement must be SELECT or INSERT/UPDATE/DELETE RETURNING. CONTEXT: SQL function "func_net_exposure"

Any clue on how to solve this error?

Solution

  • The system thinks the function's language is SQL and therefore expects the last statement to be a SELECT or ... RETURNING. The language should be PL/pgSQL. Add a language specification like:

    CREATE OR REPLACE FUNCTION data.func_net_exposure(id_fund_arg text)
RETURNS real AS

$BODY$

...

$BODY$
LANGUAGE PLpgSQL;