Message-ID: <3F28E19A.1D589548@rodos.fzk.de>
Date: Thu, 31 Jul 2003 11:30:03 +0200
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: postgres@jal.org, pgsql-sql@postgresql.org
Subject: Re: Fwd: Bad Join moment - how is this happening?
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

> I have a view:
>
> create or replace view addenda as
> select
>         documents.id,
>         documents.oid,
>         documents.projects_id,
>         documents.doc_num,
>         documents.description,
>         documents.date,
>         documents.createdate,
>         documents.moddate,
>         documents.people_id,
>         documents.parent,
>         documents.document_type,
>         documents.state,
>         documents.machines_id,
>         documents.phases_id,
>
>         d_addenda.item_num,
>         d_addenda.drawing_reference
>
> from
>         d_addenda as a, documents as d
>                 where a.documents_id =  d.id;
>
Doing this you should have got
NOTICE:  Adding missing FROM-clause entry for table "d_addenda"
NOTICE:  Adding missing FROM-clause entry for table "documents"
So it's pretty useful to write psql's output to a file when creating
relations
and check for NOTICE messages.

exec 3>/tmp/psql.out;$PGSQLD/bin/psql <your options here>  1>&3 2>&3

NOTICE messages appear to be sent to stderr.

As the previous posters already made clear
create or replace view addenda as
select
        documents.id,
        documents.oid,
        documents.projects_id,
        documents.doc_num,
        documents.description,
        documents.date,
        documents.createdate,
        documents.moddate,
        documents.people_id,
        documents.parent,
        documents.document_type,
        documents.state,
        documents.machines_id,
        documents.phases_id,

        d_addenda.item_num,
        d_addenda.drawing_reference

from
        d_addenda , documents
                where d_addenda.documents_id =  documents.id;
resp.
create or replace view addenda as
select
        d.id,
        d.oid,
        d.projects_id,
        d.doc_num,
        d.description,
        d.date,
        d.createdate,
        d.moddate,
        d.people_id,
        d.parent,
        d.document_type,
        d.state,
        d.machines_id,
        d.phases_id,

        a.item_num,
        a.drawing_reference

from
        d_addenda as a, documents as d
                where a.documents_id =  d.id;
should match your intentions.

Regards, Christoph