Database Agnostic Conditional

Question

I'm writing a Drupal module and I want it to be agnostic between PostgreSQL and MySQL. I have a field that gets its value from a conditional, and I originally wrote it as an IF(). Then I found out that IF() is a MySQL-ism. So, I looked up the conditionals for PostgreSQL and CASE seemed to be the best tool.

However, I noticed that CASE ends with END in PostgreSQL, while in MySQL it ends with END CASE. Will this destroy my hopes for database agnosticism? Can I write an agnostic query with a conditional?

Answer 1

If you're using CASE expressions in a SELECT statement, you should be looking at this page in the MySQL docs. As shown there, you use END to end a CASE expression.

If, on the other hand, you're using CASE as part of a flow control statement (like you might use in a stored procedure to conditionally execute other statements), both databases would use END CASE to end a CASE statement.

Answer 2

The SQL Function Syntax between MySQL and PostgreSQL will never converge as you wish UNLESS you are willing to take a chance on some weird way to emulate the IF() function in a convoluted manner:

WRITE A STORED FUNCTION !!!

The IF conditional function could be called MyIF and do the following:

• you could pass the necessary parameters to MyIF
• define whatever CASE structure or stored procedure logic you want
• you will have to define the MyIF stored function in MySQL and PostgreSQL

Hence, the query can be appear to be agnostic when calling the MyIF Function.

Give it a Try !!!