pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedview does not show all records it should
3+ messages / 3 participants
[nested] [flat]
* view does not show all records it should
@ 2001-02-12 14:54 Giovanni Biscuolo <giovanni.biscuolo@libero.it>
2001-02-12 15:53 ` Re: view does not show all records it should Ross J. Reedstrom <reedstrm@rice.edu>
2001-02-12 16:01 ` Re: view does not show all records it should Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: Giovanni Biscuolo @ 2001-02-12 14:54 UTC (permalink / raw)
To: pgsql-sql
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):
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);
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: view does not show all records it should
2001-02-12 14:54 view does not show all records it should Giovanni Biscuolo <giovanni.biscuolo@libero.it>
@ 2001-02-12 15:53 ` Ross J. Reedstrom <reedstrm@rice.edu>
1 sibling, 0 replies; 3+ messages in thread
From: Ross J. Reedstrom @ 2001-02-12 15:53 UTC (permalink / raw)
To: Giovanni Biscuolo <giovanni.biscuolo@libero.it>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: view does not show all records it should
2001-02-12 14:54 view does not show all records it should Giovanni Biscuolo <giovanni.biscuolo@libero.it>
@ 2001-02-12 16:01 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2001-02-12 16:01 UTC (permalink / raw)
To: Giovanni Biscuolo <giovanni.biscuolo@libero.it>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-02-12 16:01 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-12 14:54 view does not show all records it should Giovanni Biscuolo <giovanni.biscuolo@libero.it>
2001-02-12 15:53 ` Ross J. Reedstrom <reedstrm@rice.edu>
2001-02-12 16:01 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox