Thread: 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