gem-hunter

TL;DR Example

--------------------------------------------------------
-- get the name, pid and attached port of all processes
-- which are listening on localhost interfaces
--------------------------------------------------------
SELECT DISTINCT
    process.name,
    listening.port,
    process.pid
FROM processes AS process
JOIN listening_ports AS listening
ON process.pid = listening.pid
WHERE listening.address = '127.0.0.1';

   name   | port | pid
----------+------+------
 postgres | 5432 | 6932

(1 row)

About

Installation

git clone https://github.com/shish/pgosquery.git
cd pgosquery
sudo apt-get install pgxnclient
sudo pgxn install multicorn
sudo pip install psutil
sudo python setup.py develop
psql -h localhost -U postgres -w < queries.sql

$ sudo python setup.py develop

CREATE DATABASE pgosquery;
\c pgosquery;

CREATE EXTENSION multicorn;

CREATE SERVER pgosquery_srv foreign data wrapper multicorn options (
    wrapper 'pgosquery.PgOSQuery'
);

CREATE FOREIGN TABLE processes (
    pid integer,
    name character varying,
	username character varying
) server pgosquery_srv options (
    tabletype 'processes'
);

CREATE FOREIGN TABLE listening_ports (
    pid integer,
    address character varying,
	port integer
) server pgosquery_srv options (
    tabletype 'listening_ports'
);

--------------------------------------------------------
-- get the name, pid and attached port of all processes
-- which are listening on all interfaces
--------------------------------------------------------
SELECT DISTINCT
    process.name,
    listening.port,
    process.pid
FROM processes AS process
JOIN listening_ports AS listening
ON process.pid = listening.pid
WHERE listening.address = '127.0.0.1';

   name   | port | pid
----------+------+------
 postgres | 5432 | 6932

(1 row)

Table Types