You are confusing a few things, and you don't want to hear the
explanations because they are inconvenient.
Andrus wrote:
> 1. In my case b expression needs values from previous rows updated in this
> same command before:
You are confusing "to the left of" and "before".
If you want behaviour that deviates from the SQL standard, you will
usually meet fierce resistance from PostgreSQL.
> I understood from replies that
>
> set transaction isolation level read uncommitted;
>
> in PostgreSql is broken: it sets silently committed isolation level.
You should read this:
http://www.postgresql.org/docs/8.4/static/transaction-iso.html
I agree that the behaviour may be surprising, but "broken" is polemic.
This is not always a good comparison when standard behaviour is
concerned, but Oracle does it the same way.
> 2. In my planned UPDATE statement instead of 4 there is an expression
> containing one big CASE WHEN expression with many WHEN .. THEN clauses.
> This command takes several hundreds of lines.
> Your solution requires repeating this expression two times and thus makes
> sql difficult to read.
... plus the expression would be evaluated twice. But you cannot hold that against
the person who gave you the advice, because you hid that fact.
Why don't you let your imagination play a little:
1) You could use a subquery like
UPDATE foo SET col = myex
FROM (SELECT foo_id, <your 100 lines here> AS myex FROM whatever ...) AS bar
WHERE foo.foo_id = bar.foo_id;
2) You could define a stable SQL function for your 100 line subquery which
should be evaluated only once in the UPDAT query.
Yours,
Laurenz Albe
