On 8/15/23 09:43, Georg H. wrote:
Hi,
Am 15.08.2023 um 17:04 schrieb Russell Rose | Passfield Data Systems:
Hi there
I am trying to convert a SQL Anywhere database to postgres. Within SQL
anywhere a field can have a default value of ‘last user’. This means
that when you perform an update on a table, if the field is not
explicitly set then the current user is used. So for instance if I
have a field called mod_user in a table, but when I do an update on
the table and do not set mod_user then SQL Anywhere sets the field to
current_uer. I have tried to replicate this using a postgres trigger
in the before update. However, if I do not set the value then it
automatically picks up the value that was already in the field. Is
there a way to tell the difference between me setting the value to the
same as the previous value and postgres automatically picking it up.
If the field myfield contains the word ‘me’. Can I tell the difference
between:
Update table1 set field1=’something’,myfield=’me’
And
Update table1 set field1=’something’
maybe this is what you're looking for (without a trigger)
CREATE TABLE mytest.autovalues
(
key serial NOT NULL,
product text,
updated_by text DEFAULT current_user,
updated_at timestamp without time zone DEFAULT current_timestamp,
PRIMARY KEY (key)
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS mytest.autovalues
OWNER to postgres;
-- instead of current_user you may also use |session_user see
https://www.postgresql.org/docs/current/functions-info.html|
|
|
|then try:
|
|insert into mytest.autovalues (product) values ('apple') ;
insert into mytest.autovalues (product,updated_by) values
('apple','justanotheruser') ;
insert into mytest.autovalues (product,updated_by) values
('peach','justanotheruser') ;
select * from mytest.autovalues;
update mytest.autovalues set product='pear', updated_by=DEFAULT where key=2;
That is not going to catch the case the OP was interested in:
Current row:
updated_by='me'
update mytest.autovalues set product='pear', updated_by='me' where key=2
vs
update mytest.autovalues set product='pear' where key=2
In other distinguishing whether 'me' is explicitly set in the UPDATE or
whether it carried over from the OLD tuple.
select * from mytest.autovalues;|
|
|
|In case you want to "automate" the update command (not setting
|||updated_by to DEFAULT manually/programmatically)| you may use an on
update trigger that compares current_user/session_user with
old.|updated_by and if they are different you could set new.updated_by
to DEFAULT (or whatever logic fits your needs)||
Same problem as above, distinguishing between an explicitly set
update_by value and one carried over.
kind regards
Georg
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
