Thread: Triggers on Stored Procedures

Hello.

First of all, I apologise if I am posting this in the wrong place.

I would like to implement aspect-oriented programming in SQL. To that
end, I figured I need a notification method whenever a stored
procedure is executed (If possible, with rudimentary filtering). I
have seen that this can be done with triggers for INSERT, UPDATE, and
DELETE commands, and with RULES  for SELECT commands.

My question is: Is there a way to do this to stored procedures,
without modifying the stored procedure code?

Thank you.
Omer Anson.

Omer Anson <oaanson@gmail.com> wrote:

> First of all, I apologise if I am posting this in the wrong place.

Not a bad place to start, I guess.  If it gets into the realm of an
enhancement request or proposal it might belong on -hackers, but
since you're asking about how it might be done now, this is good.

> I would like to implement aspect-oriented programming in SQL. To
> that end, I figured I need a notification method whenever a stored
> procedure is executed (If possible, with rudimentary filtering).

Well, PostgreSQL doesn't have stored procedures, but it has
functions, which are similar in many respects.

> I have seen that this can be done with triggers for INSERT,
> UPDATE, and DELETE commands, and with RULES  for SELECT commands.
>
> My question is: Is there a way to do this to stored procedures,
> without modifying the stored procedure code?

You could put "pointcut" schemas ahead of "concern" schemas (both
business logic and cross-cutting).  The "join points" would
effectively be the business logic concern functions and the "advice"
would be embodied in the cross-cutting concern functions.  You would
just need to build a tool to create the appropriate pointcut methods
to tie them together as desired, changing the searchpath locally, or
specifying concern function names with schema-qualification.

Of course, you could do the pointcut specification manually, one
method at a time, but that's not very "enterprisy".

-Kevin

Resending because I just noticed that the list was not copied on the
rest of the exchange.  (Omer, please keep the list copied in any
future exchanges, as it tends to make the discussion available on
search engines for those who may have similar questions.)  Omer
wasn't sure about what I was proposing, so I provided this example,
which Omer said was exactly what was needed.

Omer Anson <oaanson@gmail.com> wrote:

> Did I understand you correctly?

A simple example would probably be good:

test=# create schema pointcut;
CREATE SCHEMA
test=# create schema appconcern;
CREATE SCHEMA
test=# create schema crosscutconcern;
CREATE SCHEMA
test=# set search_path to
test-# pointcut,appconcern,crosscutconcern,public;
SET
test=# create function appconcern.addints(a int, b int)
test-# returns int language sql immutable strict
test-# as 'select $1 + $2;';
CREATE FUNCTION
test=# select addints(2,3);
 addints
---------
       5
(1 row)

test=# create function crosscutconcern.notifyenterfunc(funcname
text)
test-# returns void language plpgsql immutable strict as $$
test$# begin raise notice 'entering fucntion %', funcname; end;
test$# $$;
CREATE FUNCTION
test=# create function crosscutconcern.notifyleavefunc(funcname
text)
test-# returns void language plpgsql immutable strict
test-# as $$
test$# begin raise notice 'leaving fucntion %', funcname; end;
test$# $$;
CREATE FUNCTION
test=# create or replace function pointcut.addints(a int, b int)
test-# returns int language plpgsql immutable strict
test-# as $$
test$# declare resultval int;
test$# begin
test$# perform crosscutconcern.notifyenterfunc('addints');
test$# select appconcern.addints(a, b) into resultval;
test$# perform crosscutconcern.notifyleavefunc('addints');
test$# return resultval;
test$# end;
test$# $$;
CREATE FUNCTION
test=# select addints(2,3);
NOTICE:  entering fucntion addints
CONTEXT:  SQL statement "SELECT
crosscutconcern.notifyenterfunc('addints')"
PL/pgSQL function "addints" line 4 at PERFORM
NOTICE:  leaving fucntion addints
CONTEXT:  SQL statement "SELECT
crosscutconcern.notifyleavefunc('addints')"
PL/pgSQL function "addints" line 6 at PERFORM
 addints
---------
       5
(1 row)

Note the unqualified reference to addints was initially the bare
appconcern function and was later wrapped with crosscutting
functions by the pointcut function of the same name.

-Kevin