Problem with createview

Diogo Biazus <diogo@ikono.com.br> writes:

I trying to create a view with the following SQL:
select
*
from
((agendasbusca
inner join usuarios on usuarios.codusuario = agendasbusca.codusuario)
inner join gruposusuario on gruposusuario.codgrupousuario =
usuarios.codgrupousuario)
left join buscas on agendasbusca.codagendabusca = buscas.codagendabusca

And I'm getting this error:

ERROR: CREATE TABLE: attribute "codagendabusca" duplicated

But I don't want to specify each field in the query, because I would
have to re-create the view on every change made on the source tables.
Is there any way to create the view in this way?

If codagendabusca is the only duplicate-named field in the tables,
you could change
left join buscas on agendasbusca.codagendabusca = buscas.codagendabusca
to
natural left join buscas using (codagendabusca)

"NATURAL" produces an automatic JOIN ON clause that equates each
similarly-named pair of fields, and it also removes one of each such
pair of columns from the result.

Of course, you have to be careful not to create unintended column
name matches, so I'm not sure this is really a whole lot better
from a maintenance point of view.

regards, tom lane

Tom Lane wrote:

If codagendabusca is the only duplicate-named field in the tables,
you could change
left join buscas on agendasbusca.codagendabusca = buscas.codagendabusca
to
natural left join buscas using (codagendabusca)

That's another problem, there other fields, and some of then aren't the
same thing

"NATURAL" produces an automatic JOIN ON clause that equates each
similarly-named pair of fields, and it also removes one of each such
pair of columns from the result.

Good to know this kind of thing.

Of course, you have to be careful not to create unintended column
name matches, so I'm not sure this is really a whole lot better
from a maintenance point of view.

You're right.
I think it's better to define which fields I'm gonna use.
Thanks for the help.

--
Diogo de Oliveira Biazus
diogo@ikono.com.br
Ikono Sistemas e Automa��o
http://www.ikono.com.br