Return multiple output variables from postgres function

Question

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;

select * from CheckSessionFailed ( 1::bigint, 'pano'::varchar(1000)); — Tom
– Tom, Commented Sep 8, 2021 at 8:36
When i removed the p_V_RESULT and p_V_ERROR_MSG from DECLARE section it worked — Tom
– Tom, Commented Sep 8, 2021 at 8:53

Laurenz Albe · Accepted Answer · 2021-09-08 09:21:22Z

3

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.

answered Sep 8, 2021 at 9:21

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Return multiple output variables from postgres function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related