pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to tell if column set on update
5+ messages / 3 participants
[nested] [flat]
* how to tell if column set on update
@ 2009-07-20 13:19 chester c young <chestercyoung@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: chester c young @ 2009-07-20 13:19 UTC (permalink / raw)
To: pgsql-sql
within a trigger need to know if the UPDATE statement set a column. the column might be set to the old value or a different value.
(want to make sure the app is sending all necessary values)
thanks
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: how to tell if column set on update
@ 2009-07-21 00:07 nha <lyondif02@free.fr>
parent: chester c young <chestercyoung@yahoo.com>
0 siblings, 0 replies; 5+ messages in thread
From: nha @ 2009-07-21 00:07 UTC (permalink / raw)
To: Chester C Young <chestercyoung@yahoo.com>; +Cc: pgsql-sql
Hello,
Le 20/07/09 15:19, chester c young a écrit :
> within a trigger need to know if the UPDATE statement set a column. the column might be set to the old value or a different value.
>
> (want to make sure the app is sending all necessary values)
>
> thanks
>
If the column to test is known -- e.g. column MyCol --, NEW.MyCol and
OLD.MyCol -- respectively value of MyCol after UPDATE and value of MyCol
before UPDATE -- can be compared.
I may be wrong if you are looking for the column(s) that are really
updated, not if such a column is updated or not. In this case, each
column of the target table may be tested with their respective new and
old values as well.
Regards.
--
nha / Lyon / France.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: how to tell if column set on update
@ 2009-07-22 03:51 chester c young <chestercyoung@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: chester c young @ 2009-07-22 03:51 UTC (permalink / raw)
To: nha <lyondif02@free.fr>; +Cc: pgsql-sql
> Le 20/07/09 15:19, chester c young a écrit :
> > within a trigger need to know if the UPDATE statement
> set a column. the column might be set to the old value
> or a different value.
> >
> > (want to make sure the app is sending all necessary
> values)
> >
> > thanks
> >
>
> If the column to test is known -- e.g. column MyCol --,
> NEW.MyCol and
> OLD.MyCol -- respectively value of MyCol after UPDATE and
> value of MyCol
> before UPDATE -- can be compared.
for example,
create table t1( c1 int, c2 int );
insert into t1 values( 1, 2 );
1) update t1 set c1=4 where c1=1;
2) update t1 set c1=4, c2=2 where c1=1;
each update results in the same row, but in the second update c2 was actually set.
a trigger on the update - how do we know if c2 has been actually set or not?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: how to tell if column set on update
@ 2009-07-22 11:42 Frank Bax <fbax@sympatico.ca>
parent: chester c young <chestercyoung@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Frank Bax @ 2009-07-22 11:42 UTC (permalink / raw)
To: pgsql-sql
chester c young wrote:
>> Le 20/07/09 15:19, chester c young a écrit :
>>> within a trigger need to know if the UPDATE statement
>> set a column. the column might be set to the old value
>> or a different value.
>>> (want to make sure the app is sending all necessary
>> values)
>>> thanks
>>>
>> If the column to test is known -- e.g. column MyCol --,
>> NEW.MyCol and
>> OLD.MyCol -- respectively value of MyCol after UPDATE and
>> value of MyCol
>> before UPDATE -- can be compared.
>
> for example,
> create table t1( c1 int, c2 int );
> insert into t1 values( 1, 2 );
>
> 1) update t1 set c1=4 where c1=1;
> 2) update t1 set c1=4, c2=2 where c1=1;
>
> each update results in the same row, but in the second update c2 was actually set.
> a trigger on the update - how do we know if c2 has been actually set or not?
You cannot do that. You can only detect that a value has changed.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: how to tell if column set on update
@ 2009-07-23 01:42 nha <lyondif02@free.fr>
parent: Frank Bax <fbax@sympatico.ca>
0 siblings, 0 replies; 5+ messages in thread
From: nha @ 2009-07-23 01:42 UTC (permalink / raw)
To: Chester C Young <chestercyoung@yahoo.com>; +Cc: pgsql-sql
Hello,
Le 22/07/09 13:42, Frank Bax a écrit :
> chester c young wrote:
>>> Le 20/07/09 15:19, chester c young a écrit :
>>>> within a trigger need to know if the UPDATE statement
>>> set a column. the column might be set to the old value
>>> or a different value. [...]
>>>>
>>> If the column to test is known -- e.g. column MyCol --,
>>> NEW.MyCol and OLD.MyCol [...] can be compared.
>>
>> for example,
>> create table t1( c1 int, c2 int );
>> insert into t1 values( 1, 2 );
>>
>> 1) update t1 set c1=4 where c1=1;
>> 2) update t1 set c1=4, c2=2 where c1=1;
>>
>> each update results in the same row, but in the second update c2 was
>> actually set.
>> a trigger on the update - how do we know if c2 has been actually set
>> or not?
>
> You cannot do that. You can only detect that a value has changed.
>
There would be one way to detect a value change but it is more related
to application management than to data manipulation. For the column c2,
it could be assumed that a flag is set when (a row of) c2 is updated.
Such a flag may be stored in a separate table (while it could also be
added to table t1) that is updated with a trigger based on update on t1.
Regards.
--
nha / Lyon / France.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2009-07-23 01:42 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-07-20 13:19 how to tell if column set on update chester c young <chestercyoung@yahoo.com>
2009-07-21 00:07 ` nha <lyondif02@free.fr>
2009-07-22 03:51 Re: how to tell if column set on update chester c young <chestercyoung@yahoo.com>
2009-07-22 11:42 ` Frank Bax <fbax@sympatico.ca>
2009-07-23 01:42 ` nha <lyondif02@free.fr>
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