pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAuto increment field when updating?
5+ messages / 5 participants
[nested] [flat]
* Auto increment field when updating?
@ 2000-02-16 11:08 Robin Keech <robin@dialogue.co.uk>
0 siblings, 2 replies; 5+ messages in thread
From: Robin Keech @ 2000-02-16 11:08 UTC (permalink / raw)
To: pgsql-sql
Hi, hope you can help. This may be a really stupid question, but here
goes....
I have a table called 'log'. This keeps track of an ongoing process.
The process writes records to the 'log' table using a unique id. If the
id does not exist then the process inserts, else it updates the existing
row. Only some fields are written to each time the row is updated.
There is one field called 'count' that ONLY needs to increment every
time that particular row AND the field 'count' are updated.
I know that I can select the information, increment it and update it
back again, but is there any way of setting up the database to do this
automatically? I am using PostgreSQL 6.5.
Thanks
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Auto increment field when updating?
@ 2000-02-16 14:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: Robin Keech <robin@dialogue.co.uk>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2000-02-16 14:36 UTC (permalink / raw)
To: Robin Keech <robin@dialogue.co.uk>; +Cc: pgsql-sql
Robin Keech <robin@dialogue.co.uk> writes:
> ... Only some fields are written to each time the row is updated.
> There is one field called 'count' that ONLY needs to increment every
> time that particular row AND the field 'count' are updated.
> I know that I can select the information, increment it and update it
> back again, but is there any way of setting up the database to do this
> automatically? I am using PostgreSQL 6.5.
Sure, use a trigger. Here's one I use for a table that wants to attach
a globally new sequence number to each created or updated row:
CREATE FUNCTION my_phistory_trigger() RETURNS OPAQUE AS '
BEGIN
NEW.sequenceNo := nextval(''my_phistory_Seq'');
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER my_phistory_trigger BEFORE INSERT OR UPDATE ON my_phistory
FOR EACH ROW EXECUTE PROCEDURE my_phistory_trigger();
but if you just want to increment the number that's there, you could do
NEW.sequenceNo := OLD.sequenceNo + 1;
(bearing in mind that this'll only work as an UPDATE trigger, not
INSERT). See the programmer's guide for doco on trigger functions.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Auto increment field when updating?
@ 2000-02-19 10:34 Peter Vazsonyi <neko@kredit.sth.szif.hu>
parent: Robin Keech <robin@dialogue.co.uk>
1 sibling, 1 reply; 5+ messages in thread
From: Peter Vazsonyi @ 2000-02-19 10:34 UTC (permalink / raw)
To: Robin Keech <robin@dialogue.co.uk>; +Cc: pgsql-sql
I hope this helps:
=> create table test(a int4 NOT NULL DEFAULT 1, b text);
=> create function tg_test() returns opaque as '
-> declare x text;
-> begin
-> new.a=old.a+1;
-> return new;
-> end;' language 'plpgsql';
=> insert into test(b) values('a');
INSERT 770064 1
=> update test set b='b';
UPDATE 1
=> select * from test;
a|b
-+-
2|b
(1 row)
Before this U need a createlang plpgsql too.
--
nek;(
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Auto increment field when updating?
@ 2000-02-20 02:16 Gordon Clarke <gordonc@acenet.net.au>
parent: Peter Vazsonyi <neko@kredit.sth.szif.hu>
0 siblings, 1 reply; 5+ messages in thread
From: Gordon Clarke @ 2000-02-20 02:16 UTC (permalink / raw)
To: Peter Vazsonyi <neko@kredit.sth.szif.hu>; +Cc: pgsql-sql
On Sat, 19 Feb 2000, Peter Vazsonyi wrote:
>
> Before this U need a createlang plpgsql too.
So what do u put in the lancompiler field? I presume the remaining fields
are 'f', 'f', '0'.
Cheers...Gordon
~~~~~~~~~~~~~~ 4ZzZ Brisbane's First Community FM Station ~~~~~~~~~~~~~~
The Demo Show - Supporting Australian Unsigned Artists
Mon nights 6-7pm (0800-0900UTC) on 4ZzZ 102.1 FM
http://www.4zzzfm.org.au/zed (RA feed)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Auto increment field when updating?
@ 2000-02-20 14:23 Peter Eisentraut <peter_e@gmx.net>
parent: Gordon Clarke <gordonc@acenet.net.au>
0 siblings, 0 replies; 5+ messages in thread
From: Peter Eisentraut @ 2000-02-20 14:23 UTC (permalink / raw)
To: Gordon Clarke <gordonc@acenet.net.au>; +Cc: Peter Vazsonyi <neko@kredit.sth.szif.hu>; pgsql-sql
On 2000-02-20, Gordon Clarke mentioned:
> On Sat, 19 Feb 2000, Peter Vazsonyi wrote:
>
> >
> > Before this U need a createlang plpgsql too.
>
> So what do u put in the lancompiler field? I presume the remaining fields
> are 'f', 'f', '0'.
Use the provided createlang script. It takes care of all that.
--
Peter Eisentraut Sernanders väg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2000-02-20 14:23 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-02-16 11:08 Auto increment field when updating? Robin Keech <robin@dialogue.co.uk>
2000-02-16 14:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-02-19 10:34 ` Peter Vazsonyi <neko@kredit.sth.szif.hu>
2000-02-20 02:16 ` Gordon Clarke <gordonc@acenet.net.au>
2000-02-20 14:23 ` Peter Eisentraut <peter_e@gmx.net>
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