Hi,
Am Mittwoch, 13. Juli 2005 00:03 schrieb Tom Lane:
> Janning Vygen <vygen@gmx.de> writes:
> > I have a guess, what happens here: The order of the subselect statement
> > is dropped by the optimizer because the optimizer doesn't see the
> > "side-effect" of the ranking function.
>
> That guess is wrong.
ah, and i already thought to be already on a higher level of understanding
postgresql...
> I think the problem is that you are trying to update multiple rows in
> the same statement, which would require a "reset ranking" between each
> row, which this approach doesn't provide for.
no thats not the point, i guess (which might be wrong again)
but i still don't understand why it doesn't work:
this is my important query named *Q* :=
SELECT
*,
ranking(r1.gc_gesamtpunkte, r1.gc_gesamtsiege) AS rank
FROM (
SELECT
mg_name,
gc_gesamtpunkte,
gc_gesamtsiege
FROM temp_gc
ORDER BY gc_gesamtpunkte DESC, gc_gesamtsiege DESC, mg_name ASC
) AS r1
this way it works:
CREATE TEMP TABLE ranking AS *Q*;
EXECUTE 'UPDATE temp_gc SET gc_rank = ranking.rank
FROM ranking WHERE temp_gc.mg_name = ranking.mg_name;';
and this way it doesn't:
UPDATE temp_gc
SET gc_rank = ranking.rank
FROM (*Q*)
ranking
WHERE temp_gc.mg_name = ranking.mg_name;
;
i want to update multiple rows, but the all data in table temp_gc doesnt need
a reset of the ranking.
> The whole thing looks mighty fragile in other ways; anything involving a
> single global variable isn't going to work nicely in very many cases.
> Consider casting your solution as an aggregate instead...
I know that this is not the best solution but it is the fastest. A corrolated
subquery with aggregates takes ages in opposite to the ranking function
solution.
But by the time of writing i have a new problem with my solution posted today
with subject "strange error with temp table: pg_type_typname_nsp_index"
kind regards,
janning
