i am trying to write a SQL function that determines if the Postgres Server is running on windows, is that possible, or is there a way to get the platform of a running Server?
3 Answers
You can simply use select version()
I tried it on an Linux Alpine server (through a Docker container) and got this:
test=> select version();
version
-------------------------------------------------------------------------------------------------
PostgreSQL 9.6.8 on x86_64-pc-linux-musl, compiled by gcc (Alpine 6.2.1) 6.2.1 20160822, 64-bit
(1 row)
You can have more informations on postgres system informations functions here https://www.postgresql.org/docs/current/functions-info.html
Hope it'll help you !
1 Comment
ZORRO_BLANCO
I know the version() function and this is what I thought too, i wanted something more specific without parsing, but thanks for the answer
This is the code I wroteand used at the end, it works great on Postgres 11 version:
CREATE OR REPLACE FUNCTION GetPLatform()
RETURNS varchar
AS
$$
declare platform varchar;
begin
SELECT CASE
WHEN OSVersion.OS LIKE '%w64%' THEN 'windows'
WHEN OSVersion.OS LIKE '%w32%' THEN 'windows'
WHEN OSVersion.OS LIKE '%mingw%' THEN 'windows'
WHEN OSVersion.OS LIKE '%visual studio%' THEN 'windows'
WHEN OSVersion.OS LIKE '%linux%' THEN 'linux'
WHEN OSVersion.OS LIKE '%mac%' THEN 'mac'
ELSE
'UNKNOWN'
END into platform
FROM (SELECT
substr(substr(version(), strpos(version(), ' on ')+3), 1,
strpos(substr(version(), strpos(version(), ' on ')+3),
', compiled by')-1) as OS)
as OSVersion;
return platform;
end;
$$
LANGUAGE PLPGSQL;
select GetPLatform()
You can adjust it as you want for other platforms...
1 Comment
MetaData
I did this using your code select case when version() like '%linux%' then 'linux' else 'windows' end as flag
Because using mmap is very rare, you can use :
SELECT CASE setting
WHEN 'windows' THEN 'WINDOWS family'
WHEN 'posix' THEN 'UNIX family'
WHEN 'sysv' THEN 'UNIX family'
ELSE 'UNKNOWN'
END AS OS_family
FROM pg_catalog.pg_file_settings
WHERE name = 'dynamic_shared_memory_type'
Another solution could be :
SELECT CASE setting
WHEN '0700' THEN 'Windows'
ELSE 'UNIX'
END AS OS_FAMILLY
FROM pg_settings
WHERE name = 'data_directory_mode'
To show the familly of OS between UNIX or Windows...
select version()will include that information (although a bit hard to parse)