How to include PostgreSQL-specific code within portable SQL?

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 */ INT开发者_JS百科O 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.

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."

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.

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.

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.