Thank you for visiting this page, this page has been update in another link PostgreSQL DNS resolution functions
I have huge number of ip stored in posrgresql, but seldom try to resolve their hostnames in a big batch, today, I tried a big batch, I realized I need to find whether there is a function in postgres. Below is partial of the content from the link, in case the link disappears some day. I hope not. thanks to Christian J. Dietrich Particularly, linux host command gives you reversed ip order. # host 173.194.33.7878.33.194.173.in-addr.arpa domain name pointer sea09s15-in-f14.1e100.net.I got a good luck on google, I got this, it's simple, but very useful, ha, good day. http://blog.cj2s.de/archives/11-DNS-resolution-functions-for-PostgreSQL.html DNS resolution functions for PostgreSQLYou are working with lots of IP addresses in a PostgreSQL database? Ever wanted to quickly find out the hostname for a given IP address? Issue something likeSELECT 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 |