----- Original Message -----
> From: Cezariusz Marek <cezariusz.marek@comarch.pl>
> To: pgsql-sql@postgresql.org
> Cc:
> Sent: Wednesday, 5 February 2014, 12:01
> Subject: Re: [SQL] IDENTIFY_SYSTEM
>
>> That's part of the streaming replication protocol
>>
>> http://www.postgresql.org/docs/9.3/static/protocol-replication.html
>> As long as you're using wal_level >= archive and the replication
> connection is enabled you can retrieve it via psql
>
> Yes, I know, but there is no way to get the systemid value from a function using
> just SQL or plpgsql?
>
I don't think so no, but you may have better luck finding someone more knowledgable posting to pgsql-general. You
coulddo 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;
>> If it's just for licencing perhaps inet_server_addr() or a plperl
> function to grab the mac address of the machine might suffice?
>
> I have to license each database, not just the whole machine. And the systemid is
> the only unique database identifier I've found.
Is it each database or each postgresql instance / cluster? How exactly do you want your licencing to work? There may
bea better way.