?
Solved

Creating an sp procedure in PostgreSQL database

Posted on 2014-10-05
10
Medium Priority
?
379 Views
Last Modified: 2014-10-11
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?
0
Comment
Question by:Gerhardpet
  • 6
  • 4
10 Comments
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 40362273
Hi!

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

There you have the the trigger you want.

Regards,
     Tomas Helgi
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 40362390
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
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 40362465
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 1

Author Comment

by:Gerhardpet
ID: 40362831
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
 
LVL 26

Accepted Solution

by:
Tomas Helgi Johannsson earned 2000 total points
ID: 40363143
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
 
LVL 1

Author Comment

by:Gerhardpet
ID: 40363458
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
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 40363526
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
 
LVL 1

Author Comment

by:Gerhardpet
ID: 40363530
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
 
LVL 1

Assisted Solution

by:Gerhardpet
Gerhardpet earned 0 total points
ID: 40363532
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
 
LVL 1

Author Closing Comment

by:Gerhardpet
ID: 40374576
My comment is also selected as solution since that is the final working solution for me.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question