SELECT hostbyname('www.google.com'); in order to resolve that hostname? Then pg-dns-resolve is the right thing for you. pg-dns-resolve contains PL/Python functions for DNS resolution at the SQL prompt. Check out these examples:For all of the functions there is a variant ending in "_n" which means that on error, NULL is to be returned instead of an error string describing the cause of the error. Some functions have a "_s"-version which means they return the result as a set, i.e. multiple rows.
Here we go. Resolve the hostname for a given IP address:
db=# select dst, hostbyaddr(dst) from dns_per_ip limit 2;
dst | hostbyaddr
---------------+-----------------------------
192.168.1.1 | (1, 'Unbekannter Rechner')
193.232.128.6 | ns5.msk-ix.net
(2 rows)
Forward resolve www.google.de to (one of) its IP address:
db=# select hostbyname('www.google.de');
hostbyname
---------------
74.125.43.105
(1 row)
Note that on error, NULL is returned by hostbyname_n, BUT hostbyname returns an error string instead. So if you want to know why the resolution failed, use hostbyname, otherwise use hostbyname_n.
db=# select hostbyname_n('nonexisting'), hostbyname('nonexisting');
hostbyname_n | hostbyname
---------------+----------------------------------------------------
| (-2, 'Der Name oder der Dienst ist nicht bekannt')
(1 row)
db=# select hostbyname_n('nonexistinghost') is NULL;
?column?
----------
true
(1 row)
If you need all IP addresses of a hostname, use addrsbyname. DNS usually returns a different order of multiple IP addresses due to round-robin. Note, that the list of IP addresses of addrsbyname is sorted, thus two executions with the same argument return the same list. This is very useful for comparisons.
db=# select addrsbyname('www.google.de');
addrsbyname
-------------------
74.125.43.103
74.125.43.104
74.125.43.105
74.125.43.106
74.125.43.147
74.125.43.99
(1 row)
If you want e.g. a comma-separated list instead of newline-separated list, use your own separator string as the second argument to addrsbyname:
db=# select addrsbyname('www.google.de', ', ');
addrsbyname
-----------------------------------------------------------------------------------------
74.125.43.103, 74.125.43.104, 74.125.43.105, 74.125.43.106, 74.125.43.147, 74.125.43.99
(1 row)
hostsbyname works similar to addrsbyname. hostsbyname returns a list of all hostnames associated with a given hostname, including aliases. As with addrsbyname there are 2 variants, one using the default newline delimiter to separate elements and one where you can specify the delimiter yourself. The list of resulting hostnames is sorted.
db=# select hostsbyname('www.google.de', ', ');
hostsbyname
-------------------------------------------------
www.google.com, www.google.de, www.l.google.com
(1 row)
When working with sets, there are 4 interesting functions: addrsbyname_s and addrsbyname_ns as well as hostsbyname_s and hostsbyname_ns. Those return a set, i.e. multiple rows, instead of an aggregated string and they are useful when working with statements such as
SELECT ...
FROM ...
WHERE xxx IN ( SELECT addrsbyname_ns('www.google.com') )
db=# SELECT addrsbyname_s('www.google.com');
addrsbyname_s
---------------
74.125.43.103
74.125.43.104
74.125.43.105
74.125.43.106
74.125.43.147
74.125.43.99
(6 rows)
Note the subtle difference: 6 rows instead of 1 row when comparing the output of addrsbyname_s to that of addrsbyname.
db=# SELECT '74.125.43.103'::ip4 IN ( SELECT addrsbyname_s('www.google.com') );
?column?
----------
t
(1 row)
db=# SELECT hostsbyname_ns('www.google.com');
hostsbyname_ns
------------------
www.google.com
www.l.google.com
(2 rows)
Query a non existing hostname with the "_ns"-variant and the result will be an empty set (0 rows):
db=# SELECT hostsbyname_ns('nonexistinghost');
hostsbyname_ns
----------------
(0 rows)
A special case is forward-confirmed reverse DNS resolution (http://en.wikipedia.org/wiki/Forward-confirmed_reverse_DNS):
db=# SELECT fcrdns('192.203.230.10');
fcrdns
--------
f
(1 row)
db=# SELECT fcrdns('74.125.43.104');
fcrdns
--------
t
(1 row)
Like it? It's free, download it here: http://pgfoundry.org/projects/pgdnsres/. Alternatively, grab a version from http://www.cj2s.de/plpython_dns-functions.sql.
Installation is easy:
- Make sure, you have ip4r installed. Get it from: http://pgfoundry.org/projects/ip4r/
- Make sure, you have PL/Python installed and are allowed to add new functions
- psql [YOUR OPTIONS] < plpython_dns-functions.sql
