If you have references from one table to another by way of a foreign key, or if you related these things with a join table in the case of many-to-many relationships, etc. you should be able to get this with a single query using JOIN or simply joining tables with WHERE. For example:
Code:
SELECT user.id,user.name,character.id,character.name
FROM user,character
WHERE user.id = 3
AND character.user_id=user.id
ORDER BY character.name;
or
Code:
SELECT user.id,user.name,character.id,character.name
FROM user
JOIN character ON (user.id = character.user_id)
WHERE user.id = 3
ORDER BY character.name;
This is the point of foreign keys, normalization, etc. If you do something very frequently you can also build views that pull together data from a number of tables so you don't have to explicitly join each time you need to get the data.
Do abide what others have said. You don't design a database at the psql prompt, you design it on paper or in a tool made for database design. Read the things linked, and start completely from scratch. This will mean throwing away your current schema and code that accesses it, but that isn't a bad thing. Once you have a design, and know your relationships (one-to-one, one-to-many, many-to-many), what goes where, etc. actually writing the DDL to generate your tables should be the easy part.
Also note that in the queries above i took out the redundant user_ and character_ prefixes on fields. When you read something aloud, it makes things a little clearer. You wouldn't say "I need my character's character name", you'd just say "I need my character's name". Save the prefixing of table names for your foreign keys.
-Lee
