CODEWITHSUNDEEP
postgresqlreturnplpgsql
Tom
Tom

Reputation: 21

Return multiple output variables from postgres function

When I run the function below, I get nulls in the two output values p_V_RESULT and p_V_ERROR_MSG. What could be the problem?

The RAISE INFO shows values assigned:

INFO:  p_V_RESULT,T,p_V_ERROR_MSG

CREATE OR REPLACE   FUNCTION public.CheckSessionFailed (
IN p_V_CHK_SESSION_ID   bigint,
IN p_V_ERROR_COMMENT    varchar(1000) ,
OUT p_V_RESULT          char(1)         ,
OUT p_V_ERROR_MSG       varchar(1000)  
) 
AS $$
DECLARE
    p_V_RESULT  char(1) := 'T';
    p_V_ERROR_MSG varchar(1000)  := NULL;
    errMsg text;
    errState text;
    errContext text;
    r_rec record;
BEGIN
    p_V_RESULT   := 'T';
    p_V_ERROR_MSG  := '';

    UPDATE CHECK_SESSION
    SET SEVERITY_CD = 'FATAL',
        SESSION_COMMENT = 'Check Session Failed: ' || COALESCE(p_V_ERROR_COMMENT, ''),
        SESSION_END_DATE = NOW(),
        UPDATED_STATUS_FLG = 'Y',
        LAST_UPDATED = NOW()
    WHERE CHK_SESSION_ID = p_V_CHK_SESSION_ID;

    RAISE INFO 'p_V_RESULT,%,p_V_ERROR_MSG,%',p_V_RESULT,p_V_ERROR_MSG;

EXCEPTION
    WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS
            errState = RETURNED_SQLSTATE,
            errMsg   = MESSAGE_TEXT,
            errContext = PG_EXCEPTION_CONTEXT;
        p_V_ERROR_MSG := 'SQL State: '|| errState || ';Message ' || errMsg || '; Context '|| errContext;
        p_V_RESULT := 'F';
END;
$$ LANGUAGE plpgsql;

Upvotes: 0

Views: 895

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246433

An OUT parameter in a function automatically defines a variable with the name of the parameter.

Your explicit declaration

DECLARE
    p_V_RESULT  char(1) := 'T';
    p_V_ERROR_MSG varchar(1000)  := NULL;

overrides and shadows those parameters. So you end up assigning to the wrong variables.

The solution is to remove these second variable declarations.

Upvotes: 3

Related Questions