Postgresql – Transform all columns records to lowercase

I ended up writing some code that transforms the SQL generated by the application into PostgreSQL-compatible SQL. It's pretty straightforward:

• Split the statement into sensible tokens, skipping single-quoted string literals
• Double-quote anything that is not a keyword or number

I also took advantage of this layer to transform calls to isnull to coalesce. So far it's working pretty well.

Took a while, but I figured out how to do it. Here is some example code:

pqxx::connection conn( ... );
conn.prepare( "test", "INSERT INTO mytable( name, binfile ) VALUES ($1, $2)" );
pqxx::work work( conn );
for ( ... )
{
    std::string name = "foo";
    void * bin_data = ...; // -- obviously do what you need to get the binary data...
    size_t bin_size = 123; // -- ...and the size of the binary data

    pqxx::binarystring blob( bin_data, bin_size );
    pqxx::result r = work.prepared( "test" )( name )( blob ).exec();
}
work.commit();