Обсуждение: knowing which columns have beend UPDATEd inside a TRIGGER?
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.
Hello, how must i write a NULL value in a text file for the \copy command to understand it? NULL, \0 doesnt work.. how must i write a boolean value in a text file for the \copy command to understand it? t doesnt work, however in a file written by /copy command it looks as t or f .. tried to search archives, but it seems deadly slow... Thanks in advance, sandis
\N is normally used to represent NULL in a text file, however you can change that to another string (or an empty string) using COPY FROM ... WITH NULL AS Mark Sandis Jerics wrote: > > Hello, > > how must i write a NULL value in a text file for the \copy command to > understand it? > NULL, \0 doesnt work.. > > how must i write a boolean value in a text file for the \copy command to > understand it? > t doesnt work, however in a file written by /copy command it looks as > t or f .. > > tried to search archives, but it seems deadly slow... > > Thanks in advance, > sandis
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
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.
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