From: | "Georg H(dot)" <georg-h(at)silentrunner(dot)de> |
---|---|
To: | 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 16:43:11 |
Message-ID: | b99a285f-6fb3-b1b8-0c2b-ca08dd23f6e2@silentrunner.de |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2023-08-15 18:19:56 | Re: Converting sql anywhere to postgres |
Previous Message | Erik Wienhold | 2023-08-15 16:05:13 | Re: Converting sql anywhere to postgres |