How to test a function that selects from real tables?
This is really difficult to answer. What works for others might not work for you and vice versa.
Personally i would set up a battery of repeatable test scripts. Start with a clean set of data, run a standard set of executions and then check the results are valid. Personally i would not put tests inside the code itself.
However, i would put validation checks inside your code so that it exits with sensible warnings, rather than corrupt data.
Im not a programmer, but i have learned that when a function or proc gets so complicated no-one understands it, it may be worth breaking it down into smaller more manageable functions.