Re: renumber id's in correct order (compact id's) - Mailing list pgsql-general

If you don't mind creating a psql function, I guess you could do something
like that



CREATE OR REPLACE FUNCTION Update_voev_content( ) RETURNS int4 AS $$
DECLARE

_record   RECORD;
_rank     int4;

BEGIN

_rank := 0;
FOR _record IN ( SELECT rank FROM voev_content ORDER BY rank )
LOOP
  UPDATE voev_content SET rank = _rank WHERE rank = _record.rank;
  _rank := _rank + 1;
END LOOP;


  RETURN _rank;

END

$$ LANGUAGE 'plpgsql';


then SELECT Update_voev_content() should do the trick....

Regards,

Patrick

----------------------------------------------------------------------------
---------------
Patrick Fiche
email : patrick.fiche@aqsacom.com
tél : 01 69 29 36 18
----------------------------------------------------------------------------
---------------




-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of peter pilsl
Sent: mardi 21 juin 2005 15:23
To: Martijn van Oosterhout
Cc: PostgreSQL List
Subject: Re: [GENERAL] renumber id's in correct order (compact id's)


Martijn van Oosterhout wrote:
> How about:
>
> update table set id = (select count(*) from table t2 where t2.id <=
table.id);
>
> Ugly as hell, but it should work.
>


thnx a lot. But it does not work as expected cause the update-statement
ist not commiting for the whole table during the execution. So the
resulting order can be different from the original order, which is what
I try to avoid.


example with real-work-database. entries with rank=0 are excluded from
the query.


knowledge=# select  rank,kategorie,titel from voev_content where
kategorie=5 order by rank;

  rank | kategorie |        titel
------+-----------+----------------------
     0 |         5 | hauptaktivitäten
     3 |         5 | test
     4 |         5 | startseite
     5 |         5 | Salzburger Gespräche
(4 rows)

knowledge=# update voev_content set rank = (select count(*) from
voev_content t2 where t2.id <= voev_content.id and t2.kategorie=5 and
t2.id !=0) where kategorie=5 and rank!=0;

UPDATE 3


knowledge=# select  rank,kategorie,titel from voev_content where
kategorie=5 order by rank;
  rank | kategorie |        titel
------+-----------+----------------------
     0 |         5 | hauptaktivitäten
     1 |         5 | Salzburger Gespräche
     2 |         5 | test
     3 |         5 | startseite
(4 rows)


note that test now is ordered as second (excluding the rank=0-entry)
while it was ordered first in the original configuration.

thnx,
peter


> Hope this helps,
>
> On Tue, Jun 21, 2005 at 10:06:40AM +0200, peter pilsl wrote:
>
>>
>>I've entries with id's like:
>>
>> x | id
>>---+----
>> b |  1
>> a |  4
>> e |  5
>> c | 12
>> d | 19
>>(5 rows)
>>
>>
>>now I'd like to have the id in continuing number to get:
>>
>> x | id
>>---+----
>> b |  1
>> a |  2
>> e |  3
>> c |  4
>> d |  5
>>(5 rows)
>>
>>
>>Simpliest way to do would be to create a sequence and update the whole
>>table using nextval on the sequencec. Unfortunately UPDATE does not know
>>about an order-statement.
>>
>>Any Idea,
>>thnx,
>>peter
>>
>>
>>
>>
>>
>>--
>>mag. peter pilsl
>>goldfisch.at
>>IT-management
>>tel +43 699 1 3574035
>>fae +43 699 4 3574035
>>pilsl@goldfisch.at
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 3: if posting/reading through Usenet, please send an appropriate
>>      subscribe-nomail command to majordomo@postgresql.org so that your
>>      message can get through to the mailing list cleanly
>
>


--
mag. peter pilsl
goldfisch.at
IT-management
tel +43 699 1 3574035
fae +43 699 4 3574035
pilsl@goldfisch.at

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match