Re: getting the ranks out of items with SHARED

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