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?

If you want to return something use a function, not a procedure — , Nov 19 '21 at 17:06
You can't see, [Return from procedure](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-PROCEDURE). You can [Execute](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN) the query just not return it unless you break it down into `OUT` variables, which I'm pretty sure is not what you want. You will need to use a function. — Adrian Klaver, Nov 19 '21 at 17:07
As I have already mentioned, I am trying to achieve this with stored procedures and not with functions, I have around 400 stored procedures which are migrated over from SQLSever. It would be a very tedious task to update all those SPs to functions. — Fakhar Ahmad Rasul, Nov 19 '21 at 17:13
I am able to return a SQL string from my stored procedure, I am wondering if its possible to execute the returned SQL string? — Fakhar Ahmad Rasul, Nov 19 '21 at 17:15
In Postgres you should use functions to return something. You have to edit each and every procedure anyway because the syntax is substantially different. Turning them into functions doesn't really add that much more work. [Migrate your mind set too](https://blog.sql-workbench.eu/post/migrate-your-mindset/) - — , Nov 19 '21 at 17:30
@a_horse_with_no_name got it, thank you for the response. I am still wondering if what I am trying to do is at all possible at all? — Fakhar Ahmad Rasul, Nov 19 '21 at 17:43
No it is not possible. If you want to `RETURN` a query you will need to use a function. Given the similarity of this question to one from yesterday(which has since been deleted) your colleague was told the same thing. Going from MSSQL --> Postgres or the other way is going to involve a lot of work, no way around it. — Adrian Klaver, Nov 19 '21 at 18:10
@AdrianKlaver withoud DO you need external script, which can keep result of function, then you can call it. With DO statement, and function all can be done with PG itself. — Michał Zaborowski, Nov 22 '21 at 11:57

score 0 · Answer 1 · answered Nov 19 '21 at 21:32

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.

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

1 Answers1