Re: New variable server_version_num

On Sat, Jul 29, 2006 at 09:14:16PM -0400, Greg Sabino Mullane wrote:
> Today on IRC David Fetter and some others were discussing version
> numbers and we realized that although libpq now provides the version of
> Postgres as a number, this is still a wheel that is being reinvented by
> apps many times over, as it is not available any other way. Hence, a
> small patch to provide a new variable "server_version_num", which is
> almost the same as "server_version" but uses the handy PG_VERSION_NUM
> which allows apps to do things like if ($version >= 80200) without
> having to parse apart the value of server_version themselves.

Here's an SQL function which does the same thing.  I've had it tested
back through 7.4x, and it should work back to the 7.3 series, although
I haven't tested it there.  Thanks to Andrew of Supernews for the
short version :)

SELECT
    sum(
        substring(
            split_part(
                current_setting(
                    'server_version'
                ),
                '.'
            ,
            i
        )
        FROM
        '^[[:digit:]]+'
    )::integer * 10^(6-i*2) ) AS server_version_integer
FROM (
    SELECT 1
UNION ALL
    SELECT 2
UNION ALL
    SELECT 3
) AS s(i);

Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!