Обсуждение: view does not show all records it should

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 ASSELECT s.id_carico, SUM(s.qta_scaricata) AS ScaricatiFROM   mag_scarico_tbl sGROUP BY
s.id_carico; 
 
CREATE VIEW mag_giacenza1_view ASSELECT c.ref AS "Ref.",       p.descrizione_breve AS Descrizione,       c.id_carico AS
"Schedadi 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 pWHERE  (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):

SELECT c.ref AS "Ref.",      p.descrizione_breve AS Descrizione,      c.id_carico AS "Scheda di carico",
c.qta_caricataAS "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);
it shows me all the records.

Please, is there anybody who can help me, I can't even imagine what's
happening. :-(((

Ciao.
-- 
____________________________________
Giovanni Biscuolo
mailto:giovanni.biscuolo@libero.it

Giovanni Biscuolo <giovanni.biscuolo@libero.it> writes:
> 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; 

Grouped views don't work very well in versions before 7.1, though I'm
not sure if that is the issue here or not.  (What PG version are you
using, anyway?)

> 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):
> it shows me all the records.

That's fairly hard to believe, unless you ran the query using the view
inside a transaction that had been open the whole time.  I suspect that
you have misinterpreted the behavior, and that the real problem is not
related to when the records were inserted.  Can you show us exactly what
you're getting from each query?
        regards, tom lane

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