Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f1CFrvx19438 for ; Mon, 12 Feb 2001 10:53:57 -0500 (EST) (envelope-from reedstrm@rice.edu) Received: by rice.edu via sendmail from stdin id (Debian Smail3.2.0.102) for pgsql-sql@postgresql.org; Mon, 12 Feb 2001 09:53:46 -0600 (CST) Date: Mon, 12 Feb 2001 09:53:46 -0600 From: "Ross J. Reedstrom" To: Giovanni Biscuolo Cc: pgsql-sql@postgresql.org Subject: Re: view does not show all records it should Message-ID: <20010212095346.B24537@rice.edu> Mail-Followup-To: Giovanni Biscuolo , pgsql-sql@postgresql.org References: <3A87F92F.49345962@libero.it> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii User-Agent: Mutt/1.0i In-Reply-To: <3A87F92F.49345962@libero.it>; from giovanni.biscuolo@libero.it on Mon, Feb 12, 2001 at 03:54:39PM +0100 X-Archive-Number: 200102/213 X-Sequence-Number: 1403 On Mon, Feb 12, 2001 at 03:54:39PM +0100, Giovanni Biscuolo wrote: > I set up a simple database in wich all works well, exept > one *very* strange (to me) result. > > Some time ago I created this views: > > CREATE VIEW mag_scaricati_view AS > SELECT s.id_carico, SUM(s.qta_scaricata) AS Scaricati > FROM mag_scarico_tbl s > GROUP BY s.id_carico; > > CREATE VIEW mag_giacenza1_view AS > SELECT c.ref AS "Ref.", > p.descrizione_breve AS Descrizione, > c.id_carico AS "Scheda di carico", > c.qta_caricata AS "Caricati", > s.Scaricati, > (c.qta_caricata-s.Scaricati) AS "Giacenza" > FROM mag_carico_tbl c, mag_scaricati_view s , prd_ref_tbl p > WHERE (c.id_carico = s.id_carico) AND > (c.ref = p.ref); > > Now, when I issue the following command: > > SELECT * FROM mag_giacenza1_view > > it shows me the records that was in the database the day > I created the view, _not all the records in the database_. > If I select "by hand" with the following statament (please > note it is the very same with which I created the view): > Are you absolutely sure you did "CREATE VIEW" and not "CREATE TABLE" ? PostgreSQL allows you to create a table by selecting from existing tables. The sytax _exactly_ matches the create view syntax, except for the single word TABLE vs. VIEW. You can test this from pgsql with: \d mag_giacenza1_view If it's a view, you'll see the column definitions, plus the parsed version of the SELECT you used to create it. If it's a table, you'll just see the column defines. Which version of PostgreSQL are you using, BTW? Ross