postgresql – 如何从执行的预准备语句中获取$1参数的值(使用cur
发布时间:2020-12-13 18:10:56 所属栏目:百科 来源:网络整理
导读:在触发器中我想看看sql查询触发了这个触发器.我使用了 postgresql(8.4)的current_query()函数. 一切都很好,但是如果触发器是通过预备语句执行的,我会获得占位符($1)而不是正确的值.例如(已记录的查询): delete from some_table where id=$1 有没有办法获得/
在触发器中我想看看sql查询触发了这个触发器.我使用了
postgresql(8.4)的current_query()函数.
一切都很好,但是如果触发器是通过预备语句执行的,我会获得占位符($1)而不是正确的值.例如(已记录的查询): delete from some_table where id=$1 有没有办法获得/拥有这个值/参数? 已编辑(已添加示例): --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; 创建一个触发器: create trigger test_trigger before delete on some_table for each row execute procedure save_query(); 准备好的语句是从hibernate执行的. 再次编辑(添加了java部分) 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 :) } } } 你需要一个jdbc驱动程序 – click.
这是一些工作代码(对于Debian用户:只需安装postgresql-pltcl-8.4包并运行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(); 但是我无法在预准备语句中获取占位符(它返回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) 编辑: 作为一种解决方法使用OLD记录(在Tcl中表示为数组),从那里获取id列并使用 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; 结果: java -classpath '.:postgresql-8.4-702.jdbc4.jar' DeleteUsingPreparedStmt TABLE log_table; id | query ----+--------------------------------- 1 | delete from floors where id = 1 (1排) (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |