Re: no self-joins in views?

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
>