From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | "Georg H(dot)" <georg-h(at)silentrunner(dot)de>, russellrose(at)passfield(dot)co(dot)uk, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Converting sql anywhere to postgres |
Date: | 2023-08-15 18:19:56 |
Message-ID: | 7b59f904-7209-30a2-94f5-9b5a4f4554ed@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Jason Long | 2023-08-15 18:27:50 | Re: AW: PostgreSQL and GUI management |
Previous Message | Georg H. | 2023-08-15 16:43:11 | Re: Converting sql anywhere to postgres |