From: | Jason Earl <jdearl(at)yahoo(dot)com> |
---|---|
To: | Francois Thomas <FrancoisT(at)alsdesign(dot)fr>, "'pgsql-novice(at)postgresql(dot)org'" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Serial field |
Date: | 2001-07-12 20:57:40 |
Message-ID: | 20010712205740.9007.qmail@web10001.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
I am not entirely sure I understand what you mean, but
I think I understand, so I am going to give answering
your question a shot. But first of all I would highly
recommend using a newer version of PostgreSQL than
6.5.2. The latest stable version is 7.1.2 and it is
literally light years ahead of 6.5.2.
That being said the trick that I will outline should
work for a version 6.5 PostgreSQL database. At least
variations of this trick worked for me when I was
using a PostgreSQL version 6.5.3.
First off it appears to me that what you want is a
column that keeps track of the "revision level" of a
particular row. Every time the row got updated it's
particular revision number should increment. There
isn't a PostgreSQL type that does this for you, but it
is fairly easy to do using triggers and a simple
function.
Let's say that you had a table that looked like this:
CREATE TABLE foo (
rev int default 0,
name char(10)
);
And you created a function and trigger that looked
like this:
CREATE FUNCTION update_rev() RETURNS OPAQUE AS '
BEGIN
NEW.rev := NEW.rev + 1;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER foo_update_rev BEFORE INSERT OR UPDATE
ON foo
FOR EACH ROW EXECUTE PROCEDURE update_rev();
Now if you inserted a record into foo without
specifically setting rev like this:
INSERT INTO foo (name) VALUES ('Jason');
Your database would contain the following row:
rev | name
-----+------------
1 | Jason
So far so good. New rows added to the database would
automagically get a rev of 1. More importantly if you
decided to update this particular record with an
update statement like:
UPDATE foo SET name = 'Francois' WHERE name = 'Jason'
Your database would look like this. Note that when
the row was updated it triggered our trigger and
incremented rev by one.
rev | name
-----+------------
2 | Francois
Hopefully this is what you had in mind. If not please
try and restate your question with a little more
detail.
Take Care,
Jason
--- Francois Thomas <FrancoisT(at)alsdesign(dot)fr> wrote:
>
> Hello to all
>
> Unable to find an answer by searching the list
> archive, I ask my question
> here:
> I want to migrate a database to PostgreSQL 6.5.2
> I need an auto-increment field. I first tried the
> SERIAL type, but it
> doesn't fit my needs.
> In fact, I want to:
> 1/ keep the original INT value of my existing
> records from another server
> (say, first row can be "1", second "3", next one
> "17",...)
> 2/ have an automatic incrementation of the last
> value for a new record. For
> exemple, with the values above and a SERIAL field,
> the default value for a
> new field would be "1" (first use of the sequence)
> instead of "18" (last
> value+1)..
> I hope my english is not too obscure !
> Any advice will be welcome
> Regards
>
> --
> Franois THOMAS
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/
From | Date | Subject | |
---|---|---|---|
Next Message | Robby Slaughter | 2001-07-12 23:48:10 | RE: Serial field |
Previous Message | SuperDutyTA73 | 2001-07-12 19:47:16 | Best formal training for PostgreSQL use? |