On Thu, 2005-02-10 at 18:20 +0100, Christoph Pingel wrote: > >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 > FROM obj s, obj o, ool, rlt > WHERE s.obj_id = ool.subject > AND o.obj_id = ool.object > AND rlt.rlt_id = ool.relation > ORDER BY rlt_id > > Do I miss something obvious? > yep, because when you create a view you are creating a table with volatile contents so the DB needs to have an identifier for each column. Theconstraints are basically the same as column names in a table. In a select you doing just that selecting so the issue does not arise. > thanks, > Christoph > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
