Search code examples
sqlrubypg

Ruby PG Submitted SQL

I'm looking for a PG method that returns the actual submitted SQL query to the database. I have this params statement sent to the database:

resclose = conn.exec_params( %q{ SELECT * FROM alpha WHERE alpha.pname ILIKE $1 ORDER BY id}, [submitted] )

This is a simple query with one parameter called submitted. Some of my other statements are much more complex. I'd like to test the actual SQL statement in a database client to see that I'm getting what I'm thinking I'm asking for.

Does PG have a method that I can use? I can't seem to find anything that returns the actual query.

Note: PG is a gem, not meant to be postgresql. PG is here

Solution

  • There is no way because the conn.exec_params is a native C implementation, and the returned object PG::Result also has no way to show the SQL. If you really want to see the query you can turn on statement logging in your postgress configuration and have a look at it

    You might however try to see if there is some information you might use in the error constants, but you'll have to force an error to get it (note that I changed the name of the table in the query to alpha_non_existent to force an error):

    begin
    resclose = conn.exec_params( %q{ SELECT * FROM alpha_non_existent WHERE pname ILIKE $1 ORDER BY id}, [submitted] )
rescue PG::Error => err
    p [
        err.result.error_field( PG::Result::PG_DIAG_SEVERITY ),
        err.result.error_field( PG::Result::PG_DIAG_SQLSTATE ),
        err.result.error_field( PG::Result::PG_DIAG_MESSAGE_PRIMARY ),
        err.result.error_field( PG::Result::PG_DIAG_MESSAGE_DETAIL ),
        err.result.error_field( PG::Result::PG_DIAG_MESSAGE_HINT ),
        err.result.error_field( PG::Result::PG_DIAG_STATEMENT_POSITION ),
        err.result.error_field( PG::Result::PG_DIAG_INTERNAL_POSITION ),
        err.result.error_field( PG::Result::PG_DIAG_INTERNAL_QUERY ),
        err.result.error_field( PG::Result::PG_DIAG_CONTEXT ),
        err.result.error_field( PG::Result::PG_DIAG_SOURCE_FILE ),
        err.result.error_field( PG::Result::PG_DIAG_SOURCE_LINE ),
        err.result.error_field( PG::Result::PG_DIAG_SOURCE_FUNCTION ),
    ]
end