Version Number Function?

Howdy,

Any interest in adding a function like this to core?

Datum
pg_version(PG_FUNCTION_ARGS)
{
PG_RETURN_INT32(PG_VERSION_NUM);
}

That returns an integer, such as

try=# select pg_version();
pg_version
------------
80304
(1 row)

I've whipped this up for pgtap, as it'll be useful for determing when
to skip tests based on a version of PostgreSQL, but I thought it might
be generally useful enough to add to core.

Thoughts?

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> Any interest in adding a function like this to core?

No, because it's already there: see "show server_version_num".

(It's probably worth noting that none of our existing clients that would
have any use for this information look at server_version_num, because
it's only available in 8.2 and up. A function introduced as of 8.4
would be an additional two major releases behind the curve.)

regards, tom lane

On Oct 11, 2008, at 19:57, Tom Lane wrote:

> "David E. Wheeler" <david(at)kineticode(dot)com> writes:
>> Any interest in adding a function like this to core?
>
> No, because it's already there: see "show server_version_num".
>
> (It's probably worth noting that none of our existing clients that
> would
> have any use for this information look at server_version_num, because
> it's only available in 8.2 and up. A function introduced as of 8.4
> would be an additional two major releases behind the curve.)

Yeah, but I want to use it in WHERE clauses or CASE statements. I'm
fine to just include it in pgtap, though.

Best,

David

David E. Wheeler wrote:
> On Oct 11, 2008, at 19:57, Tom Lane wrote:
>
>> "David E. Wheeler" <david(at)kineticode(dot)com> writes:
>>> Any interest in adding a function like this to core?
>>
>> No, because it's already there: see "show server_version_num".
>>
>> (It's probably worth noting that none of our existing clients that would
>> have any use for this information look at server_version_num, because
>> it's only available in 8.2 and up. A function introduced as of 8.4
>> would be an additional two major releases behind the curve.)
>
> Yeah, but I want to use it in WHERE clauses or CASE statements. I'm fine
> to just include it in pgtap, though.

You could do:
select setting from pg_settings where name='server_version_num';

(wrapped in the appropriate subselect to use it in a WHERE clause)

//Magnus

On Oct 12, 2008, at 11:21, Magnus Hagander wrote:

>> Yeah, but I want to use it in WHERE clauses or CASE statements. I'm
>> fine
>> to just include it in pgtap, though.
>
> You could do:
> select setting from pg_settings where name='server_version_num';
>
> (wrapped in the appropriate subselect to use it in a WHERE clause)

Right, but I want to make it as simple as possible for test writers to
use.

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Oct 11, 2008, at 19:57, Tom Lane wrote:
>> "David E. Wheeler" <david(at)kineticode(dot)com> writes:
>>> Any interest in adding a function like this to core?
>>
>> No, because it's already there: see "show server_version_num".

> Yeah, but I want to use it in WHERE clauses or CASE statements.

current_setting('server_version_num')

regards, tom lane

On Oct 12, 2008, at 12:42, Tom Lane wrote:

>> Yeah, but I want to use it in WHERE clauses or CASE statements.
>
> current_setting('server_version_num')

Hrm. That's nice. I don't suppose there's any way to get something
like that in 8.1 and earlier? I was going to fake it in the .c file.

Thanks,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Oct 12, 2008, at 12:42, Tom Lane wrote:
>> current_setting('server_version_num')

> Hrm. That's nice. I don't suppose there's any way to get something
> like that in 8.1 and earlier? I was going to fake it in the .c file.

You'd have to parse the result of version().

regards, tom lane

On Oct 12, 2008, at 14:11, Tom Lane wrote:

> You'd have to parse the result of version().

As I figured. This is what I'm trying:

