Could you give an actual example?
Sure. The idea is that 'objects' (persons, books, places) from a table obj are linked with each other in a link table ool where objects from obj can appear in an 'subject' or a 'object' column. Since the relation can be any, this is a very flexible and lean design for an 'ontology-like' collection of facts.
As I said, the CREATE VIEW returns an error (column obj_id duplicated), while the select statement by itself works (with an additional constraint on s.obj_id, otherwise the db would throw up hundreds of thousands of rows).
CREATE VIEW relations_aspect_subject AS
SELECT s.obj_id, s.canonical_name, rlt.dscr, rlt.rlt_id, o.obj_id, o.canonical_name
You've got two columns that the system wants to call "obj_id" here. Try something like
SELECT s.obj_id AS s_obj_id, ...
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
