CODEWITHSUNDEEP
sqlpostgresqldynamic-sql
user2843129
user2843129

Reputation: 21

PostgreSQL-dynamic sql inside function?

I have a PostgreSQL function that takes one string as parameter/this is dynamic sql query/ and executes that dynamic SQL and I expect the result from the dynamic query.

It seems that in PostgreSQL I should predefine what I will return - but this is impossible since I am executing dynamic statement and sometimes I will return one int column, sometimes I will return 5 varchar columns....

Another thing is that existing jdbc code will call the function-and I cannot change it-I can't define the types dynamically like:

{call execute_dynamic(?) as (a varchar(255),b int)};

The code that will call the procedure is: 

{call execute_dynamic(?)}

and cannot be changed....

Is there a way to implement this?

Upvotes: 1

Views: 238

Answers (1)

user2843129
user2843129

Reputation: 21

The solution is to use refcursor as return type. 

OPEN ref_cursor FOR EXECUTE dynamic_sql;
 return ref_cursor;

Upvotes: 1

Related Questions