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