Database Reference
In-Depth Information
EXECUTE PROCEDURE log_trigger('file', '/tmp/
test.json.log');
Any changes to the table done via
INSERT
,
UPDATE
, or
DELETE
are logged into
/tmp/test.json.log
. This file is initially owned by the same user running the
server, usually postgres; so to look at it you need to either be that user or root user,
or you have to change the permissions on the file created to allow reading.
If you want to test the UDP logging part, you just have to define another trigger with
different arguments:
CREATE TRIGGER test_audit_trigger_udp
AFTER INSERT OR UPDATE OR DELETE ON test
FOR EACH ROW
EXECUTE PROCEDURE log_trigger('udp',
'localhost', 9999);
Of course, you need something to listen at the UDP port there. A minimalist UDP
listener is provided for testing in
chapter07/logtrigger/
log_udp_listener.py
. Just run it, and it prints any UDP packets received to
stdout
.
Constructing queries
PL/Python does a good job of managing values passed to prepared query plans, but
a standard PostgreSQL query plan can take an argument in a very limited number of
places. Sometimes, you may want to construct whole queries, not just pass values
to predefined queries. For example, you can't have an argument for a table name, or
a field name.
So, how would you proceed if you want to construct a query from the function's ar-
guments and be sure that everything is quoted properly and no SQL injection would
be possible? PL/Python provides three functions to help you with proper quoting of
identifiers and data just for this purpose.
The function
plpy.quote_ident(name)
is meant for quoting identifiers, that is,
anything that names a database object or its attribute like a table, a view, a field
