Re: IDENTIFY_SYSTEM

On Wed, Feb 5, 2014 at 6:51 AM, Glyn Astill <glynastill(at)yahoo(dot)co(dot)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