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