How to execute a SQL string returned from a stored procedure in Postgresql?

Question

I have a simple stored procedure below which is returning a SQL string:

CREATE OR REPLACE PROCEDURE dbo.select_users_test(sqlstring inout text)
LANGUAGE plpgsql
AS
$$
BEGIN
        sqlstring = 'select * from dbo.user where usr_key in (1, 2);';
END;
$$;

I can call the stored procedure like this:

CALL dbo.select_users_test('')

and the result is :

sqlstring text
select * from dbo.user where usr_key in (1, 2);

What I am trying to achieve is to execute that SQL string returned from the stored procedure.

I have tried

EXECUTE QUERY CALL dbo.select_users_test('');

and

EXECUTE CALL dbo.select_users_test('');

but both throw an error:

Syntax error at or near "CALL"

I am explicitly trying to achieve this with stored procedures and not functions, is it possible to execute the returned SQL string?

Answer 1

Yes you can return a value from a procedure, you should not but you can. However you cannot call it as you proposed. You are attempting to call the procedure with a literal value (a 0 length string) and to receive a value in it. But a literal parameter is a constant, thus you cannot change the value. You are resisting converting to a function because It would be a very tedious task to convert to a function. However it will be an at least if not even more tedious task using a procedure. You will have to declare a variable to receive the result and then pass that variable. So in everywhere you want to use the procedure you need something like:

do $$
declare
   v_to_receive_sql text = '';

begin 
   raise notice 'v_to_receive_sql =>%',v_to_receive_sql;
   call select_users_test(v_to_receive_sql);
   raise notice 'v_to_receive_sql =>%',v_to_receive_sql;
end;
$$;

As already mentioned by @a_horse_with_no_name indicated first you need to read and heed Migrate your mind set too. Postgres and MS Sql Server are very different. Unless you make that mindset adjustment you are in for a very difficult time.