X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 99B4EB427D8 for ; Mon, 7 Jul 2003 11:59:21 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 41930-03 for ; Mon, 7 Jul 2003 08:59:04 -0300 (ADT) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by svr1.postgresql.org (Postfix) with ESMTP id 8EB03B42167 for ; Mon, 7 Jul 2003 08:59:03 -0300 (ADT) Received: from matrix.gatewaynet.com (localhost [127.0.0.1]) by matrix.gatewaynet.com (8.12.5/8.12.5) with ESMTP id h67H5jjm009998; Mon, 7 Jul 2003 15:05:45 -0200 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.5/8.12.5/Submit) with ESMTP id h67H5hLj009994; Mon, 7 Jul 2003 15:05:43 -0200 Date: Mon, 7 Jul 2003 15:05:43 -0200 (GMT+2) From: Achilleus Mantzios To: Gary Stainburn Cc: pgsql-sql@postgresql.org Subject: Re: create view error In-Reply-To: <200307071240.23936.gary.stainburn@ringways.co.uk> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/59 X-Sequence-Number: 14173 On Mon, 7 Jul 2003, Gary Stainburn wrote: > Hi folks, > > I know I'm missing something blindingly obvious, can someone point it out to > me please. > > create table locos ( -- Locos table - contains details of locos > lid int4 default nextval('loco_lid_seq'::text) unique not null, > lclass int4 references lclass(lcid), -- Loco Class > lbuilt date, -- Date off-shed > lcme int4 references cme(cmid), -- Chief Mechanical Engineer > lname character varying(30), -- Name of Loco > lcomments text -- free text comments > ); > NOTICE: CREATE TABLE / UNIQUE will create implicit index 'locos_lid_key' for > table 'locos' > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > CREATE > create table lnumbers ( -- alternate loco numbers > lnid int4 not null references locos(lid), > lnumber character varying(10), > lncurrent bool, > primary key (lnid, lnumber) > ); > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'lnumbers_pkey' > for table 'lnumbers' > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > CREATE > create view loco_dets as > select * from locos l > left outer join > (select * from lclass) lc on lc.lcid = l.lclass > left outer join > (select lnumber from lnumbers) ln on ln.lnid = l.lid and ln.lncurrent ^^^ select also lnid > = true > left outer join > (select * from company) c on c.coid = lc.lcompany; > ERROR: No such attribute or function ln.lnid > -- ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-210-8981112 fax: +30-210-8981877 email: achill at matrix dot gatewaynet dot com mantzios at softlab dot ece dot ntua dot gr