From: Christoph Haller <ch@rodos.fzk.de>
To: grupos@carvalhaes.net
Cc: pgsql-sql@postgresql.org
Subject: Re: UPDATE WITH ORDER BY
Date: Tue, 26 Apr 2005 11:08:13 +0200
Message-ID: <426E04FD.318032AA@rodos.fzk.de> (raw)
References: <fddc0c5705042511464f94be68@mail.gmail.com>
<20050425202406.GA3289@winnie.fuhr.org>
<426DB1A0.2000604@carvalhaes.net>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, grupos@carvalhaes.net
Subject: Re: UPDATE WITH ORDER BY
In-Reply-To: <426E04FD.318032AA@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox