Creating an sp procedure in PostgreSQL database

I'm attempting to audit a PostgreSQL database and found this blog here to accomplish this http://doublebyteblog.wordpress.com/2014/02/11/quick-guide-to-auditing-a-postgresql-database/comment-page-1/#comment-114

Everything works as per the post. The only thing missing is the query to create the sp procedure to monitor every table in the database. Towards the end the author has this


CREATE TRIGGER fr_frame_audit
AFTER INSERT OR UPDATE OR DELETE ON fr_frame
FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func();

Open in new window

In the example above, I created a trigger for auditing table ‘fr_frame’ (of course you can create a sp procedure to generate these statements, if you want to generate a trigger for auditing every table in the database…

Can someone help me with creating the sp procedure to monitor every table?
LVL 1
GerhardpetAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tomas Helgi JohannssonCommented:
Hi!

Look at the manual :)
https://wiki.postgresql.org/wiki/Audit_trigger

There you have the the trigger you want.

Regards,
     Tomas Helgi
0
GerhardpetAuthor Commented:
Yes I know, I have already implemented the trigger. I'm looking for help in creating the sp procedure to monitor every table.

The manual does not provide help for creating sp procedure.
0
Tomas Helgi JohannssonCommented:
Hi!

What the author of the blog you posted is saying that you can create a SP that loops through the system catalog
and creates a audit trigger for all tables in the database or a particular schema.

http://www.postgresql.org/docs/9.1/static/view-pg-tables.html

Something like this

CREATE OR REPLACE PROCEDURE addAuditTrigger( myschema text ) 
DECLARE
  rec record;
  sql text;
  sqltrigg text;
BEGIN
 
  sql = 'select * from pg_tables where schemaname = '||myschema||'; ;
	
  for rec in EXECUTE sql loop
    sqltrigg = 'CREATE TRIGGER fr_frame_audit AFTER INSERT OR UPDATE OR DELETE ON '|| rec.schemaname||'.'||rec.tablename ||' FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); ';
    EXECUTE sqltrigg;
  end loop;
	  
END;
$$ LANGUAGE plpgsql;

Open in new window


Regards,
    Tomas Helgi
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

GerhardpetAuthor Commented:
I hope you can bear with me for a bit longer. I'm just beginning to learn database functions. I know basic queries.

This created the gltrans_audit for the gltrans table which works fine
CREATE TRIGGER gltrans_audit
AFTER INSERT OR UPDATE OR DELETE ON gltrans
FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); 

Open in new window


I want to create the trigger for all tables in the public schema

I'm not sure what to add to the top row. I changed the myschema to public
CREATE OR REPLACE PROCEDURE addAuditTrigger ( public text ) 

Open in new window

but then get this error

ERROR:  syntax error at or near "PROCEDURE"
LINE 1: CREATE OR REPLACE PROCEDURE addAuditTrigger ( public text ) 

Open in new window


I hope you wont mind holding my hand a bit here :-)
0
Tomas Helgi JohannssonCommented:
Hi!

Sorry my mistake this should be like this

CREATE OR REPLACE FUNCTION addAuditTrigger( myschema varchar(30) ) 
RETURNS void AS $$
DECLARE
  rec record;
  sql text;
  sqltrigg text;
BEGIN
 
  sql = 'select * from pg_tables where schemaname = '||myschema||';' ;
	
  for rec in EXECUTE sql loop
    sqltrigg = 'CREATE TRIGGER fr_frame_audit AFTER INSERT OR UPDATE OR DELETE ON '|| rec.schemaname||'.'||rec.tablename ||' FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); ';
    EXECUTE sqltrigg;
  end loop;
	  
END;
$$ LANGUAGE plpgsql;

Open in new window


and called like this for example

 SELECT addAuditTrigger('public');

where public is the public schema where your tables are.

Be aware that you maybe don't want to have this trigger added to the system catalog.

Regards,
       Tomas Helgi
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GerhardpetAuthor Commented:
Still not able to make it work.

I copied and pasted the code below from your post and when I execute it I don't get any errors but it does not create the triggers for any of the tables.

I'm not sure what you mean by calling like this SELECT addAuditTrigger('public');

How would that look like in one query. Sorry I'm very new at all of this.

CREATE OR REPLACE FUNCTION addAuditTrigger( public varchar(30) ) 
RETURNS void AS $$
DECLARE
  rec record;
  sql text;
  sqltrigg text;
BEGIN
 
  sql = 'select * from pg_tables where schemaname = '||myschema||';' ;
	
  for rec in EXECUTE sql loop
    sqltrigg = 'CREATE TRIGGER _audit AFTER INSERT OR UPDATE OR DELETE ON '|| rec.schemaname||'.'||rec.tablename ||' FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); ';
    EXECUTE sqltrigg;
  end loop;
	  
END;
$$ LANGUAGE plpgsql;

Open in new window

0
Tomas Helgi JohannssonCommented:
HI!

What do you get if you call this query ?
select * from pg_tables where schemaname = 'PUBLIC' ; 

Open in new window


To call/execute the trigger you can do it like this.
SELECT addAuditTrigger('public');

Open in new window

Where PUBLIC is the schemaname where your tables that you want to audit are.
It may be that the pg_tables table are under a schemaname different from what the function is created under ( I don't have access to postgresql right now ) so you will probably need to add the schemaname in front of the tablename.

Regards,
    Tomas Helgi
0
GerhardpetAuthor Commented:
I got it now. It is working if I call the function like you said

This is how I called it
SELECT addAuditTrigger('public');

Open in new window


Learned something new. Thank you for your patients!
0
GerhardpetAuthor Commented:
This is my final working version. I had to add 2 more single ' to this part '''||myschema||''';' ;

Someone else figured that out for me

CREATE OR REPLACE FUNCTION addAuditTrigger( myschema varchar(30) ) 
RETURNS void AS $$
DECLARE
  rec record;
  sql text;
  sqltrigg text;
BEGIN
 
  sql = 'select * from pg_tables where schemaname = '''||myschema||''';' ;
	
  for rec in EXECUTE sql loop
    sqltrigg = 'CREATE TRIGGER fr_frame_audit AFTER INSERT OR UPDATE OR DELETE ON '|| rec.schemaname||'.'||rec.tablename ||' FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); ';
    EXECUTE sqltrigg;
  end loop;
	  
END;
$$ LANGUAGE plpgsql;

Open in new window


And then I called it with this query

SELECT addAuditTrigger('public');

Open in new window

0
GerhardpetAuthor Commented:
My comment is also selected as solution since that is the final working solution for me.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.