Thread: Converting sql anywhere to postgres

On 8/15/23 08:04, Russell Rose | Passfield Data Systems wrote:
> 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.

Not that I know of. In Postgres an UPDATE is essentially a DELETE of the 
OLD tuple and an INSERT of a new tuple. You cannot determine whether the 
same value in a given field for the OLD/NEW records was explicitly set 
the same or just carried over.

> 
> 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’
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

> On 15/08/2023 17:04 CEST Russell Rose | Passfield Data Systems <russellrose@passfield.co.uk> wrote:
>
> 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’

Do you also have a timestamp column (let's say mod_time) that goes along with
mod_user (both updated together)?

In that case you can compare OLD.mod_time and NEW.mod_time in the BEFORE
trigger.  Only if the timestamp does not change should the trigger then assign
NEW.mod_user := current_user and NEW.mod_time := now().

Or use clock_timestamp() instead of now() if you need to handle multiple updates
of the same row in one transaction.  But this is only relevant when changing the
current user with SET ROLE during the transaction.

--
Erik

Hi,

@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; font-size:11.0pt; font-family:"Calibri",sans-serif; mso-ligatures:standardcontextual; mso-fareast-language:EN-US;}span.EmailStyle17 {mso-style-type:personal-compose; font-family:"Calibri",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif; mso-ligatures:standardcontextual; mso-fareast-language:EN-US;}div.WordSection1 {page:WordSection1;}

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

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

On 2023-08-15 18:43:11 +0200, Georg H. wrote:
> update mytest.autovalues set product='pear', updated_by=DEFAULT where key=2;
                                                         ^^^^^^^^
Oh, I didn't know that worked. Obvious in hindsight, of course (I've
been using DEFAULT in VALUES(...) for ages) but I never thought of it.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

I have just had a quick look at rules and I am not sure how it can be done. Rules still use the concept of NEW and OLD.
Ifmy original row has 'myfield' set to 'me' then I don't think I can tell the difference between: 

Update mytable set afield='something'
and
Update mytable set afield='something',myfield='me'

Within the rule I think NEW.myfield will be set to 'me' in both cases. Please can you explain how I can tell the
differencebetween the two update statements 

On 8/16/23 12:01, Rob Sargent wrote:
> On 8/16/23 12:30, Guyren Howe wrote:
>> For some reason, I was thinking the rule could see just the fields 
>> from the command, but you’re right; a rule won’t work. Sorry.
>>
>> Guyren G Howe
>> On Aug 15, 2023 at 23:22 -0700, Russell Rose | Passfield Data Systems 
>> <russellrose@passfield.co.uk>, wrote:
>>> I have just had a quick look at rules and I am not sure how it can be 
>>> done. Rules still use the concept of NEW and OLD. If my original row 
>>> has 'myfield' set to 'me' then I don't think I can tell the 
>>> difference between:
>>>
>>> Update mytable set afield='something'
>>> and
>>> Update mytable set afield='something',myfield='me'
>>>
>>> Within the rule I think NEW.myfield will be set to 'me' in both 
>>> cases. Please can you explain how I can tell the difference between 
>>> the two update statements
>>>
> If the original value in the user column is "me", what is the difference 
> between "set other_column = some_value, user = 'me'" and "set 
> other_column = some_value" at the business level?

Affirmation that the user updating the record explicitly set the user value.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

> On Aug 16, 2023, at 1:35 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 8/16/23 12:01, Rob Sargent wrote:
>> On 8/16/23 12:30, Guyren Howe wrote:
>>> For some reason, I was thinking the rule could see just the fields from the command, but you’re right; a rule won’t
work.Sorry. 
>>>
>>> Guyren G Howe
>>> On Aug 15, 2023 at 23:22 -0700, Russell Rose | Passfield Data Systems <russellrose@passfield.co.uk>, wrote:
>>>> I have just had a quick look at rules and I am not sure how it can be done. Rules still use the concept of NEW and
OLD.If my original row has 'myfield' set to 'me' then I don't think I can tell the difference between: 
>>>>
>>>> Update mytable set afield='something'
>>>> and
>>>> Update mytable set afield='something',myfield='me'
>>>>
>>>> Within the rule I think NEW.myfield will be set to 'me' in both cases. Please can you explain how I can tell the
differencebetween the two update statements 
>>>>
>> If the original value in the user column is "me", what is the difference between "set other_column = some_value,
user= 'me'" and "set other_column = some_value" at the business level? 
>
> Affirmation that the user updating the record explicitly set the user value.
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
Agreed.  But at the end of the day, the difference is what exactly?  Wouldn't auditing (short of sql logging) say “no
change”with respect to “me” column? 

This then is a client issue, no?  There has to be two paths in the client code, one which generates an update without
“me”and one which includes “me" and the second path does not take in to account current value.  If it’s worth the
effortthe latter code path needs to be cognizant of the current state of the record (“me” column).   

On 8/16/23 14:16, Rob Sargent wrote:
> 

>>>>> I have just had a quick look at rules and I am not sure how it can be done. Rules still use the concept of NEW
andOLD. If my original row has 'myfield' set to 'me' then I don't think I can tell the difference between:
 
>>>>>
>>>>> Update mytable set afield='something'
>>>>> and
>>>>> Update mytable set afield='something',myfield='me'
>>>>>
>>>>> Within the rule I think NEW.myfield will be set to 'me' in both cases. Please can you explain how I can tell the
differencebetween the two update statements
 
>>>>>
>>> If the original value in the user column is "me", what is the difference between "set other_column = some_value,
user= 'me'" and "set other_column = some_value" at the business level?
 
>>
>> Affirmation that the user updating the record explicitly set the user value.
>>
>> -- 
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>>
> Agreed.  But at the end of the day, the difference is what exactly?  Wouldn't auditing (short of sql logging) say “no
change”with respect to “me” column?
 

That is above my pay grade, that is for the OP to elaborate on. I was 
just saying that the Postgres server could not make that distinction 
with the information provided.



-- 
Adrian Klaver
adrian.klaver@aklaver.com