Thread: Problem with createview

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?

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

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