Thread: no self-joins in views?

no self-joins in views?

From
Christoph Pingel
Date:
Hello,

is there any restriction re the use of self joins in views? I have a
moderately complicated query where I scan the same table twice using
aliases, and I would like to put that into a view to keep my python
code clean. However, I get an error 'column xy duplicated'. Is this
so by design?

best,
Christoph

Re: no self-joins in views?

From
Richard Huxton
Date:
Christoph Pingel wrote:
> Hello,
>
> is there any restriction re the use of self joins in views? I have a
> moderately complicated query where I scan the same table twice using
> aliases, and I would like to put that into a view to keep my python code
> clean. However, I get an error 'column xy duplicated'. Is this so by
> design?

Could you give an actual example?

--
   Richard Huxton
   Archonet Ltd

Re: no self-joins in views?

From
Martijn van Oosterhout
Date:
On Thu, Feb 10, 2005 at 05:13:51PM +0100, Christoph Pingel wrote:
> Hello,
>
> is there any restriction re the use of self joins in views? I have a
> moderately complicated query where I scan the same table twice using
> aliases, and I would like to put that into a view to keep my python
> code clean. However, I get an error 'column xy duplicated'. Is this
> so by design?

It's probably complaining that the *resultset* has two columns with the
same name. Consider:

CREATE VIEW test AS
  SELECT * FROM table, table
  WHERE table.a = table.b;

Basically, the output of the view will have all the columnnames listed
twice. You'll need to rename them to make it work...

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Attachment

Re: no self-joins in views?

From
Michael Fuhr
Date:
On Thu, Feb 10, 2005 at 05:13:51PM +0100, Christoph Pingel wrote:
>
> is there any restriction re the use of self joins in views? I have a
> moderately complicated query where I scan the same table twice using
> aliases, and I would like to put that into a view to keep my python
> code clean. However, I get an error 'column xy duplicated'. Is this
> so by design?

Use column aliases so the xy columns don't have the same name:

CREATE VIEW viewname AS
SELECT a.xy AS axy,
       b.xy AS bxy
FROM foo AS a,
     foo AS b
WHERE ...

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: no self-joins in views?

From
Christoph Pingel
Date:
>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

Re: no self-joins in views?

From
"Uwe C. Schroeder"
Date:
-----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-----


Re: no self-joins in views?

From
mike
Date:
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
>

Re: no self-joins in views?

From
Richard Huxton
Date:
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

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

Re: no self-joins in views?

From
Christoph Pingel
Date:
>BTW: that's not a postgres problem, ANY relational database will
>give the same error here.

I didn't suspect it to be a postgres problem. I only didn't have the
opportunity to make this mistake working with MySQL. :-)


best,
Christoph

Re: no self-joins in views?

From
Christoph Pingel
Date:
>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, ...

That would have been elegant, but returns a 'syntax error at or near
"s_obj_id"'. Hm...

Thanks,
Christoph

Re: no self-joins in views? - works now!

From
Christoph Pingel
Date:
>>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, ...
>
>That would have been elegant, but returns a 'syntax error at or near
>"s_obj_id"'. Hm...

Was just some minor syntax error. Works now - this is exactly what I wanted.

Thanks!

regards,
Christoph