Message-ID: <426E04FD.318032AA@rodos.fzk.de>
Date: Tue, 26 Apr 2005 11:08:13 +0200
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: grupos@carvalhaes.net
Cc: pgsql-sql@postgresql.org
Subject: Re: UPDATE WITH ORDER BY
References: <fddc0c5705042511464f94be68@mail.gmail.com>
	<20050425202406.GA3289@winnie.fuhr.org>
	<426DB1A0.2000604@carvalhaes.net>
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

> Rodrigo Carvalhaes wrote:
> 
> Hi Guys!
> 
> I need to make an UPDATE on a column reordering it with a sequence
> using order by a description.
> Confusing??? Well.. Let me give an example...
> 
> Today, my table it's organized like this:
> 
> Code     / Description
> 9          Orange
> 15         Apple
> 1          Pear
> 3          Tomato
> 
> I wanna to reorganize (reordering the code from 1 to ... ordering by
> description)
> 
> Code     / Description
> 1          Apple
> 2          Orange
> 3          Pear
> 4          Tomato
> 
> I created a sequence but I am having no succes to use it because
> UPDATE don't accept ORDER BY CLAUSE. The "ideal SQL" is UPDATE table
> SET code = nextval('sequence') ORDER BY description
> 
> I searched a lot on the NET without ant "tip" for my case.
> It's a very simple need but I am not able to solve it...
> 
> Anyone knows how I can do it?
> 
> Cheers,
> 
> --
> Rodrigo Carvalhaes
> 
I doubt this can be done by a single SQL command. 
My approach is a function. I did: 
CREATE TABLE fruittable(
fruitcode INTEGER,
fruitname TEXT
);
INSERT INTO fruittable VALUES( 9,	   'Orange' );
INSERT INTO fruittable VALUES( 15,	   'Apple'  );
INSERT INTO fruittable VALUES( 1,	   'Pear'   );
INSERT INTO fruittable VALUES( 3,	   'Tomato' );
SELECT * FROM fruittable ORDER BY fruitname ;
 fruitcode | fruitname 
-----------+-----------
        15 | Apple
         9 | Orange
         1 | Pear
         3 | Tomato

CREATE OR REPLACE FUNCTION reorder_fruitcode() RETURNS INTEGER AS '

DECLARE
 newcode INTEGER ;
 fruitrecord RECORD ;

BEGIN
 newcode := 1 ;
 
 FOR fruitrecord IN SELECT * FROM fruittable ORDER BY fruitname LOOP
 
  RAISE NOTICE ''fruitname is %'', fruitrecord.fruitname ;
  UPDATE fruittable SET fruitcode = newcode 
   WHERE fruitname = fruitrecord.fruitname ;
 
  newcode := newcode + 1 ;
 
 END LOOP ;
 
 RETURN 1;
END;
' LANGUAGE plpgsql;

SELECT reorder_fruitcode();
SELECT * FROM fruittable ORDER BY fruitname ;
 fruitcode | fruitname 
-----------+-----------
         1 | Apple
         2 | Orange
         3 | Pear
         4 | Tomato
(4 rows)
Voila. 

Regards, Christoph