Обсуждение: auto-increment integer field for each row

Greetings,
I'm trying to create a trigger which will auto-increment the integer
stored in one of the 'not null' table rows each time that row is
updated.

I tried to use the trigger example here, but it silently fails to work:
http://archives.postgresql.org/pgsql-sql/2000-02/msg00147.php

I can repeatedly update column 'b' in the table of that example, and
'a' will remain '1'.

I'm guessing that maybe plpgsql has changed sufficiently since that
example was written (almost 10! years ago), that its no longer
compatible with the version (8.1.10) that I'm using.

many thanks for any advice/pointers.

-Lonni

Lonni J Friedman <netllama@gmail.com> writes:
> I tried to use the trigger example here, but it silently fails to work:
> http://archives.postgresql.org/pgsql-sql/2000-02/msg00147.php

That example seems to be lacking a CREATE TRIGGER command, so
there's nothing telling the system to call the function.
It never worked as given --- read the whole thread.

The only really out-of-date part of it is that nowadays
"returns trigger" is preferred to "returns opaque".
But you need CREATE TRIGGER.

            regards, tom lane

On Thu, Dec 3, 2009 at 2:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Lonni J Friedman <netllama@gmail.com> writes:
>> I tried to use the trigger example here, but it silently fails to work:
>> http://archives.postgresql.org/pgsql-sql/2000-02/msg00147.php
>
> That example seems to be lacking a CREATE TRIGGER command, so
> there's nothing telling the system to call the function.
> It never worked as given --- read the whole thread.

I thought that I had read the whole thread, but never saw any mention
of a missing 'create trigger' command.  Anyway, once I created the
trigger, it worked perfectly.  For the sake of posterity, the create
trigger command that I used was (my table name is appstracker, and my
function name is appstracker_incrementer) :

create trigger appstracker_incrementer_trigger before update on
appstracker FOR EACH ROW EXECUTE PROCEDURE appstracker_incrementer() ;

>
> The only really out-of-date part of it is that nowadays
> "returns trigger" is preferred to "returns opaque".
> But you need CREATE TRIGGER.

And its even nice enough to warn me about this:
WARNING:  changing return type of function appstracker_incrementer
from "opaque" to "trigger"
CREATE TRIGGER

thanks Tom!