Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Jean-Luc Lachance <jllachan@nsd.ca>
Subject: Re: How to update record in a specified order
Date: Fri, 9 Aug 2002 15:12:59 -0700
Cc: pgsql-sql@postgresql.org
References: <no.Yo.N.nN.0208091642320.2280-100000@business.com>
	<200208091437.52702.josh@agliodbs.com> <3D543B55.CE5F5DEF@nsd.ca>
In-Reply-To: <3D543B55.CE5F5DEF@nsd.ca>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200208091512.59239.josh@agliodbs.com>

JLL,

So, you want to update a field with a NEXTVAL counter, but with the counter=
=20
ordered by another column?

If so, you will have to use a procedure.   Ordering your UPDATEs is not par=
t=20
of SQL -- it requires a procedural element.   Here's a simple procedure (yo=
u=20
debug it):

CREATE PROCEDURE add_my_table_counter ()
RETURNS TEXT AS '
DECLARE v_rec RECORD;
BEGIN
	WHILE v_rec IN SELECT * FROM my_table ORDER BY last_name LOOP
		UPDATE my_table SET counter_field =3D NEXTVAL(''my_sequence'')
		WHERE my_table.id =3D v_rec.id;
	END LOOP;
	RETURN ''Done updating.'';
END;'
LANGUAGE 'plpgsql';

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco