Re: no self-joins in views?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You missed something obvious. Executing this query in psql you have no
problem, because there is no structure created from the result. A view
behaves like a table - try creating a table like
create table blah (
id int,
id int
);

and you'll hit the same error. You have to have different names for the
columns. BTW: that's not a postgres problem, ANY relational database will
give the same error here.

On Thursday 10 February 2005 09:20 am, 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?
>
> thanks,
> Christoph
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

- --
UC

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFCC5zdjqGXBvRToM4RAk6bAJ0feXiYMKN0uYHv9qM2S8tH3mAVOwCaAjWv
VwPo11ag0tGoOzeclxxFkxI=
=xvU0
-----END PGP SIGNATURE-----