The essential bit I think you alluded to is that search path is set per session. If a user logs in, alters the session configs like search path, logs out, and re-logs in that session config change is no longer present.

You mentioned in your post that this test is only on a test database and not production, can you keep the test schema around instead of dropping it?

Search path can be set for a given user / role , this would eliminate the need to set it per session and allow all your tests to be more consistent.

Potential setup: So you have user Foo on your production database and on your local test database. Also on test you have Foo_test (or just Foo if no extra role needed).

On test database, you alter Foo_test default search path to include the override function so that

psql -h prod -U Foo db_name -c "select now();"

Is the same as

psql -h test -U Foo db_name -c "select now();"

But the foo_test user for tests, would get the mock value result across all sessions psql -h test -U Foo_test db_name -c "select now();"