Re: Converting sql anywhere to postgres
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 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.kla...@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.kla...@aklaver.com
Re: Converting sql anywhere to postgres
> On Aug 16, 2023, at 1:35 PM, Adrian Klaver 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 >>> , 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.kla...@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 effort the latter code path needs to be cognizant of the current state of the record (“me” column).
Re: Converting sql anywhere to postgres
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 , 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.kla...@aklaver.com
Re: Converting sql anywhere to postgres
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 , 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?
Re: Converting sql anywhere to postgres
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 , 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 >
Re: Converting sql anywhere to postgres
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
Re: Converting sql anywhere to postgres
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. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Converting sql anywhere to postgres
I’m fairly confident you can do this using a Rule. It would even be fairly simple. Be careful, though: Rules are Postgres’ biggest potential foot gun. Guyren G Howe On Aug 15, 2023 at 08:05 -0700, 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. > > 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’ >
Re: Converting sql anywhere to postgres
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
<http://www.passfield.co.uk/>
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
<https://aka.ms/AAb9ysg>
From: Adrian Klaver
Sent: Tuesday, August 15, 2023 7:19:56 PM
To: Georg H. ; Russell Rose | Passfield Data Systems
; pgsql-general@lists.postgresql.org
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.kla...@aklaver.com
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.kla...@aklaver.com
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
Re: Converting sql anywhere to postgres
> On 15/08/2023 17:04 CEST Russell Rose | Passfield Data Systems > 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
Re: 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.kla...@aklaver.com
Converting sql anywhere to postgres
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'
