Works as SELECT but not as a VIEW
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
Attachments:
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
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:
[ snip embarrassing mistake ]
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.
So the SELECT can manage this bot not the VIEW? Thanks for catching my mistake.
Cheers,
Rob
--
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