Re: Converting sql anywhere to postgres

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;
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)||

kind regards

Georg