Hi, Andrus,
First, it does seem that you are expecting PostgreSQL to have the same
behavior as a flat-file manager such as FoxPro (indeed, it seems you'd
like PG to have the behavior of a *specific* flat-file manager).
Despite the superficial similarity in the command syntax, a modern
RDBMS is a very different animal from FoxPro, dBase, 4D and the like,
and needs to be approached on its own terms rather than expecting the
semantics of commands with the same keyword to be the same. While
that may seem to be an irritating and pointless transition, modern
RDBMSes are so much more powerful than flat-file managers that you'll
find the transition well worth your time.
On Dec 20, 2009, at 1:12 AM, Andrus wrote:
> I tried
>
> drop table if exists tt ;
> create temp table tt ( a int, b int );
> insert into tt values ( 1,2);
> insert into tt values ( 3,4);
> update tt set a=a*10, b=(select sum(a) from tt);
> select * from tt
>
> b has value 4 for every row.
>
> So we *dont* see new values done in the same transaction.
You seem to have a specific model for execution in mind, and that
model is not the one PostgreSQL (or any other standards-compliant SQL
database) will use. Within each UPDATE statement, the UPDATE is
operating on a snapshot of the database at the time the command begins
execution. That's what the SQL standard requires, as Tom Lane noted
earlier.
If you want to iterate through each row, applying changes, using PL/
pgSQL with cursors is probably the best solution:
http://www.postgresql.org/docs/8.4/interactive/plpgsql-cursors.html
If you can be a bit more detailed about what you are trying to
accomplish, we can help you more.
--
-- Christophe Pettus
xof@thebuild.com