audit sql queries

On 9/9/07, Dan99 <power919@gmail.com> wrote:

Any help with this would be greatly appreciated.

http://www.postgresql.org/docs/8.2/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-EXAMPLE

Dan99 wrote:

Hello,

I am working on auditing interactions with a pgsql database using
php. So my question is how can i go about obtaining the following
information by only being provided a pgsql query. I have asked this
same question in a php group however i would also like to ask it here
encase there is a way to do this entirely through postgres.

http://pgfoundry.org/search/?type_of_search=soft&amp;words=audit&amp;Search=Search

--
Richard Huxton
Archonet Ltd

On Sun, 2007-09-09 at 23:13 +0000, Dan99 wrote:

1. table(s) affected
2. column(s) affected
3. action performed on data (ie. update, insert, select, delete)
4. previous data for each row and column effected (if data changed or
deleted)
5. new data for each row and column effected (or existing data if data
is being selected)

Auditing SELECTs is the trickiest; everything else can be done with
triggers.

For SELECT, you can use a view over a set-returning function, where the
function actually reads the underlying data, however that could be bad
for performance.

Alternatively, there is also kind of a trick where you can use an
uncorrelated subquery in a view so that the select has the side effect
of executing a function, like so:

CREATE VIEW mytable_audit AS SELECT * FROM mytable WHERE (SELECT
audit_func());

audit_func() should always return true, and should also record the other
information that you need. This strategy may perform better than using a
set-returning function.

Regards,
Jeff Davis