For example: I would like to set my CPU to higher end for when running a specific application, such as psx4droid. Is this possible with Tasker?
Or if not Tasker... any other app? SetCPU doesn't have the option.
It seems to me that you could get there by having tasker either directly set the system variables necessary to overclock or to access the commands that setCPU uses. To do that you can use the "Execute" plugin that executes commands like you would in the terminal emulator. A lot of this is over my head but you probably understand terminal commands and such better and may be able to make sense of my ramblings:
Theoretically, when you open psx4droid it should run that profile you created, changing the CPU settings.