From: Rodrigo Carvalhaes <grupos@carvalhaes.net>
To: Christoph Haller <ch@rodos.fzk.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: UPDATE WITH ORDER BY
Date: Tue, 26 Apr 2005 13:07:18 -0300
Message-ID: <426E6736.8030403@carvalhaes.net> (raw)
In-Reply-To: <426E04FD.318032AA@rodos.fzk.de>
References: <fddc0c5705042511464f94be68@mail.gmail.com>
<20050425202406.GA3289@winnie.fuhr.org>
<426DB1A0.2000604@carvalhaes.net>
<426E04FD.318032AA@rodos.fzk.de>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-2" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Thanksyou and Franz for your help. Simple and efficient... I was
blind.... The plpgsql "for" is the perfect solution<br>
<br>
It was great. Have a nice week!!!<br>
<br>
Cheers,<br>
<br>
Rodrigo Carvalhaes<br>
<br>
Christoph Haller wrote:
<blockquote cite="mid426E04FD.318032AA@rodos.fzk.de" type="cite">
<blockquote type="cite">
<pre wrap="">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
</pre>
</blockquote>
<pre wrap=""><!---->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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to <a class="moz-txt-link-abbreviated" href="mailto:majordomo@postgresql.org">majordomo@postgresql.org</a> so that your
message can get through to the mailing list cleanly
</pre>
</blockquote>
<br>
<pre class="moz-signature" cols="72">--
Abraço,
Rodrigo Carvalhaes
DBA PostgreSQL
Moderador grupo siga-br</pre>
</body>
<br />--
<br />Esta mensagem foi verificada pelo sistema de antivírus e
<br /> acredita-se estar livre de perigo.
</html>
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: grupos@carvalhaes.net, ch@rodos.fzk.de
Subject: Re: UPDATE WITH ORDER BY
In-Reply-To: <426E6736.8030403@carvalhaes.net>
* 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