Read the following:
And, read Section 9.2 from the documentation: http://www.postgresql.org/docs/7.3/static/xfunc.html
Essentially, SETOF means you are returning anything that can fit into the definition of a "set". So, you can put a table name in there, or a view name, or you can refer to a custom composite type that you have created with the CREATE TYPE
CREATE TYPE is a little confusing in PostgreSQL, because it does double-duty:
1. creating custom base(scalar) types, where you define the functions for raw input, storage, internal and external represention
2. creating composite or "dummy" types which are really just like table definitions-- simply a list of attributes associated with existing types. The two are very different. The first kind of type can be used as custom datatypes for actual data stored in tables, while the second kind (composite) are only for use in set-returning functions.
So, if you are returning a set based on your multi-table example query you posted, you would just create a composite type that would encompass all the attributes being returned from this query.
Of course, if you are seriously suggesting writing a procedure to do the query above, you would be much better off just creating a view. Or, create a view which handles the basic join, and then use a function to return SETOF [view_name] for further manipulation.