Re: Version Number Function?

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