Databases
StackOverflow2013
Postsdbo
POHow to get value of $1 parameter from executed prepared statement (inside a trigger using a current_query())
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to get value of $1 parameter from executed prepared statement (inside a trigger using a current_query())
    text
    copied!<p>In a trigger I want to see what sql query triggered this trigger. I used <strong>current_query()</strong> function of postgresql (8.4).</p> <p>Everything is fine, but if trigger is executed by prepared statement I get placeholders ($1) instead of correct values. for example (logged query):</p> <pre><code>delete from some_table where id=$1 </code></pre> <p>Is there a way to get/have this values/parameters?</p> <p><strong>Edited (example added):</strong></p> <pre><code>--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; </code></pre> <p>Creating a trigger:</p> <pre><code>create trigger test_trigger before delete on some_table for each row execute procedure save_query(); </code></pre> <p>Prepared statement is executed from hibernate.</p> <p><strong>Edit again (java part added)</strong></p> <pre><code>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 :) } } } </code></pre> <p>You need a jdbc driver - <a href="http://jdbc.postgresql.org/download/postgresql-8.4-702.jdbc4.jar" rel="nofollow">click</a>.</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