Re: Converting sql anywhere to postgres

[Russell Rose | Passfield Data Systems <russellrose@xxxxxxxxxxxxxxx>]

The timestamp default in Sql Anywhere is quite easy to convert with a trigger as I can check the old and new values and if they are the same then update. It is very unlikely that the application will manually set a timestamp that is identical to the value
 stored. This is not the case with a text field. I think I am going to have to look through the whole application to check how things are set and change the code as required.

Russell Rose

Developer

 

 

 

01404 514400

 

Passfield Data Systems Ltd  VAT Registration No: 673 8387 86  Company Registration No: 3130617  Registered address: The Globe, 165 High Street, Honiton, EX14 1LQ, United Kingdom

 

This email is sent in confidence for the addressee(s) only.  If you receive this communication in error, please notify us immediately and delete any copies.  Passfield Data Systems Ltd cannot accept responsibility for any loss or damage arising from any use of this e-mail or attachments. Any views expressed may not necessarily

 

---

From: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
Sent: Tuesday, August 15, 2023 7:19:56 PM
To: Georg H. <georg-h@xxxxxxxxxxxxxxx>; Russell Rose | Passfield Data Systems <russellrose@xxxxxxxxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxxxxxxxx <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
Subject: Re: Converting sql anywhere to postgres

 

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