Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 7 down vote accepted

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.

share|improve this answer
    
Thanks! I linked to the wrong mysql page. –  user1359 Jul 15 '11 at 19:16
    
OK, but now your question is a bit confused, because the MySQL CASE expression ends with END, not END CASE. –  dmc Jul 15 '11 at 19:25
    
I'll be darned! Everything is okay then :) –  user1359 Jul 15 '11 at 19:31
    
@dmd - you said: "it sounds like the syntax differs between the two databases". Actually they are the same for the the case statement as well –  Jack Douglas Jul 18 '11 at 8:02
    
@Jack Douglas: Ah, very good! I'll edit my answer to remove that bit of inaccuracy. Thanks. –  dmc Jul 18 '11 at 15:52

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 !!!

share|improve this answer
    
+1, that's what stored procedures are there for. Plus it will help with other statements also. –  StanleyJohns Jul 18 '11 at 18:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.