Skip site navigation (1) Skip section navigation (2)

Re: Serial field

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 (view raw or flat)
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/

In response to

Responses

pgsql-novice by date

Next:From: Robby SlaughterDate: 2001-07-12 23:48:10
Subject: RE: Serial field
Previous:From: SuperDutyTA73Date: 2001-07-12 19:47:16
Subject: Best formal training for PostgreSQL use?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group