Sample Header Ad - 728x90

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

2 votes
1 answer
3289 views
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.
Asked by Oleksii Cherkas (131 rep)
Feb 2, 2023, 03:38 PM
Last activity: Feb 3, 2023, 11:24 AM