Re: view does not show all records it should

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