pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedknowing which columns have beend UPDATEd inside a TRIGGER?
4+ messages / 3 participants
[nested] [flat]
* knowing which columns have beend UPDATEd inside a TRIGGER?
@ 2000-10-24 13:55 Louis-David Mitterrand <cunctator@apartia.ch>
0 siblings, 1 reply; 4+ messages in thread
From: Louis-David Mitterrand @ 2000-10-24 13:55 UTC (permalink / raw)
To: pgsql-sql@hub.org
Hello,
Is there a way to know which columns are being UPDATEd or INSERTEd from
inside a trigger, either in C or pl/pgsql?
Thanks in advance,
--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org
If at first you don't succeed, redefine success.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: knowing which columns have beend UPDATEd inside a TRIGGER?
@ 2000-10-24 22:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Louis-David Mitterrand <cunctator@apartia.ch>
0 siblings, 2 replies; 4+ messages in thread
From: Tom Lane @ 2000-10-24 22:51 UTC (permalink / raw)
To: ldm@apartia.ch; +Cc: pgsql-sql@hub.org
Louis-David Mitterrand <cunctator@apartia.ch> writes:
> Is there a way to know which columns are being UPDATEd or INSERTEd from
> inside a trigger, either in C or pl/pgsql?
Huh? An INSERT always inserts all columns, by definition. Some of them
might be null and/or equal to their default values, but they're all
there.
For an UPDATE, you could check to see whether old.col = new.col.
This would miss the case where an UPDATE command is explicitly setting
a column to the same value it already had; dunno if you care or not.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: knowing which columns have beend UPDATEd inside a TRIGGER?
@ 2000-10-25 06:23 Louis-David Mitterrand <cunctator@apartia.ch>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Louis-David Mitterrand @ 2000-10-25 06:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql@hub.org
On Tue, Oct 24, 2000 at 06:51:03PM -0400, Tom Lane wrote:
> Louis-David Mitterrand <cunctator@apartia.ch> writes:
> > Is there a way to know which columns are being UPDATEd or INSERTEd from
> > inside a trigger, either in C or pl/pgsql?
>
> Huh? An INSERT always inserts all columns, by definition. Some of them
> might be null and/or equal to their default values, but they're all
> there.
*slap* Doh! Thanks for clearing up my mind about this ;-)
> For an UPDATE, you could check to see whether old.col = new.col.
> This would miss the case where an UPDATE command is explicitly setting
> a column to the same value it already had; dunno if you care or not.
That is so obvious I didn't think about it, and it's exactly what I
need.
Thanks a lot,
--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org
Hi. This is my friend, Jack Shit, and you don't know him.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: knowing which columns have beend UPDATEd inside a TRIGGER?
@ 2000-10-25 07:21 Tomas Berndtsson <tomas@nocrew.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tomas Berndtsson @ 2000-10-25 07:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: ldm@apartia.ch; pgsql-sql@hub.org
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Louis-David Mitterrand <cunctator@apartia.ch> writes:
> > Is there a way to know which columns are being UPDATEd or INSERTEd from
> > inside a trigger, either in C or pl/pgsql?
>
> Huh? An INSERT always inserts all columns, by definition. Some of them
> might be null and/or equal to their default values, but they're all
> there.
>
> For an UPDATE, you could check to see whether old.col = new.col.
> This would miss the case where an UPDATE command is explicitly setting
> a column to the same value it already had; dunno if you care or not.
Another way, is to have an extra boolean column called "updated", or
something like that. When you do the UPDATE, you make sure that always
updates the row with a 't' in that column. Then you do whatever you
need to do with the newly updated rows, and when done, you run another
UPDATE to set all rows to 'f' in the "updated" column.
Takes two updates, but might sometimes be easier than comparing the
rows to see if they've changed.
Tomas
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2000-10-25 07:21 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-10-24 13:55 knowing which columns have beend UPDATEd inside a TRIGGER? Louis-David Mitterrand <cunctator@apartia.ch>
2000-10-24 22:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-10-25 06:23 ` Louis-David Mitterrand <cunctator@apartia.ch>
2000-10-25 07:21 ` Tomas Berndtsson <tomas@nocrew.org>
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