Repository files navigation

https://twitter.com/pvh/status/567395527357001728

This is an extension for PostgreSQL that provides auri data type.Advantages over using plaintext for storing URIs include:

• URI syntax checking
• functions for extracting URI components
• human-friendly sorting

The actual URI parsing is provided by theuriparser library, which supportsURI syntax as perRFC 3986.

Note that this might not be the right data type to use if you want tostore user-provided URI data, such as HTTP referrers, since they mightcontain arbitrary junk.

You need to have the above-mentioneduriparser library installed.It is included in many operating system distributions and packagemanagement systems.pkg-config will be used to find it. Irecommend at least version 0.8.0. Older versions will also work, butthey apparently contain some bugs and might fail to correctly acceptor reject URI syntax corner cases. This is mainly a problem if yourapplication needs to be robust against junk input.

To build and install this module:

makemake install

or selecting a specific PostgreSQL installation:

make PG_CONFIG=/some/where/bin/pg_configmake PG_CONFIG=/some/where/bin/pg_config install

And finally inside the database:

CREATE EXTENSION uri;

This module provides a data typeuri that you can use like a normaltype. For example:

CREATETABLElinks (    idintPRIMARY KEY,    link uri);INSERT INTO linksVALUES (1,'https://github.com/petere/pguri');

A number of functions are provided to extract parts of a URI:

• uri_scheme(uri) returns text

  Extracts the scheme of a URI, for examplehttp orftp ormailto.

• uri_userinfo(uri) returns text

  Extracts the user info part of a URI. This is normally a username, but could also be of the formusername:password. If theURI does not contain a user info part, then this will return null.

• uri_host(uri) returns text

  Extracts the host of a URI, for examplewww.example.com or192.168.0.1. (For IPv6 addresses, the brackets are not includedhere.) If there is no host, the return value is null.

• uri_host_inet(uri) returns inet

  If the host is a raw IP address, then this will return it as aninet datum. Otherwise (not an IP address or no host at all),the return value is null.

• uri_port(uri) returns integer

  Extracts the port of a URI as an integer, for example5432. Ifno port is specified, the return value is null.

• uri_path(uri) returns text

  Extracts the path component of a URI. Logically, a URI alwayscontains a path. The return value can be an empty string butnever null.

• uri_path_array(uri) returns text[]

  Returns the path component of a URI as an array, with the pathsplit at the slash characters. This is probably not as useful astheuri_path function, but it is provided here because theuriparser library exposes it.

• uri_query(uri) returns text

  Extracts the query part of a URI (roughly speaking, everythingafter the?). If there is no query part, returns null.

• uri_fragment(uri) returns text

  Extracts the fragment part of a URI (roughly speaking, everythingafter the#). If there is no fragment part, returns null.

Other functions:

• uri_normalize(uri) returns uri

  Performs syntax-based normalization of the URI. This includescase normalization, percent-encoding normalization, and removingredundant. and.. path segments. SeeRFC 3986 section 6.2.2for the full details.

  Note that this module (and similar modules in other programminglanguages) compares URIs for equality in their original form,without normalization. If you want to consider distinct URIswithout regard for mostly irrelevant syntax differences, pass themthrough this function.

• uri_escape(text, space_to_plus boolean DEFAULT false, normalize_breaks boolean DEFAULT false) returns text

  Percent-encodes all reserved characters from the text. This canbe useful for constructing URIs from strings.

  Ifspace_to_plus is true, then spaces are replaced by plussigns. Ifnormalize_breaks is true, then line breaks areconverted to CR LF pairs (and subsequently percent-encoded). Notethat these two conversions come from the HTML standard forencoding form data but are not part of the specification for URIs.

• uri_unescape(text, plus_to_space boolean DEFAULT false, break_conversion boolean DEFAULT false) returns text

  Decodes all percent-encodings in the text.

  Ifplus_to_space is true, then plus signs are converted tospaces. Ifbreak_conversion is true, then CR LF pairs areconverted to simple newlines (\n).