PostgreSQL: How to use INOUT parameter of a Stored Procedure called in dynamic SQL?

Question

I have a stored procedure with an INOUT parameter, which value is modified inside the SP. I need to call that SP, providing a variable as that parameter, and then use the modified variable. But the tricky part is that I need to call the SP using dynamic SQL. When I do that, the variable's value is not changed:

CREATE OR REPLACE PROCEDURE pr_test(
    INOUT p_rows INTEGER DEFAULT NULL 
)
AS
$body$
BEGIN
    RAISE NOTICE 'Inside SP, p_rows before modify = %', p_rows;
    p_rows := 100;
    RAISE NOTICE 'Inside SP, p_rows after modify = %', p_rows;
    RETURN;
END
$body$
LANGUAGE 'plpgsql'
;

Trying to use it...

DO $$ 
DECLARE
    v       INTEGER = 7;
    v_sql   TEXT;
BEGIN
    RAISE NOTICE 'v before SP call = %', v;
    v_sql := 'CALL pr_test(p_rows => $1);';
    EXECUTE v_sql USING v;
    RAISE NOTICE 'v after SP call = %', v;
END;$$;

And the output is:

v before SP call = 7 
Inside SP, p_rows before modify = 7 
Inside SP, p_rows after modify = 100 
v after SP call = 7

Please advise on how to do it right.

Answer 1

I managed to find a somewhat acceptable solution for this. It has its flaws, but it works anyway.

First we create our Stored Procedure with the INOUT parameter:

CREATE OR REPLACE PROCEDURE pr_test(
    INOUT p_rows INTEGER DEFAULT NULL 
)
AS
$body$
BEGIN
    RAISE NOTICE 'Inside SP, p_rows before modify = %', p_rows;
    p_rows := p_rows * 2;
    RAISE NOTICE 'Inside SP, p_rows after modify = %', p_rows;
    RETURN;
END
$body$
LANGUAGE 'plpgsql'
;

And now to how we can use the INOUT parameter with dynamic SQL. From our function/sp/anonymous block, we call dynamic SQL. What's inside it? In that dynamic SQL we create a temporary function, which itself calls our SP, passing our parameter variable to it and returning that (now modified by the SP) variable as function's return value. After we have created the function, we call it (still inside our dynamic SQL) using simple SELECT query. This way, our dynamic SQL block returns a 'row column', which we read in our outer block, assigning the value to our initial variable.

DO $$ 
DECLARE
    v       INTEGER = 7;
    v_sql   TEXT;
BEGIN
    RAISE NOTICE 'v before SP call = %', v;
    v_sql := 'CREATE OR REPLACE FUNCTION pg_temp.pr_test_result(par INTEGER) 
              RETURNS INTEGER 
              AS $func$
              DECLARE
                  w INTEGER = par;
              BEGIN
                  CALL pr_test(p_rows => w);
                  RETURN w;
              END;
              $func$ language ''plpgsql'';
              SELECT pg_temp.pr_test_result($1);
             ';             
    EXECUTE v_sql INTO v USING v;
    RAISE NOTICE 'v after SP call = %', v;
    DROP FUNCTION IF EXISTS pg_temp.pr_test_result(INTEGER);
END;$$;

And we have the desired result:

v before SP call = 7
Inside SP, p_rows before modify = 7
Inside SP, p_rows after modify = 14
v after SP call = 14