Accessing variables inside trigger function

Question

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?

possible duplicate of [How do you use script variables in PostgreSQL?](http://stackoverflow.com/questions/36959/how-do-you-use-script-variables-in-postgresql) — Denis de Bernardy, Oct 10 '13 at 12:05
I'd suggest editing the question to highlight how it differs, thhen. By the way, and on a separate note, shouldn't you be using something like e.g. now(). If you're monitoring how long your scripts are taking, there's a time function that yields the current time, which you can subtract from the time of the start of the transaction. If not, redefining a trigger on the fly like what you're trying to do seems extremely fishy. — Denis de Bernardy, Oct 10 '13 at 12:12

score 3 · Answer 1 · edited Oct 10 '13 at 15:13

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.

Thank you. I should have realized these variables are client side! — Trixl, Oct 10 '13 at 14:23
@Trixl: More options to set "global variables" in these related answers [here](http://stackoverflow.com/questions/13316773/is-there-a-way-to-define-a-named-constant-in-a-postgresql-query) and [here](http://stackoverflow.com/questions/13172524/passing-user-id-to-postgresql-triggers/13172964). — Erwin Brandstetter, Oct 10 '13 at 15:18

Accessing variables inside trigger function

1 Answers1