How to get the value of parameter $ 1 from a prepared statement (inside a trigger using current_query ())

Question

In the trigger, I want to see which sql query called this trigger. I used the current_query () function of the postgresql (8.4) function.

Everything is fine, but if the trigger is executed by a prepared statement, I get placeholders ($ 1) instead of the correct values. e.g. (registered request):

delete from some_table where id=$1 

Is there any way to get / have these values ​​/ parameters?

Edited (added example):

 --table for saving query create table log_table ( query text ) --table for trigger create table some_table ( id text ) --function itself CREATE 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; 

Creating a trigger:

 create trigger test_trigger before delete on some_table for each row execute procedure save_query(); 

The prepared statement is executed from sleep mode.

Change again (added java part)

 import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class DeleteUsingPreparedStmt { public static void main(String[] args) { try { String deleteString = "delete from floors where id = ? "; final int idToDelte = 1; Class.forName("org.postgresql.Driver"); String url = "jdbc:postgresql://127.0.0.1:5432/YOUR_DATABASE"; Connection conn = DriverManager.getConnection(url, "user", "password"); PreparedStatement deleteStmt = conn.prepareStatement(deleteString); deleteStmt.setInt(1, idToDelte); deleteStmt.executeUpdate(); } catch (Exception e) { //hide me :) } } } 

You need a jdbc driver - click .

Answer 1

Here is the working code (for Debian users: just install the postgresql-pltcl-8.4 package and run CREATE LANGUAGE pltcl; )

 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(); 

However, I cannot get placeholders in the prepared message (it returns EXECUTE deleteFromFloors(2); ):

 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) 

EDIT:

As a workaround, use the OLD entry (represented as an array in Tcl), enter the identifier column there, and use the replace function, replace $1 instead. Here you have two solutions: PL / pgSQL and PL / Tcl:

 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; 

Result:

 java -classpath '.:postgresql-8.4-702.jdbc4.jar' DeleteUsingPreparedStmt TABLE log_table; id | query ----+--------------------------------- 1 | delete from floors where id = 1 

(1 line)