X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 653005372C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 26 Apr 2005 14:04:17 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 54951-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 26 Apr 2005 17:04:07 +0000 (GMT)
Received: from nitrogenio.carvalhaes.net (unknown [200.175.206.50])
	by svr1.postgresql.org (Postfix) with ESMTP id 5F4CD53875
	for <pgsql-sql@postgresql.org>; Tue, 26 Apr 2005 14:04:04 -0300 (ADT)
Received: from [192.168.0.199] ([192.168.0.199]) (authenticated bits=0)
	by nitrogenio.carvalhaes.net (8.13.4/8.13.4) with ESMTP id
	j3QGsaRD003474; Tue, 26 Apr 2005 13:54:41 -0300
Message-ID: <426E6736.8030403@carvalhaes.net>
Date: Tue, 26 Apr 2005 13:07:18 -0300
From: Rodrigo Carvalhaes <grupos@carvalhaes.net>
Reply-To: grupos@carvalhaes.net
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Christoph Haller <ch@rodos.fzk.de>
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> <426E04FD.318032AA@rodos.fzk.de>
In-Reply-To: <426E04FD.318032AA@rodos.fzk.de>
Content-Type: text/html; charset=ISO-8859-2
Content-Transfer-Encoding: quoted-printable
X-Triade-MailScanner-Information: Por favor,
	entre em contato para obter detalhes
X-Triade-MailScanner: Found to be clean
X-MailScanner-From: grupos@carvalhaes.net
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.837 tagged_above=0 required=5 tests=AWL, HTML_20_30,
	HTML_MESSAGE, HTML_TEXT_AFTER_BODY, HTML_TITLE_EMPTY,
	MIME_HTML_ONLY
X-Spam-Level: *
X-Archive-Number: 200504/384
X-Sequence-Number: 21277

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content=3D"text/html;charset=3DISO-8859-2" http-equiv=3D"Content-Ty=
pe">
  <title></title>
</head>
<body bgcolor=3D"#ffffff" text=3D"#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=3D"mid426E04FD.318032AA@rodos.fzk.de" type=3D"cite">
  <blockquote type=3D"cite">
    <pre wrap=3D"">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 =3D 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=3D""><!---->I doubt this can be done by a single SQL command.=
=20
My approach is a function. I did:=20
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=20
-----------+-----------
        15 | Apple
         9 | Orange
         1 | Pear
         3 | Tomato

CREATE OR REPLACE FUNCTION reorder_fruitcode() RETURNS INTEGER AS '

DECLARE
 newcode INTEGER ;
 fruitrecord RECORD ;

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

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

Regards, Christoph

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to <a class=3D"moz-txt-link-abbreviated" hre=
f=3D"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=3D"moz-signature" cols=3D"72">--=20


Abra=E7o,

Rodrigo Carvalhaes
DBA PostgreSQL
Moderador grupo siga-br</pre>
</body>
<br />--
<br />Esta mensagem foi verificada pelo sistema de antiv=EDrus e=20
<br /> acredita-se estar livre de perigo.
</html>