StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here is some working code (for Debian users: just install <code>postgresql-pltcl-8.4</code> package and run <code>CREATE LANGUAGE pltcl;</code>)</p> <pre><code>CREATE TABLE log_table ( id serial, query text ); CREATE TABLE floors ( id serial, value text ); INSERT INTO floors(value) VALUES ('aaa'), ('bbb'); CREATE OR REPLACE FUNCTION save_query() RETURNS trigger AS $$ switch $TG_op { DELETE { spi_exec "INSERT INTO log_table (query) VALUES (current_query())" } default { return OK } } return OK $$ LANGUAGE pltcl; CREATE TRIGGER test_trigger BEFORE DELETE ON floors FOR EACH ROW EXECUTE PROCEDURE save_query(); </code></pre> <p>However I can't get placeholders in prepared statement (it returns <code>EXECUTE deleteFromFloors(2);</code>):</p> <pre><code>TABLE log_table; id | query ----+------- (0 rows) DELETE FROM floors WHERE id = 1; DELETE 1 TABLE log_table; id | query ----+---------------------------------- 1 | DELETE FROM floors WHERE id = 1; (1 row) PREPARE deleteFromFloors(integer) AS DELETE FROM floors WHERE id = $1; PREPARE EXECUTE deleteFromFloors(2); DELETE 1 TABLE log_table; id | query ----+---------------------------------- 1 | DELETE FROM floors WHERE id = 1; 2 | EXECUTE deleteFromFloors(2); (2 rows) </code></pre> <p><strong>EDIT:</strong></p> <p>As a workaround use <code>OLD</code> record (represented as array in Tcl), fetch id column from there and use <a href="http://www.postgresql.org/docs/8.4/static/functions-string.html" rel="nofollow"><code>replace</code></a> function to put it instead of <code>$1</code>. Here you have two solutions: PL/pgSQL and PL/Tcl:</p> <pre><code>CREATE OR REPLACE FUNCTION save_query() RETURNS TRIGGER AS $$ BEGIN INSERT INTO log_table (query) VALUES (replace(current_query(), '$1', OLD.id::text)); RETURN OLD; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION save_query() RETURNS trigger AS $$ switch $TG_op { DELETE { spi_exec "INSERT INTO log_table (query) VALUES (replace(current_query(), '\$1', '$OLD(id)'))" } default { return OK } } return OK $$ LANGUAGE pltcl; </code></pre> <p>Result:</p> <pre><code>java -classpath '.:postgresql-8.4-702.jdbc4.jar' DeleteUsingPreparedStmt TABLE log_table; id | query ----+--------------------------------- 1 | delete from floors where id = 1 </code></pre> <p>(1 row)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload