From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-admin@postgresql.org
Subject: Re: Querying the hostname of the server
Date: Fri, 30 Apr 2010 17:01:26 +0200
User-Agent: KMail/1.9.7
References: <292551.66852.qm@web23604.mail.ird.yahoo.com>
In-Reply-To: <292551.66852.qm@web23604.mail.ird.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <201004301801.26710.achill@matrix.gatewaynet.com>

Yes, nice and simple.

I just did this in C and it works ok!

hostname.c
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

#include <unistd.h>
#include "postgres.h"
#include "utils/elog.h"
#include "utils/palloc.h"
#include "storage/bufpage.h"
#define MAX_HOST_SIZE   200

PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(hostname);
Datum hostname(PG_FUNCTION_ARGS);

Datum
hostname(PG_FUNCTION_ARGS)
{
        int len;
        char buf[MAX_HOST_SIZE + 1];
        text *result;

        gethostname(buf,MAX_HOST_SIZE);
        len =3D strlen(buf);
        //elog(NOTICE, "hostname=3D%s\n",buf);
        result=3D(text *)palloc(len + VARHDRSZ);
        SET_VARSIZE(result, len + VARHDRSZ);
        memcpy(VARDATA(result),buf,strlen(buf));
        PG_RETURN_POINTER(result);
}


Makefile
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
MODULE_big =3D hostname
OBJS =3D hostname.o

ifdef USE_PGXS
PG_CONFIG =3D pg_config
PGXS :=3D $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
else
subdir =3D /var/lib/pgsql/src/Ccode/hostname
top_builddir =3D /usr/local/src/postgresql-8.3.3
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif


hostname.sql
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
CREATE OR REPLACE FUNCTION hostname() RETURNS text
    AS '$libdir/hostname', 'hostname'
    LANGUAGE c IMMUTABLE STRICT;


=CE=A3=CF=84=CE=B9=CF=82 Friday 30 April 2010 17:43:49 =CE=BF/=CE=B7 Glyn A=
still =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
> --- On Fri, 30/4/10, Ross J. Reedstrom <reedstrm@rice.edu> wrote:
>=20
> > > Hi,
> > >=20
> > > I have a number of PostgreSQL servers which I often
> > access through ssh
> > > tunnel with Pgadmin3. I would like to double check
> > which one I have landed
> > > on (if the tunnel is really configured the way I
> > want). Is there a way to
> > > query the hostname from the catalogs?
> >=20
> > Hmm, that's a bit tricky, since I assume you're using a
> > local db
> > connection inside the tunnel, so inet_server_addr()
> > probably returns
> > null. If you're talking unix/linux machines, then
> > /etc/hostname _should_
> > have the current hostname in it, so:
> >=20
> > create temp table foo (t text);
> > copy foo from '/etc/hostname';
> > select * from foo;
> > drop table foo;
> >=20
> > Should work.
> >=20
>=20
> Or you could do something like:
>=20
> CREATE OR REPLACE FUNCTION hostname()
> RETURNS text AS
> $BODY$
> 	$host =3D `hostname`;
>         return $host;
> $BODY$
> LANGUAGE 'plperlu';
>=20
>=20
>=20
>=20
>=20
>=20


=2D-=20
Achilleas Mantzios