Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In Postgres in a user defined function I do

execute format('delete from table where session_id = %L', id);

ran without execute postgres (may) return(s) the following:

DELETE 0

Is there any way I could get this output in the user defined function? What I am looking is something like:

execute format('delete from table where session_id = %L', id) into log;
share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

You can use the GET DIAGNOSTICS command for this purpose. A quick example:

DO $$
DECLARE i integer; 
BEGIN
    DELETE FROM event1;
    GET DIAGNOSTICS i = ROW_COUNT;
    RAISE NOTICE 'DELETE %', i;
END;
$$;

For DELETE statements, the only useful variable is ROW_COUNT, for INSERTs there is also RESULT_OID.

Set the error level after the RAISE to your choice and set the logging configuration accordingly.

share|improve this answer
    
The point is that I also use user defined functions, that generate 'raise notice' output. Would be cool to catch that too.. –  arthur Sep 18 '13 at 16:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.