no self-joins in views?

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

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/

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/

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

-----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-----

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.

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

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

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

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