Dear Adrian, Dear All! Hmmm... Then I remembered wrong. I thought that I can't set variables without defining them in the server's configuration somehow.
So: your example is good for us. I can set a variable and I can read this value. It's cool. Thank you for your help! Best regards dd Adrian Klaver <[email protected]> ezt írta (időpont: 2026. márc. 25., Sze, 18:51): > On 3/25/26 10:20 AM, Durumdara wrote: > > Hello! > > > > Sometimes we have to use "Current User ID", and "User Name" in the > > Triggers to make a log into a table. These values are based on our User, > > not in the PSQL role. > > > > Now we use a temporary table to do this. > > When the user logged into the application, we created a temporary table > > with the same name (user_info) and structure. This holds the data (id, > > name, machine info, ip address). > > > > In the trigger we try to find this table (in the LOCAL_TEMPORARY schema). > > Then we read the row into a JSON record, and then into PLPGSQL variables. > > Tables can exist with the same name, so this is the safest solution. > > If the User ID is invalid (none or empty) that means this is a > > background operation, and then we don't need to log the changes. > > > > But maybe there is a better way to somehow store some session based data > > and use it in the triggers. > > Because if these selects are slow, the trigger is also slow. So when I > > start an UPDATE command in a big table, maybe this slows down the whole > > operation. > > > > Note: > > A table with the PID key is not enough, because the PID is a repeated > > value. > > I logged it and in the Windows system there are many of the same values > > (10001, 10004, etc.). > > Ok, I can combine with session creation time. But for this I also need > > to start a select in the pg_stat_activty table. > > > > So maybe you have an easier way to point to a record in a session. > > Important: the PG servers are different, the lesser version is 11, and > > we have only a Database Owner role. We can't configure the server. > > > > What is your opinion? Is there any way to get session based data? > > As I read before, we can't set the session variables onfly. > > Maybe SET?: > > https://www.postgresql.org/docs/current/sql-set.html > > With LOCAL it is scoped to a transaction. > > Otherwise it persists for session unless a transaction is rolled back. > > As example: > > CREATE OR REPLACE FUNCTION public.session_test() > RETURNS void > LANGUAGE plpgsql > AS $function$ > DECLARE > _test_var varchar := current_setting('test.session_var', 't'); > BEGIN > RAISE NOTICE 'Variable is %', _test_var; > END; > $function$ > > > No variable set: > > test=# select session_test(); > NOTICE: Variable is <NULL> > session_test > -------------- > > (1 row) > > Variable set: > > test=# begin ; > BEGIN > test=*# set local test.session_var = 'test'; > SET > test=*# select session_test(); > NOTICE: Variable is test > session_test > -------------- > > (1 row) > > > > > Best regards > > dd > > > > > > > -- > Adrian Klaver > [email protected] >
