The PostgreSQL documentation states:
Any function with side-effects must be labeled VOLATILE...
Consider the following function:
CREATE OR REPLACE FUNCTION count_items() RETURNS integer AS $BODY$ DECLARE v_result INTEGER DEFAULT 0; BEGIN SELECT count( t.id ) INTO v_result FROM some_table t; RETURN v_result; EXCEPTION WHEN OTHERS THEN PERFORM error_log_insert( SQLSTATE, SQLERRM, current_query() ); RETURN 0; END; $BODY$ LANGUAGE plpgsql STABLE COST 10;
error_log_insert alters the database (performs an insert upon an exception), does this mean that the
count_items function has a side-effect (albeit indirect), and thus cannot be declared
STABLE, but must be
In other words, does the stability or volatility of a function also depend on the functions it calls within its exception block?
If that is the case, then how would you create
STABLE functions in PostgreSQL that log all exceptions to a database table?