PostgreSQL execute statement conditionally by server version

I'm currently writing some installer script that fires SQL files against different database types depending on the system's configuration (the webapplication supports multiple database server like MySQL, MSSQL and PostgreSQL).

One of those types is PostgreSQL. I'm not fluent with it and I would like to know if it's possible to make a statement into a define/populate SQL file that makes an SQL query conditional to a specific PostgreSQL serve开发者_开发技巧r version.

How to make an SQL statement conditionally in plain PGSQL so that it is only executed in version 9? The command is:

ALTER DATABASE dbname SET bytea_output='escape';

The version check is to compare the version with 9.

Postgres does have version() function, however there is no major_vesion(). Assuming that output string always includes version number as number(s).number(s).number(s) you could write your own wrapper as:

CREATE OR REPLACE FUNCTION major_version() RETURNS smallint
AS $BODY$ 
    SELECT substring(version() from $$(\d+)\.\d+\.\d+$$)::smallint;
$BODY$ LANGUAGE SQL;

Example:

=> Select major_version();
 major_version
---------------
             9
(1 row)

However real issue here is that AFAIK you can't execute your commands conditionally in "pure" SQL and best what you can do is to write some stored function like this:

CREATE OR REPLACE FUNCTION conditionalInvoke() RETURNS void
AS $BODY$
BEGIN
    IF major_version() = 9 THEN
        ALTER DATABASE postgres SET bytea_output='escape';
    END IF;
    RETURN;
END;
$BODY$ LANGUAGE plpgsql;

I think that you should rather use some scripting language and generate appropriate SQL with it.

Or you could just use

select setting from pg_settings where name = 'server_version'

Or

select setting from pg_settings where name = 'server_version_num'

If you need major version only

select Substr(setting, 1, 1) from pg_settings where name = 'server_version_num'

or

select Substr(setting, 1, strpos(setting, '.')-1) from pg_settings where name = 'server_version'

if you want it to be compatible with two digit versions.

Maybe you could make things dependent on the output of select version(); (probably you'll have to trim and substring that a bit)

BTW (some) DDL statements may not be issued from within functions; maybe you'll have to escape to shell-programming and here-documents.