From: Josh Berkus <josh@agliodbs.com>
To: Jean-Luc Lachance <jllachan@nsd.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to update record in a specified order
Date: Fri, 9 Aug 2002 15:12:59 -0700
Message-ID: <200208091512.59239.josh@agliodbs.com> (raw)
In-Reply-To: <3D543B55.CE5F5DEF@nsd.ca>
References: <no.Yo.N.nN.0208091642320.2280-100000@business.com>
<200208091437.52702.josh@agliodbs.com>
<3D543B55.CE5F5DEF@nsd.ca>
JLL,
So, you want to update a field with a NEXTVAL counter, but with the counter
ordered by another column?
If so, you will have to use a procedure. Ordering your UPDATEs is not part
of SQL -- it requires a procedural element. Here's a simple procedure (you
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 = NEXTVAL(''my_sequence'')
WHERE my_table.id = v_rec.id;
END LOOP;
RETURN ''Done updating.'';
END;'
LANGUAGE 'plpgsql';
--
-Josh Berkus
Aglio Database Solutions
San Francisco
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: josh@agliodbs.com, jllachan@nsd.ca
Subject: Re: How to update record in a specified order
In-Reply-To: <200208091512.59239.josh@agliodbs.com>
* 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