PostgreSQL execute statement conditionally by server version

Question

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

ertx · Accepted Answer · 2011-09-02 16:16:23Z

5

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.

answered Sep 2, 2011 at 16:16

ertx

1,5442 gold badges15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Grzegorz Szpetkowski Over a year ago

+1 for smart use of server_version and server_version_num preset options :)

isaaclw Over a year ago

I'm worried about the 'two digit version' you included. Is it not stripping the zeros? what if the version is 91000 (9.10.0), how do you account for that?

Grzegorz Szpetkowski · Accepted Answer · 2011-09-02 15:14:43Z

4

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.

answered Sep 2, 2011 at 15:14

Grzegorz Szpetkowski

38.2k6 gold badges94 silver badges140 bronze badges

3 Comments

hakre Over a year ago

Thanks for the insight. We're acutally using PHP and execute the statement conditionally. I thought it would be easier to do in PGSQL directly so I could reduce the PHP installer script, but well, I think you've answered my question and well, it was a dream of mine :)

Peter Eisentraut Over a year ago

This won't always work: I have 9.1rc1 and 9.2devel around at the moment. ;-)

isaaclw Over a year ago

I'm trying to decide what the most stable would be, maybe something like this?: SELECT substring(version() from $$PostgreSQL (\d+)\.\d+$$);

wildplasser · Accepted Answer · 2011-09-02 11:14:43Z

0

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.

answered Sep 2, 2011 at 11:14

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Collectives™ on Stack Overflow

PostgreSQL execute statement conditionally by server version

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related