Re: Converting sql anywhere to postgres - Mailing list pgsql-general

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@aklaver.com