Re: [BUGS] Works as SELECT but not as a VIEW

#1

Robert Creager <Robert_Creager (AT) LogicalChaos (DOT) org> writes:

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#2

--Signature_Sun__26_Jun_2005_13_32_48_-0600_Ku3nq68QCS6X22G5
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

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

Quote:

Robert Creager <Robert_Creager (AT) LogicalChaos (DOT) 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

--Signature_Sun__26_Jun_2005_13_32_48_-0600_Ku3nq68QCS6X22G5
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iEYEARECAAYFAkK/AuAACgkQLQ/DKuwDYzl0egCfVGPFQZtYRCCsL5u05OR3ok+m
cdUAoIGjvd7x2fgI7p/LAZe55DaX7v64
=oM+1
-----END PGP SIGNATURE-----

--Signature_Sun__26_Jun_2005_13_32_48_-0600_Ku3nq68QCS6X22G5--

#3

Robert Creager <Robert_Creager (AT) LogicalChaos (DOT) org> writes:

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] Works as SELECT but not as a VIEW

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] Works as SELECT but not as a VIEW - 06-26-2005 , 02:20 PM

Re: [BUGS] Works as SELECT but not as a VIEW - 06-26-2005 , 02:34 PM

Re: [BUGS] Works as SELECT but not as a VIEW - 06-26-2005 , 02:45 PM