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