How to include PostgreSQL-specific code within portable SQL?

Question

In MySQL it is possible to include MySQL-specific SQL-statements within generic/standard SQL by using a specific comment syntax such as follows:

INSERT /*! DELAYED */ INTO foo VALUES (1, 2, 3);

This is described at http://dev.mysql.com/doc/refman/5.1/en/comments.html.

Is there any equivalent syntax or hack which could be used with PostgreSQL to embed PostgreSQL-specific statements in the same file?

I would like to make my application portable on both platforms but in some cases I can not find a generic way of doing things and need to do DB specific things. For example putting an automagically incremented column in a table is completely different on these DB engines but most other parts of the DB schema are exactly the same and can be shared. Thus I would rather include just a single create-the-database.sql file in the distribution as it is easier to maintain and feels neater.

Answer 1

Suck it up and create a "postgresql.sql" file and a "mysql.sql" file. It takes very little effort. You could go old-school and use cpp(1) that way both schemas are in the same file, and even interleaved.

% cat foo.sql
#ifdef USE_POSTGRESQL
CREATE TABLE pg_epicness (
  -- PostgreSQL schema def
);
#elif USE_MYSQL
CREATE TABLE phail (
  -- idontcareaboutmyusers schema def
);
#endif
% cat foo.sql | cpp -DUSE_POSTGRESQL | grep -v ^# > postgresql.sql
% cat foo.sql | cpp -DUSE_MYSQL | grep -v ^# > mysql.sql

Not pretty, but as you said, you were looking for a hack. Since there shouldn't be any leading pound characters in your .sql files anyway, it's "safe-ish."

Answer 2

My first thought is that I would include a preprocessing step when compiling your code which could then generate a middle tier for each database separately. You could then distribute whichever one was to be used or alternatively have a setting in the application to allow it to switch between the two.

You would need to have separate testing for each platform, but I would expect that anyway.

The preprocessor could be as simple as an intelligent search and replace or it could be more complex.

Answer 3

If the syntactic differences are structurally compatible enough, you can probably use macro expansion. M4 is a popular, portable macro processor. I've used it in several languages that didn't natively support macro processing, including SQL.

Answer 4

I'm not aware of an equivalent feature in Postgres. That syntax is pretty limited anyway: It only works when something is "extra" as opposed to "different".

The only way I know to do this is with code. Write a function to create your INSERT statement, for example. Have two versions of the function: one for Postgres, and another for MySQL. Embed any differences in the function. Then at run time set a flag or have a factory to create appropriate subclasses or whatever to get the right set of functions executed.