pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsimple trigger question ...
10+ messages / 3 participants
[nested] [flat]
* simple trigger question ...
@ 2003-04-03 02:41 Marc G. Fournier <scrappy@hub.org>
2003-04-03 03:44 ` Re: simple trigger question ... Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Marc G. Fournier @ 2003-04-03 02:41 UTC (permalink / raw)
To: pgsql-sql
I want to create a trigger on a table that for every insert, in performs a
nextval on a sequence ...
Something like:
CREATE TRIGGER on_company_company_id_seq
AFTER INSERT ON company FOR EACH ROW
EXECUTE PROCEDURE nextval('company_company_id_seq');
I'm embaressingly new at triggers ... is this possible?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: simple trigger question ...
2003-04-03 02:41 simple trigger question ... Marc G. Fournier <scrappy@hub.org>
@ 2003-04-03 03:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-03 04:06 ` Re: simple trigger question ... Marc G. Fournier <scrappy@hub.org>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2003-04-03 03:44 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@hub.org>; +Cc: pgsql-sql
"Marc G. Fournier" <scrappy@hub.org> writes:
> I want to create a trigger on a table that for every insert, in performs a
> nextval on a sequence ...
Er ... you just want to bump the sequence and throw away the actual
value? You don't want to store the value somewhere?
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: simple trigger question ...
2003-04-03 02:41 simple trigger question ... Marc G. Fournier <scrappy@hub.org>
2003-04-03 03:44 ` Re: simple trigger question ... Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-04-03 04:06 ` Marc G. Fournier <scrappy@hub.org>
2003-04-03 04:16 ` Re: simple trigger question ... Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Marc G. Fournier @ 2003-04-03 04:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Wed, 2 Apr 2003, Tom Lane wrote:
> "Marc G. Fournier" <scrappy@hub.org> writes:
> > I want to create a trigger on a table that for every insert, in performs a
> > nextval on a sequence ...
>
> Er ... you just want to bump the sequence and throw away the actual
> value? You don't want to store the value somewhere?
Correct
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: simple trigger question ...
2003-04-03 02:41 simple trigger question ... Marc G. Fournier <scrappy@hub.org>
2003-04-03 03:44 ` Re: simple trigger question ... Tom Lane <tgl@sss.pgh.pa.us>
2003-04-03 04:06 ` Re: simple trigger question ... Marc G. Fournier <scrappy@hub.org>
@ 2003-04-03 04:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-03 04:31 ` Re: simple trigger question ... Marc G. Fournier <scrappy@hub.org>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2003-04-03 04:16 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@hub.org>; +Cc: pgsql-sql
"Marc G. Fournier" <scrappy@hub.org> writes:
>> Er ... you just want to bump the sequence and throw away the actual
>> value? You don't want to store the value somewhere?
> Correct
Then you need something like (untested)
CREATE FUNCTION mytrig() RETURNS TRIGGER AS '
begin
perform nextval(''seq'');
return new;
end' LANGUAGE plpgsql;
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: simple trigger question ...
2003-04-03 02:41 simple trigger question ... Marc G. Fournier <scrappy@hub.org>
2003-04-03 03:44 ` Re: simple trigger question ... Tom Lane <tgl@sss.pgh.pa.us>
2003-04-03 04:06 ` Re: simple trigger question ... Marc G. Fournier <scrappy@hub.org>
2003-04-03 04:16 ` Re: simple trigger question ... Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-04-03 04:31 ` Marc G. Fournier <scrappy@hub.org>
2003-04-03 04:33 ` Re: simple trigger question ... Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Marc G. Fournier @ 2003-04-03 04:31 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Okay, so I do have to create the FUNCTION first, I can't do it without?
Can a TRIGGER pass an arg to the FUNCTION?
On Wed, 2 Apr 2003, Tom Lane wrote:
> "Marc G. Fournier" <scrappy@hub.org> writes:
> >> Er ... you just want to bump the sequence and throw away the actual
> >> value? You don't want to store the value somewhere?
>
> > Correct
>
> Then you need something like (untested)
>
> CREATE FUNCTION mytrig() RETURNS TRIGGER AS '
> begin
> perform nextval(''seq'');
> return new;
> end' LANGUAGE plpgsql;
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: simple trigger question ...
2003-04-03 02:41 simple trigger question ... Marc G. Fournier <scrappy@hub.org>
2003-04-03 03:44 ` Re: simple trigger question ... Tom Lane <tgl@sss.pgh.pa.us>
2003-04-03 04:06 ` Re: simple trigger question ... Marc G. Fournier <scrappy@hub.org>
2003-04-03 04:16 ` Re: simple trigger question ... Tom Lane <tgl@sss.pgh.pa.us>
2003-04-03 04:31 ` Re: simple trigger question ... Marc G. Fournier <scrappy@hub.org>
@ 2003-04-03 04:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-03 10:12 ` Re: simple trigger question ... Marc G. Fournier <scrappy@hub.org>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2003-04-03 04:33 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@hub.org>; +Cc: pgsql-sql
"Marc G. Fournier" <scrappy@hub.org> writes:
> Okay, so I do have to create the FUNCTION first, I can't do it without?
Check.
> Can a TRIGGER pass an arg to the FUNCTION?
Yes, but only simple constant strings. (In this context, it might
make sense for the CREATE TRIGGER command to tell the function the
name of the specific sequence to increment.)
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: simple trigger question ...
2003-04-03 02:41 simple trigger question ... Marc G. Fournier <scrappy@hub.org>
2003-04-03 03:44 ` Re: simple trigger question ... Tom Lane <tgl@sss.pgh.pa.us>
2003-04-03 04:06 ` Re: simple trigger question ... Marc G. Fournier <scrappy@hub.org>
2003-04-03 04:16 ` Re: simple trigger question ... Tom Lane <tgl@sss.pgh.pa.us>
2003-04-03 04:31 ` Re: simple trigger question ... Marc G. Fournier <scrappy@hub.org>
2003-04-03 04:33 ` Re: simple trigger question ... Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-04-03 10:12 ` Marc G. Fournier <scrappy@hub.org>
2003-04-03 14:26 ` Re: simple trigger question ... Rod Taylor <rbt@rbt.ca>
2003-04-03 14:38 ` Re: simple trigger question ... Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 10+ messages in thread
From: Marc G. Fournier @ 2003-04-03 10:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Wed, 2 Apr 2003, Tom Lane wrote:
> "Marc G. Fournier" <scrappy@hub.org> writes:
> > Okay, so I do have to create the FUNCTION first, I can't do it without?
>
> Check.
>
> > Can a TRIGGER pass an arg to the FUNCTION?
>
> Yes, but only simple constant strings. (In this context, it might
> make sense for the CREATE TRIGGER command to tell the function the
> name of the specific sequence to increment.)
'K, that makes it workable ... final question (I hope!) ... RULEs vs
TRIGGERs? I ended up doing it as a RULE, since I could do it without a
function ... but is there a reason why that is a Bad Idea? Or in a case
like this, it doesn't really matter?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: simple trigger question ...
2003-04-03 02:41 simple trigger question ... Marc G. Fournier <scrappy@hub.org>
2003-04-03 03:44 ` Re: simple trigger question ... Tom Lane <tgl@sss.pgh.pa.us>
2003-04-03 04:06 ` Re: simple trigger question ... Marc G. Fournier <scrappy@hub.org>
2003-04-03 04:16 ` Re: simple trigger question ... Tom Lane <tgl@sss.pgh.pa.us>
2003-04-03 04:31 ` Re: simple trigger question ... Marc G. Fournier <scrappy@hub.org>
2003-04-03 04:33 ` Re: simple trigger question ... Tom Lane <tgl@sss.pgh.pa.us>
2003-04-03 10:12 ` Re: simple trigger question ... Marc G. Fournier <scrappy@hub.org>
@ 2003-04-03 14:26 ` Rod Taylor <rbt@rbt.ca>
2003-04-03 23:00 ` Re: simple trigger question ... Marc G. Fournier <scrappy@hub.org>
1 sibling, 1 reply; 10+ messages in thread
From: Rod Taylor @ 2003-04-03 14:26 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@hub.org>; +Cc: pgsql-sql
> 'K, that makes it workable ... final question (I hope!) ... RULEs vs
> TRIGGERs? I ended up doing it as a RULE, since I could do it without a
> function ... but is there a reason why that is a Bad Idea? Or in a case
> like this, it doesn't really matter?
Rules are per statement, not per row.
An INSERT INTO ... SELECT ... statement will cause a single execution of
the rule even if you insert several hundred rows.
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: simple trigger question ...
2003-04-03 02:41 simple trigger question ... Marc G. Fournier <scrappy@hub.org>
2003-04-03 03:44 ` Re: simple trigger question ... Tom Lane <tgl@sss.pgh.pa.us>
2003-04-03 04:06 ` Re: simple trigger question ... Marc G. Fournier <scrappy@hub.org>
2003-04-03 04:16 ` Re: simple trigger question ... Tom Lane <tgl@sss.pgh.pa.us>
2003-04-03 04:31 ` Re: simple trigger question ... Marc G. Fournier <scrappy@hub.org>
2003-04-03 04:33 ` Re: simple trigger question ... Tom Lane <tgl@sss.pgh.pa.us>
2003-04-03 10:12 ` Re: simple trigger question ... Marc G. Fournier <scrappy@hub.org>
2003-04-03 14:26 ` Re: simple trigger question ... Rod Taylor <rbt@rbt.ca>
@ 2003-04-03 23:00 ` Marc G. Fournier <scrappy@hub.org>
0 siblings, 0 replies; 10+ messages in thread
From: Marc G. Fournier @ 2003-04-03 23:00 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: pgsql-sql
On Thu, 3 Apr 2003, Rod Taylor wrote:
> > 'K, that makes it workable ... final question (I hope!) ... RULEs vs
> > TRIGGERs? I ended up doing it as a RULE, since I could do it without a
> > function ... but is there a reason why that is a Bad Idea? Or in a case
> > like this, it doesn't really matter?
>
> Rules are per statement, not per row.
>
> An INSERT INTO ... SELECT ... statement will cause a single execution of
> the rule even if you insert several hundred rows.
Ah, okay, definitely not a good thing to use then ... thanks, will finish
what I started with TRIGGERs instead :)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: simple trigger question ...
2003-04-03 02:41 simple trigger question ... Marc G. Fournier <scrappy@hub.org>
2003-04-03 03:44 ` Re: simple trigger question ... Tom Lane <tgl@sss.pgh.pa.us>
2003-04-03 04:06 ` Re: simple trigger question ... Marc G. Fournier <scrappy@hub.org>
2003-04-03 04:16 ` Re: simple trigger question ... Tom Lane <tgl@sss.pgh.pa.us>
2003-04-03 04:31 ` Re: simple trigger question ... Marc G. Fournier <scrappy@hub.org>
2003-04-03 04:33 ` Re: simple trigger question ... Tom Lane <tgl@sss.pgh.pa.us>
2003-04-03 10:12 ` Re: simple trigger question ... Marc G. Fournier <scrappy@hub.org>
@ 2003-04-03 14:38 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 10+ messages in thread
From: Tom Lane @ 2003-04-03 14:38 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@hub.org>; +Cc: pgsql-sql
"Marc G. Fournier" <scrappy@hub.org> writes:
> 'K, that makes it workable ... final question (I hope!) ... RULEs vs
> TRIGGERs? I ended up doing it as a RULE, since I could do it without a
> function ... but is there a reason why that is a Bad Idea? Or in a case
> like this, it doesn't really matter?
A lot of people have found that rules don't behave quite the way they
want, particularly with regard to things like the number of times that
side-effects happen. Better test it out.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2003-04-03 23:00 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-03 02:41 simple trigger question ... Marc G. Fournier <scrappy@hub.org>
2003-04-03 03:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-03 04:06 ` Marc G. Fournier <scrappy@hub.org>
2003-04-03 04:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-03 04:31 ` Marc G. Fournier <scrappy@hub.org>
2003-04-03 04:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-03 10:12 ` Marc G. Fournier <scrappy@hub.org>
2003-04-03 14:26 ` Rod Taylor <rbt@rbt.ca>
2003-04-03 23:00 ` Marc G. Fournier <scrappy@hub.org>
2003-04-03 14:38 ` Tom Lane <tgl@sss.pgh.pa.us>
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