Search code examples
sqlpostgresqlidn

count length of international domain name throw SQL (PostgreSQL)

I have several domain names saved in database in idn format ("xn--"). I want to run some statistics queries, but have problem to count characters on these domain names

SELECT
    dom_name,
    char_length(dom_name) as raw_length, -- counted with zone extension for now
FROM
    my_domains_table;

Ofcourse i have mistake about "xn--" domains, and i wanted to get count from sql (not using php further or some other language).

Or, please, give me some advice how to do it better.

- In first save domain names into table in UTF-8 would be great, but this is not an option right now :)

Solution

  • If you can install untrusted languages to your database, like PL/PerlU or PL/PythonU, then you can create punycode_decode function using Net::IDN::Encode perl module or decode('idna') python string class member function.

    If you don't, then you'd need to implement it using pl/pgsql, which won't be easy.