Skip site navigation (1) Skip section navigation (2)

Re: [SQL] Select & Tables X Select & Views

From: Rodrigo Rezende <Rodrigo(dot)Rezende(at)hortolandia(dot)marelli(dot)it>
To:
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Select & Tables X Select & Views
Date: 2000-02-21 12:42:59
Message-ID: 38B132D3.5CEB990A@hortolandia.marelli.it (view raw or flat)
Thread:
Lists: pgsql-sql
I'm sorry,

the real case is :

1.) create the table func_preench

create table func_preench (
   codfuncionario int4,
   codest_preench int4,
   data date
);

2.) I put the data :

codfuncionario codest_preench data
-------------- -------------- ----------
108            2              1999-08-01
109            2              1999-08-01
110            2              1999-08-01
111            2              1999-08-01
112            2              1999-08-01
113            2              1999-08-01
114            2              1999-08-01
115            1              1999-10-01
116            2              1999-08-01
117            2              1999-08-01
118            2              1999-08-01
119            2              1999-08-01
120            2              1999-08-01
121            2              1999-08-01
122            1              1999-08-01
123            1              1999-08-01
124            1              1999-08-01
125            1              1999-08-01
126            1              1999-08-01
127            1              1999-08-01
128            1              1999-08-01
128            0              1999-10-01
129            1              1999-08-01
130            1              1999-08-01
131            1              1999-08-01
132            1              1999-08-01
133            1              1999-08-01
134            1              1999-08-01
135            1              1999-08-01
136            1              1999-08-01
137            1              1999-08-01
138            1              1999-08-01
139            1              1999-08-01
140            1              1999-08-01
141            1              1999-08-01
142            1              1999-08-01
143            1              1999-08-01
144            1              1999-08-01
145            1              1999-08-01
146            3              1999-10-01
147            1              1999-08-01
148            1              1999-08-01
149            2              1999-08-01
150            1              1999-08-01
151            2              1999-08-01
152            2              1999-08-01
153            1              1999-08-01
154            1              1999-08-01
155            1              1999-08-01
156            1              1999-08-01
157            1              1999-08-01
158            1              1999-08-01
159            1              1999-08-01
160            1              1999-08-01
161            1              1999-08-01
162            1              1999-08-01
163            1              1999-08-01
164            1              1999-08-01
165            1              1999-08-01
166            1              1999-08-01
167            1              1999-08-01
168            1              1999-08-01
169            1              1999-08-01
170            1              1999-08-01
171            1              1999-08-01
172            1              1999-08-01
173            1              1999-08-01
174            1              1999-08-01
175            1              1999-08-01
176            1              1999-08-01
177            1              1999-08-01
178            1              1999-08-01
183            1              1999-08-01
184            1              1999-08-01
184            0              1999-09-01
185            1              1999-08-01
186            1              1999-08-01
187            1              1999-08-01
188            1              1999-08-01
189            1              1999-08-01
190            1              1999-08-01
191            1              1999-08-01
191            0              1999-12-01
192            1              1999-08-01
193            1              1999-08-01
194            2              1999-08-01
195            1              1999-10-01
198            0              1999-07-01
199            1              2000-01-01
202            1              1999-12-01
203            1              2000-01-01
166            0              2000-01-01
(92 rows)

The view :

create view func_aux_stat as select fp.codfuncionario, max(fp.data) as
data
from func_preench as fp group by fp.codfuncionario ;

running : select * from func_aux_stat; the result have 88 rows affected

The problem :

running:

select * from func_preench, func_aux_stat ;

