Thread: 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@kineticode.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@kineticode.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@kineticode.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@kineticode.com> writes:
> On Oct 11, 2008, at 19:57, Tom Lane wrote:
>> "David E. Wheeler" <david@kineticode.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@kineticode.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@2ndQuadrant.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