pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Georg H. <georg-h@silentrunner.de>
To: russellrose@passfield.co.uk
To: pgsql-general@lists.postgresql.org
Subject: Re: Converting sql anywhere to postgres
Date: Tue, 15 Aug 2023 18:43:11 +0200
Message-ID: <b99a285f-6fb3-b1b8-0c2b-ca08dd23f6e2@silentrunner.de> (raw)
In-Reply-To: <VI1P191MB0638057C9A6AD74BD09758AAF214A@VI1P191MB0638.EURP191.PROD.OUTLOOK.COM>
References: <VI1P191MB0638057C9A6AD74BD09758AAF214A@VI1P191MB0638.EURP191.PROD.OUTLOOK.COM>

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

view thread (14+ messages)  latest in thread

Message-ID: <b99a285f-6fb3-b1b8-0c2b-ca08dd23f6e2@silentrunner.de>
Permalink:  ../b99a285f-6fb3-b1b8-0c2b-ca08dd23f6e2@silentrunner.de/
Also on:    postgresql.org/message-id/b99a285f-6fb3-b1b8-0c2b-ca08dd23f6e2@silentrunner.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: georg-h@silentrunner.de, russellrose@passfield.co.uk
  Subject: Re: Converting sql anywhere to postgres
  In-Reply-To: <b99a285f-6fb3-b1b8-0c2b-ca08dd23f6e2@silentrunner.de>
  References: <VI1P191MB0638057C9A6AD74BD09758AAF214A@VI1P191MB0638.EURP191.PROD.OUTLOOK.COM> <b99a285f-6fb3-b1b8-0c2b-ca08dd23f6e2@silentrunner.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox