CODEWITHSUNDEEP
sqlpostgresqlstored-procedures
Jean Paul CP
Jean Paul CP

Reputation: 253

Return query from stored procedure

someone has created a stored procedure complicated and now I need to see the query that is generated by introducing some parameters. I do not want the results, I just want to see something like 

SELECT a, b, x FROM table WHERE OR ib ia = 1 = 2 some i ON LEFT JOIN xxx = yyy

The sp was developered on Postgresql =/

The last line says:

OPEN $1 FOR EXECUTE(lcQuery);
RETURN $1;

Can I do...?

RETURN lcQuery;

Upvotes: 0

Views: 465

Answers (1)

klin
klin

Reputation: 121834

You cannot do this because type of lcQuery (i.e. text or varchar) differs from the declared return type of the function. Use raise notice instead, execute the function in psql and you will see generated notice:

...
raise notice '%', lcQuery;
...

Another option is to use a temporary table with a text column, insert value of lcQuery into the table inside the function and select the table after the function was executed. You can also use a custom configuration parameter in the analogous way.

Upvotes: 2

Related Questions