codfuncionario codest_preench data       codfuncionario data
-------------- -------------- ---------- -------------- ----------
108            2              1999-08-01 108            1999-08-01
108            2              1999-08-01 109            1999-08-01
108            2              1999-08-01 110            1999-08-01
108            2              1999-08-01 111            1999-08-01
108            2              1999-08-01 112            1999-08-01
108            2              1999-08-01 113            1999-08-01
108            2              1999-08-01 114            1999-08-01
108            2              1999-08-01 115            1999-10-01
108            2              1999-08-01 116            1999-08-01
108            2              1999-08-01 117            1999-08-01
108            2              1999-08-01 118            1999-08-01
108            2              1999-08-01 119            1999-08-01
108            2              1999-08-01 120            1999-08-01
108            2              1999-08-01 121            1999-08-01
108            2              1999-08-01 122            1999-08-01
108            2              1999-08-01 123            1999-08-01
108            2              1999-08-01 124            1999-08-01
108            2              1999-08-01 125            1999-08-01
108            2              1999-08-01 126            1999-08-01
108            2              1999-08-01 127            1999-08-01
108            2              1999-08-01 128            1999-10-01
108            2              1999-08-01 129            1999-08-01
108            2              1999-08-01 130            1999-08-01
108            2              1999-08-01 131            1999-08-01
108            2              1999-08-01 132            1999-08-01
108            2              1999-08-01 133            1999-08-01
108            2              1999-08-01 134            1999-08-01
108            2              1999-08-01 135            1999-08-01
108            2              1999-08-01 136            1999-08-01
108            2              1999-08-01 137            1999-08-01
108            2              1999-08-01 138            1999-08-01
108            2              1999-08-01 139            1999-08-01
108            2              1999-08-01 140            1999-08-01
108            2              1999-08-01 141            1999-08-01
108            2              1999-08-01 142            1999-08-01
108            2              1999-08-01 143            1999-08-01
108            2              1999-08-01 144            1999-08-01
108            2              1999-08-01 145            1999-08-01
108            2              1999-08-01 146            1999-10-01
108            2              1999-08-01 147            1999-08-01
108            2              1999-08-01 148            1999-08-01
108            2              1999-08-01 149            1999-08-01
108            2              1999-08-01 150            1999-08-01
108            2              1999-08-01 151            1999-08-01
108            2              1999-08-01 152            1999-08-01
108            2              1999-08-01 153            1999-08-01
108            2              1999-08-01 154            1999-08-01
108            2              1999-08-01 155            1999-08-01
108            2              1999-08-01 156            1999-08-01
108            2              1999-08-01 157            1999-08-01
108            2              1999-08-01 158            1999-08-01
108            2              1999-08-01 159            1999-08-01
108            2              1999-08-01 160            1999-08-01
108            2              1999-08-01 161            1999-08-01
108            2              1999-08-01 162            1999-08-01
108            2              1999-08-01 163            1999-08-01
108            2              1999-08-01 164            1999-08-01
108            2              1999-08-01 165            1999-08-01
108            2              1999-08-01 166            2000-01-01
108            2              1999-08-01 167            1999-08-01
108            2              1999-08-01 168            1999-08-01
108            2              1999-08-01 169            1999-08-01
108            2              1999-08-01 170            1999-08-01
108            2              1999-08-01 171            1999-08-01
108            2              1999-08-01 172            1999-08-01
108            2              1999-08-01 173            1999-08-01
108            2              1999-08-01 174            1999-08-01
108            2              1999-08-01 175            1999-08-01
108            2              1999-08-01 176            1999-08-01
108            2              1999-08-01 177            1999-08-01
108            2              1999-08-01 178            1999-08-01
108            2              1999-08-01 183            1999-08-01
108            2              1999-08-01 184            1999-09-01
108            2              1999-08-01 185            1999-08-01
108            2              1999-08-01 186            1999-08-01
108            2              1999-08-01 187            1999-08-01
108            2              1999-08-01 188            1999-08-01
108            2              1999-08-01 189            1999-08-01
108            2              1999-08-01 190            1999-08-01
108            2              1999-08-01 191            1999-12-01
108            2              1999-08-01 192            1999-08-01
108            2              1999-08-01 193            1999-08-01
108            2              1999-08-01 194            1999-08-01
108            2              1999-08-01 195            1999-10-01
108            2              1999-08-01 198            1999-07-01
108            2              1999-08-01 199            2000-01-01
108            2              1999-08-01 202            1999-12-01
108            2              1999-08-01 203            2000-01-01

88 Row(s) affected


Why this operation presented 88 rows if the correct is 8096 ?

Thanks a lot,

Rodrigo C. Rezende

reedstrm(at)wallace(dot)ece(dot)rice(dot)edu wrote:

> Rodrigo -
> Are you reporting a bug, or looking for advice? Here's how it works
> for me:
>
> I ran this script:
>
> create table taba (a1 int);
> create table tabb (b1 char);
> insert into taba (1);
> insert into taba values (1);
> insert into taba values (2);
> insert into taba values (3);
> insert into tabb values ('a');
> insert into tabb values ('b');
> create view viewb as select * from tabb;
>
> And here are the tests:
> test=> select version();
> version
> --------------------------------------------------------------
> PostgreSQL 6.5.0 on i686-pc-linux-gnu, compiled by gcc 2.7.2.3
> (1 row)
>
> test=> select * from taba, tabb;
> a1|b1
> --+--
>  1|a
>  2|a
>  3|a
>  1|b
>  2|b
>  3|b
> (6 rows)
>
> test=> select * from taba, viewb;
> a1|b1
> --+--
>  1|a
>  2|a
>  3|a
>  1|b
>  2|b
>  3|b
> (6 rows)
>
> test=>
>
> So, it looks like your view definition is broken, or your using an
> old version of pgsql.
>
> Ross
> --
> Ross J. Reedstrom, Ph.D., <reedstrm(at)rice(dot)edu>
> NSBRI Research Scientist/Programmer
> Computer and Information Technology Institute
> Rice University, 6100 S. Main St.,  Houston, TX 77005


In response to

Responses

pgsql-sql by date

Next:From: Rodrigo RezendeDate: 2000-02-21 15:38:16
Subject: rule plan string too big
Previous:From: M.MazurekDate: 2000-02-21 02:07:33
Subject: Re: [SQL] group by date_part

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group