CODEWITHSUNDEEP
postgresqltriggersglobal-variables
Trixl
Trixl

Reputation: 144

Accessing variables inside trigger function

I'm trying to make a trigger function to create a time stamp based on a base date stored in a variable plus an interval in seconds.

This base date is given to the psql script with the -v option, e.g. "-v start_time='2013-10-10 13:48:00'".

I want to access this variable from within a trigger function a do something like:

NEW.mytimestamp = timestamp :start_time + interval NEW.elapsed_seconds ' s';

Unfortunately I cannot figure out the right syntax for that. Any ideas?

Upvotes: 1

Views: 1139

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45805

It is impossible. psql variables (accessed via :varname) are client side variables. Trigger functions are executed on the server and cannot access these variables.

There is a way around this, but a little difficult (one cannot simple initialize values via command line). You can use custom configuration setting variables:

postgres=# select set_config('public.xxx', '10', false);
 set_config 
------------
 10
(1 row)

create or replace function foo_trg()
returns trigger as $$
begin
  raise notice '%', current_setting('public.xxx');
  return new;
end;
$$ language plpgsql;

create table foo(a int);

create trigger hh before insert on foo for each row execute procedure foo_trg();

postgres=# insert into foo values(200);
NOTICE:  10
INSERT 0 1

Another (more established) technique would be to use an auxiliary table.

On second thought, trigger parametrization (based on some global value) is usually a terrible idea. It indicates you are doing some wrong. Use a function instead.

Upvotes: 3

Related Questions