README.md
1`uri` type for PostgreSQL
2=========================
3
4https://twitter.com/pvh/status/567395527357001728
5
6This is an extension for PostgreSQL that provides a `uri` data type.
7Advantages over using plain `text` for storing URIs include:
8
9- URI syntax checking
10- functions for extracting URI components
11- human-friendly sorting
12
13The actual URI parsing is provided by the
14[uriparser](http://uriparser.sourceforge.net/) library, which supports
15URI syntax as per [RFC 3986](http://tools.ietf.org/html/rfc3986).
16
17Note that this might not be the right data type to use if you want to
18store user-provided URI data, such as HTTP referrers, since they might
19contain arbitrary junk.
20
21Installation
22------------
23
24You need to have the above-mentioned `uriparser` library installed.
25It is included in many operating system distributions and package
26management systems. `pkg-config` will be used to find it. I
27recommend at least version 0.8.0. Older versions will also work, but
28they apparently contain some bugs and might fail to correctly accept
29or reject URI syntax corner cases. This is mainly a problem if your
30application needs to be robust against junk input.
31
32To build and install this module:
33
34 make
35 make install
36
37or selecting a specific PostgreSQL installation:
38
39 make PG_CONFIG=/some/where/bin/pg_config
40 make PG_CONFIG=/some/where/bin/pg_config install
41
42And finally inside the database:
43
44 CREATE EXTENSION uri;
45
46Using
47-----
48
49This module provides a data type `uri` that you can use like a normal
50type. For example:
51
52```sql
53CREATE TABLE links (
54 id int PRIMARY KEY,
55 link uri
56);
57
58INSERT INTO links VALUES (1, 'https://github.com/petere/pguri');
59```
60
61A number of functions are provided to extract parts of a URI:
62
63- `uri_scheme(uri) returns text`
64
65 Extracts the scheme of a URI, for example `http` or `ftp` or
66 `mailto`.
67
68- `uri_userinfo(uri) returns text`
69
70 Extracts the user info part of a URI. This is normally a user
71 name, but could also be of the form `username:password`. If the
72 URI does not contain a user info part, then this will return null.
73
74- `uri_host(uri) returns text`
75
76 Extracts the host of a URI, for example `www.example.com` or
77 `192.168.0.1`. (For IPv6 addresses, the brackets are not included
78 here.) If there is no host, the return value is null.
79
80- `uri_host_inet(uri) returns inet`
81
82 If the host is a raw IP address, then this will return it as an
83 `inet` datum. Otherwise (not an IP address or no host at all),
84 the return value is null.
85
86- `uri_port(uri) returns integer`
87
88 Extracts the port of a URI as an integer, for example `5432`. If
89 no port is specified, the return value is null.
90
91- `uri_path(uri) returns text`
92
93 Extracts the path component of a URI. Logically, a URI always
94 contains a path. The return value can be an empty string but
95 never null.
96
97- `uri_path_array(uri) returns text[]`
98
99 Returns the path component of a URI as an array, with the path
100 split at the slash characters. This is probably not as useful as
101 the `uri_path` function, but it is provided here because the
102 `uriparser` library exposes it.
103
104- `uri_query(uri) returns text`
105
106 Extracts the query part of a URI (roughly speaking, everything
107 after the `?`). If there is no query part, returns null.
108
109- `uri_fragment(uri) returns text`
110
111 Extracts the fragment part of a URI (roughly speaking, everything
112 after the `#`). If there is no fragment part, returns null.
113
114Other functions:
115
116- `uri_normalize(uri) returns uri`
117
118 Performs syntax-based normalization of the URI. This includes
119 case normalization, percent-encoding normalization, and removing
120 redundant `.` and `..` path segments. See
121 [RFC 3986 section 6.2.2](http://tools.ietf.org/html/rfc3986#section-6.2.2)
122 for the full details.
123
124 Note that this module (and similar modules in other programming
125 languages) compares URIs for equality in their original form,
126 without normalization. If you want to consider distinct URIs
127 without regard for mostly irrelevant syntax differences, pass them
128 through this function.
129
130- `uri_escape(text, space_to_plus boolean DEFAULT false, normalize_breaks boolean DEFAULT false) returns text`
131
132 Percent-encodes all reserved characters from the text. This can
133 be useful for constructing URIs from strings.
134
135 If `space_to_plus` is true, then spaces are replaced by plus
136 signs. If `normalize_breaks` is true, then line breaks are
137 converted to CR LF pairs (and subsequently percent-encoded). Note
138 that these two conversions come from the HTML standard for
139 encoding form data but are not part of the specification for URIs.
140
141- `uri_unescape(text, plus_to_space boolean DEFAULT false, break_conversion boolean DEFAULT false) returns text`
142
143 Decodes all percent-encodings in the text.
144
145 If `plus_to_space` is true, then plus signs are converted to
146 spaces. If `break_conversion` is true, then CR LF pairs are
147 converted to simple newlines (`\n`).
148