pg_version_num(PG_FUNCTION_ARGS)
{
#ifdef PG_VERSION_NUM
PG_RETURN_INT32(PG_VERSION_NUM);
#else
/* Code borrowed from dumputils.c. */
int cnt;
int vmaj,
vmin,
vrev;

cnt = sscanf(PG_VERSION, "%d.%d.%d", &vmaj, &vmin, &vrev);

if (cnt < 2)
return -1;

if (cnt == 2)
vrev = 0;

PG_RETURN_INT32( (100 * vmaj + vmin) * 100 + vrev );
#endif

Best,

David

On Sun, 2008-10-12 at 14:39 -0700, David E. Wheeler wrote:
> On Oct 12, 2008, at 14:11, Tom Lane wrote:
>
> > You'd have to parse the result of version().
>
> As I figured. This is what I'm trying:

if performance is not critical, then you could use this:

hannu=# create or replace function pg_version_num() returns int language
SQL as $$
select
10000 *
cast(substring(version()
from
'^PostgreSQL +([0-9]+)[.][0-9]+[.][0-9]+ +') as int)
+
100 *
cast(substring(version()
from
'^PostgreSQL +[0-9]+[.]([0-9]+)[.][0-9]+ +') as int)
+
cast(substring(version()
from
'^PostgreSQL +[0-9]+[.][0-9]+[.]([0-9]+) +') as int);
$$;
CREATE FUNCTION

hannu=# select pg_version_num();
pg_version_num
----------------
80303
(1 row)

> pg_version_num(PG_FUNCTION_ARGS)
> {
> #ifdef PG_VERSION_NUM
> PG_RETURN_INT32(PG_VERSION_NUM);
> #else
> /* Code borrowed from dumputils.c. */
> int cnt;
> int vmaj,
> vmin,
> vrev;
>
> cnt = sscanf(PG_VERSION, "%d.%d.%d", &vmaj, &vmin, &vrev);
>
> if (cnt < 2)
> return -1;
>
> if (cnt == 2)
> vrev = 0;
>
> PG_RETURN_INT32( (100 * vmaj + vmin) * 100 + vrev );
> #endif
>
> Best,
>
> David
>
>

Well, the C version I borrowed from dumpitils seems to work great. Any
reason I shouldn't stay with that?

Best,

David

Sent from my iPhone

On Oct 14, 2008, at 7:44, Hannu Krosing <hannu(at)2ndQuadrant(dot)com> wrote:

> On Sun, 2008-10-12 at 14:39 -0700, David E. Wheeler wrote:
>> On Oct 12, 2008, at 14:11, Tom Lane wrote:
>>
>>> You'd have to parse the result of version().
>>
>> As I figured. This is what I'm trying:
>
> if performance is not critical, then you could use this:
>
> hannu=# create or replace function pg_version_num() returns int
> language
> SQL as $$
> select
> 10000 *
> cast(substring(version()
> from
> '^PostgreSQL +([0-9]+)[.][0-9]+[.][0-9]+ +') as int)
> +
> 100 *
> cast(substring(version()
> from
> '^PostgreSQL +[0-9]+[.]([0-9]+)[.][0-9]+ +') as int)
> +
> cast(substring(version()
> from
> '^PostgreSQL +[0-9]+[.][0-9]+[.]([0-9]+) +') as int);
> $$;
> CREATE FUNCTION
>
> hannu=# select pg_version_num();
> pg_version_num
> ----------------
> 80303
> (1 row)
>
>> pg_version_num(PG_FUNCTION_ARGS)
>> {
>> #ifdef PG_VERSION_NUM
>> PG_RETURN_INT32(PG_VERSION_NUM);
>> #else
>> /* Code borrowed from dumputils.c. */
>> int cnt;
>> int vmaj,
>> vmin,
>> vrev;
>>
>> cnt = sscanf(PG_VERSION, "%d.%d.%d", &vmaj, &vmin, &vrev);
>>
>> if (cnt < 2)
>> return -1;
>>
>> if (cnt == 2)
>> vrev = 0;
>>
>> PG_RETURN_INT32( (100 * vmaj + vmin) * 100 + vrev );
>> #endif
>>
>> Best,
>>
>> David
>>
>>
>

On Oct 14, 2008, at 08:33, David E. Wheeler wrote:

> Well, the C version I borrowed from dumpitils seems to work great.
> Any reason I shouldn't stay with that?

Also, here's a simpler SQL version, for those following along at home:

create or replace function pg_version_num() returns int language
SQL as $$
SELECT SUM(
(string_to_array(current_setting('server_version'), '.'))
[i]::int
* CASE i WHEN 1 THEN 10000 WHEN 2 THEN 100 ELSE 1 end
)::int FROM generate_series(1, 3) AS gen(i);
$$;
CREATE FUNCTION

There must be a way to get string_to_array() to evaluate only once, yes?

Best,

David

> There must be a way to get string_to_array() to evaluate only once, yes?

WITH, but that's not going to help you for backward compatibility.

If you used plpgsql you could assign the string_to_array result to a
variable and then work off the variable, but I'm not sure that's
really better.

...Robert

On Tue, 2008-10-14 at 08:33 -0700, David E. Wheeler wrote:
> Well, the C version I borrowed from dumpitils seems to work great. Any
> reason I shouldn't stay with that?

SQL is the only "PL" available by default, no need to compile or install
anything.

It can be written more effectively in almost any other pl, and probably
in SQL as well ;)

---------------
Hannu

On Oct 14, 2008, at 14:32, Hannu Krosing wrote:

> On Tue, 2008-10-14 at 08:33 -0700, David E. Wheeler wrote:
>> Well, the C version I borrowed from dumpitils seems to work great.
>> Any
>> reason I shouldn't stay with that?
>
> SQL is the only "PL" available by default, no need to compile or
> install
> anything.
>
> It can be written more effectively in almost any other pl, and
> probably
> in SQL as well ;)

Yes, but I'm putting this into pgTAP, where I already have some C
functions I'm defining, so another won't hurt any. :-)

Thanks,

David

On Tue, 2008-10-14 at 09:53 -0700, David E. Wheeler wrote:
> On Oct 14, 2008, at 08:33, David E. Wheeler wrote:
>
> > Well, the C version I borrowed from dumpitils seems to work great.
> > Any reason I shouldn't stay with that?
>
> Also, here's a simpler SQL version, for those following along at home:
>
> create or replace function pg_version_num() returns int language
> SQL as $$
> SELECT SUM(
> (string_to_array(current_setting('server_version'), '.'))
> [i]::int
> * CASE i WHEN 1 THEN 10000 WHEN 2 THEN 100 ELSE 1 end
> )::int FROM generate_series(1, 3) AS gen(i);
> $$;
> CREATE FUNCTION

Was current_setting('server_version') available in 8.1 ?

> There must be a way to get string_to_array() to evaluate only once, yes?

SELECT s.a[1]::int * 10000 + s.a[2]::int * 100 + s.a[3]::int
FROM
(SELECT string_to_array(current_setting('server_version'), '.') AS a)
AS s;

----------------
Hannu

On Oct 14, 2008, at 14:50, Hannu Krosing wrote:

> Was current_setting('server_version') available in 8.1 ?

Yes. In 8.0, too.

>> There must be a way to get string_to_array() to evaluate only once,
>> yes?
>
> SELECT s.a[1]::int * 10000 + s.a[2]::int * 100 + s.a[3]::int
> FROM
> (SELECT string_to_array(current_setting('server_version'), '.') AS a)
> AS s;

Oh, duh. Even better!

Best,

David