Re: IDENTIFY_SYSTEM - Mailing list pgsql-sql

From bricklen
Subject Re: IDENTIFY_SYSTEM
Date
Msg-id CAGrpgQ_b8smdAU3sjRrxfTWMtK0EnukQc2LCP71Pd4m=PC26xg@mail.gmail.com
Whole thread Raw
In response to Re: IDENTIFY_SYSTEM  (Glyn Astill <glynastill@yahoo.co.uk>)
List pgsql-sql

On Wed, Feb 5, 2014 at 6:51 AM, Glyn Astill <glynastill@yahoo.co.uk> wrote:
>
> I don't think so no, but you may have better luck finding someone more
> knowledgable posting to pgsql-general.  You could do it by calling
> pg_controldata via an untrusted procedural language, not so sure how happy
> I'd be with that myself.  E.g. with plperlu:
>
> CREATE OR REPLACE FUNCTION get_system_identifier_unsafe(text)
> RETURNS text AS
> $BODY$
>     my $rv;
>     my $data;
>     my $pg_controldata_bin = $_[0];
>     my $sysid;
>    
>     $rv = spi_exec_query('SHOW data_directory', 1);
>     $data = $rv->{rows}[0]->{data_directory};
>    
>     open(FD,"$pg_controldata_bin $data | ");
>    
>     while(<FD>) {
>         if (/Database system identifier:/) {
>             $sysid = $_;
>             for ($sysid) {
>                 s/Database system identifier://;
>                 s/[^0-9]//g;
>             }
>             last;
>         }
>     }
>     close (FD);
>     return $sysid;   
>
> $BODY$
> LANGUAGE plperlu;
>
>

So if I actually ran that:

test=# select get_system_identifier_unsafe('pg_controldata');
 get_system_identifier_unsafe
------------------------------
 5667443312440565226


Joe Conway wrote something a few years ago which could probably be brought up to date and made into a Postgresql extension. https://github.com/jconway/pg_controldata

pgsql-sql by date:

Previous
From: Glyn Astill
Date:
Subject: Re: IDENTIFY_SYSTEM
Next
From: rawi
Date:
Subject: Time AT TIME ZONE: false result using offset instead of time zone name