Postgres Pro
Downloads
Home   >   mailing lists

Thread: Works as SELECT but not as a VIEW

Works as SELECT but not as a VIEW

From
Robert Creager
Date:
This doesn't seem right:

CREATE VIEW inci AS
SELECT date, inci_id, protocol, a.ip_addr, b.ip_addr, spt, dpt
FROM      (SELECT addr_id, ip_addr FROM addrs) AS a
     JOIN incident ON incident.source_addr_id = a.addr_id
     JOIN (SELECT addr_id, ip_addr FROM addrs)
           AS b ON incident.destination_addr_id = b.addr_id
     JOIN protocols USING( protocol_id );

Produces: ERROR:  column "ip_addr" duplicated

Yet the select on it's own works just fine...  I've attached the database
definition.

                                                 version

-------------------------------------------------------------------------------
--------------------------
 PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2
(Mandrake Linux 10.0 3.3.2-6mdk)


Thanks,
Rob

--
 12:31:42 up 24 days, 14:10,  6 users,  load average: 3.08, 2.63, 2.63
Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004
Attachment

Re: Works as SELECT but not as a VIEW

From
Tom Lane
Date:
Robert Creager <Robert_Creager@LogicalChaos.org> writes:
> This doesn't seem right:

> CREATE VIEW inci AS
> SELECT date, inci_id, protocol, a.ip_addr, b.ip_addr, spt, dpt
> FROM      (SELECT addr_id, ip_addr FROM addrs) AS a
>      JOIN incident ON incident.source_addr_id =3D a.addr_id=20
>      JOIN (SELECT addr_id, ip_addr FROM addrs)=20
>            AS b ON incident.destination_addr_id =3D b.addr_id
>      JOIN protocols USING( protocol_id );

> Produces: ERROR:  column "ip_addr" duplicated

Why doesn't it seem right?  You're asking it to generate a view with
two columns both named ip_addr.  Use "AS" to rename one or both.

            regards, tom lane

Re: Works as SELECT but not as a VIEW

From
Robert Creager
Date:
When grilled further on (Sun, 26 Jun 2005 15:18:47 -0400),
Tom Lane <tgl@sss.pgh.pa.us> confessed:

> Robert Creager <Robert_Creager@LogicalChaos.org> writes:
> > This doesn't seem right:
>=20
[ snip embarrassing mistake ]
>=20
> Why doesn't it seem right?  You're asking it to generate a view with
> two columns both named ip_addr.  Use "AS" to rename one or both.
>=20

So the SELECT can manage this bot not the VIEW?  Thanks for catching my mis=
take.

Cheers,
Rob

--=20
 13:29:26 up 24 days, 15:07,  6 users,  load average: 1.90, 2.31, 2.78
Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004

Re: Works as SELECT but not as a VIEW

From
Tom Lane
Date:
Robert Creager <Robert_Creager@LogicalChaos.org> writes:
> So the SELECT can manage this bot not the VIEW?

The spec constrains tables (and views) not to have duplicate column
names, but it does not say that about SELECT output lists.

            regards, tom lane