CODEWITHSUNDEEP
postgresqlplpgsql
SMW
SMW

Reputation: 339

In PostgreSQL can trigger know which function invoked it?

I have a trigger as follows:

CREATE TRIGGER A
  AFTER INSERT OR UPDATE OR DELETE
  ON TableA
  FOR EACH ROW
  EXECUTE PROCEDURE func1();

and the trigger function:

CREATE OR REPLACE FUNCTION func1()
  RETURNS trigger AS
$BODY$
begin
    insert into tableinfo values(...);
    return new;
end ;
$BODY$
  LANGUAGE plpgsql VOLATILE

Now I have many functions that insert/update/delete from TableA. Is there a way in the trigger to know which function invoke it?

something like:

CREATE OR REPLACE FUNCTION func1()
  RETURNS trigger AS
$BODY$
begin
    if functionname='insertnewrecord' or functionname='deleterecord'  then 
        insert into tableinfo values(...);
         return new;
    else
       return NULL;
    end if;
end ;
$BODY$
  LANGUAGE plpgsql VOLATILE

is it doable? can I limit the trigger to do something based on which function invoke it?

Upvotes: 2

Views: 368

Answers (1)

Egor Rogov
Egor Rogov

Reputation: 5398

You can get information from call stack using get diagnostics as described here.

Something like that:

CREATE OR REPLACE FUNCTION func1()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
declare
  stack text;
begin
  get diagnostics stack = pg_context;
  raise notice E'--- Call Stack ---\n%',stack;
  ...
return new;
end;
$function$;

Test function:

CREATE OR REPLACE FUNCTION insertnewrecord()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
begin
  insert into TableA values(...);
end;
$function$

And result:

select insertnewrecord();
NOTICE:  --- Call Stack ---
PL/pgSQL function func1() line 6 at GET DIAGNOSTICS
SQL statement "insert into tbl values(10)"
PL/pgSQL function insertnewrecord() line 3 at SQL statement
CONTEXT:  SQL statement "insert into tbl values(10)"
PL/pgSQL function insertnewrecord() line 3 at SQL statement
 insertnewrecord 
-----------------

(1 row)

So you can analyze stack in func1() and make decisions based on it, though it's not very handy.

Upvotes: 2

Related Questions