2015-02-18 04:23:18 +00:00
|
|
|
\pset null _null_
|
2015-03-13 04:11:45 +00:00
|
|
|
SET client_min_messages = warning;
|
2015-02-18 04:23:18 +00:00
|
|
|
CREATE TABLE test (a serial, b uri);
|
|
|
|
|
INSERT INTO test (b)
|
|
|
|
|
VALUES ('http://www.postgresql.org/'),
|
|
|
|
|
('http://www.postgresql.org/docs/devel/static/xfunc-sql.html#XFUNC-SQL-FUNCTION-ARGUMENTS'),
|
|
|
|
|
('https://duckduckgo.com/?q=postgresql&ia=about'),
|
|
|
|
|
('ftp://ftp.gnu.org/gnu/bison'),
|
|
|
|
|
('mailto:foo@example.com'),
|
|
|
|
|
('ssh://username@review.openstack.org:29418/openstack/nova.git'),
|
|
|
|
|
('http://admin:password@192.168.0.1'),
|
|
|
|
|
('http://[FEDC:BA98:7654:3210:FEDC:BA98:7654:3210]:80/index.html'),
|
|
|
|
|
('http://[1080::8:800:200C:417A]/foo'),
|
2015-02-19 03:10:05 +00:00
|
|
|
('http://host:'),
|
2015-02-18 04:23:18 +00:00
|
|
|
(''),
|
2015-04-04 02:49:52 +00:00
|
|
|
('/'),
|
|
|
|
|
('foobar'),
|
|
|
|
|
('/foobar');
|
2015-04-12 20:01:03 +00:00
|
|
|
-- normalization test values from <https://tools.ietf.org/html/rfc3986#section-6.2.2>
|
|
|
|
|
INSERT INTO test (b)
|
|
|
|
|
VALUES ('HTTP://www.EXAMPLE.com/'),
|
|
|
|
|
('http://www.ex%41mple.com/'),
|
|
|
|
|
('eXAMPLE://a/./b/../b/%63/%7bfoo%7d');
|
2015-02-18 04:23:18 +00:00
|
|
|
SELECT * FROM test;
|
|
|
|
|
a | b
|
|
|
|
|
----+-----------------------------------------------------------------------------------------
|
|
|
|
|
1 | http://www.postgresql.org/
|
|
|
|
|
2 | http://www.postgresql.org/docs/devel/static/xfunc-sql.html#XFUNC-SQL-FUNCTION-ARGUMENTS
|
|
|
|
|
3 | https://duckduckgo.com/?q=postgresql&ia=about
|
|
|
|
|
4 | ftp://ftp.gnu.org/gnu/bison
|
|
|
|
|
5 | mailto:foo@example.com
|
|
|
|
|
6 | ssh://username@review.openstack.org:29418/openstack/nova.git
|
|
|
|
|
7 | http://admin:password@192.168.0.1
|
|
|
|
|
8 | http://[FEDC:BA98:7654:3210:FEDC:BA98:7654:3210]:80/index.html
|
|
|
|
|
9 | http://[1080::8:800:200C:417A]/foo
|
2015-02-19 03:10:05 +00:00
|
|
|
10 | http://host:
|
|
|
|
|
11 |
|
2015-04-04 02:49:52 +00:00
|
|
|
12 | /
|
|
|
|
|
13 | foobar
|
|
|
|
|
14 | /foobar
|
2015-04-12 20:01:03 +00:00
|
|
|
15 | HTTP://www.EXAMPLE.com/
|
|
|
|
|
16 | http://www.ex%41mple.com/
|
|
|
|
|
17 | eXAMPLE://a/./b/../b/%63/%7bfoo%7d
|
|
|
|
|
(17 rows)
|
2015-02-18 04:23:18 +00:00
|
|
|
|
|
|
|
|
-- error cases
|
2015-02-19 03:10:05 +00:00
|
|
|
SELECT uri 'http://host:port/';
|
|
|
|
|
ERROR: invalid input syntax for type uri at or near "/"
|
|
|
|
|
LINE 1: SELECT uri 'http://host:port/';
|
|
|
|
|
^
|
2015-02-18 04:23:18 +00:00
|
|
|
\x on
|
|
|
|
|
SELECT b AS uri,
|
2015-04-12 20:01:03 +00:00
|
|
|
uri_normalize(b),
|
2015-02-18 04:23:18 +00:00
|
|
|
uri_scheme(b),
|
|
|
|
|
uri_userinfo(b),
|
|
|
|
|
uri_host(b),
|
|
|
|
|
uri_host_inet(b),
|
|
|
|
|
uri_port(b),
|
|
|
|
|
uri_path(b),
|
2015-04-04 02:49:52 +00:00
|
|
|
uri_path_array(b),
|
2015-02-18 04:23:18 +00:00
|
|
|
uri_query(b),
|
|
|
|
|
uri_fragment(b)
|
|
|
|
|
FROM test;
|
2015-04-04 02:49:52 +00:00
|
|
|
-[ RECORD 1 ]--+----------------------------------------------------------------------------------------
|
|
|
|
|
uri | http://www.postgresql.org/
|
2015-04-12 20:01:03 +00:00
|
|
|
uri_normalize | http://www.postgresql.org/
|
2015-04-04 02:49:52 +00:00
|
|
|
uri_scheme | http
|
|
|
|
|
uri_userinfo | _null_
|
|
|
|
|
uri_host | www.postgresql.org
|
|
|
|
|
uri_host_inet | _null_
|
|
|
|
|
uri_port | _null_
|
|
|
|
|
uri_path | /
|
|
|
|
|
uri_path_array | {""}
|
|
|
|
|
uri_query | _null_
|
|
|
|
|
uri_fragment | _null_
|
|
|
|
|
-[ RECORD 2 ]--+----------------------------------------------------------------------------------------
|
|
|
|
|
uri | http://www.postgresql.org/docs/devel/static/xfunc-sql.html#XFUNC-SQL-FUNCTION-ARGUMENTS
|
2015-04-12 20:01:03 +00:00
|
|
|
uri_normalize | http://www.postgresql.org/docs/devel/static/xfunc-sql.html#XFUNC-SQL-FUNCTION-ARGUMENTS
|
2015-04-04 02:49:52 +00:00
|
|
|
uri_scheme | http
|
|
|
|
|
uri_userinfo | _null_
|
|
|
|
|
uri_host | www.postgresql.org
|
|
|
|
|
uri_host_inet | _null_
|
|
|
|
|
uri_port | _null_
|
|
|
|
|
uri_path | /docs/devel/static/xfunc-sql.html
|
|
|
|
|
uri_path_array | {docs,devel,static,xfunc-sql.html}
|
|
|
|
|
uri_query | _null_
|
|
|
|
|
uri_fragment | XFUNC-SQL-FUNCTION-ARGUMENTS
|
|
|
|
|
-[ RECORD 3 ]--+----------------------------------------------------------------------------------------
|
|
|
|
|
uri | https://duckduckgo.com/?q=postgresql&ia=about
|
2015-04-12 20:01:03 +00:00
|
|
|
uri_normalize | https://duckduckgo.com/?q=postgresql&ia=about
|
2015-04-04 02:49:52 +00:00
|
|
|
uri_scheme | https
|
|
|
|
|
uri_userinfo | _null_
|
|
|
|
|
uri_host | duckduckgo.com
|
|
|
|
|
uri_host_inet | _null_
|
|
|
|
|
uri_port | _null_
|
|
|
|
|
uri_path | /
|
|
|
|
|
uri_path_array | {""}
|
|
|
|
|
uri_query | q=postgresql&ia=about
|
|
|
|
|
uri_fragment | _null_
|
|
|
|
|
-[ RECORD 4 ]--+----------------------------------------------------------------------------------------
|
|
|
|
|
uri | ftp://ftp.gnu.org/gnu/bison
|
2015-04-12 20:01:03 +00:00
|
|
|
uri_normalize | ftp://ftp.gnu.org/gnu/bison
|
2015-04-04 02:49:52 +00:00
|
|
|
uri_scheme | ftp
|
|
|
|
|
uri_userinfo | _null_
|
|
|
|
|
uri_host | ftp.gnu.org
|
|
|
|
|
uri_host_inet | _null_
|
|
|
|
|
uri_port | _null_
|
|
|
|
|
uri_path | /gnu/bison
|
|
|
|
|
uri_path_array | {gnu,bison}
|
|
|
|
|
uri_query | _null_
|
|
|
|
|
uri_fragment | _null_
|
|
|
|
|
-[ RECORD 5 ]--+----------------------------------------------------------------------------------------
|
|
|
|
|
uri | mailto:foo@example.com
|
2015-04-12 20:01:03 +00:00
|
|
|
uri_normalize | mailto:foo@example.com
|
2015-04-04 02:49:52 +00:00
|
|
|
uri_scheme | mailto
|
|
|
|
|
uri_userinfo | _null_
|
|
|
|
|
uri_host | _null_
|
|
|
|
|
uri_host_inet | _null_
|
|
|
|
|
uri_port | _null_
|
|
|
|
|
uri_path | foo@example.com
|
|
|
|
|
uri_path_array | {foo@example.com}
|
|
|
|
|
uri_query | _null_
|
|
|
|
|
uri_fragment | _null_
|
|
|
|
|
-[ RECORD 6 ]--+----------------------------------------------------------------------------------------
|
|
|
|
|
uri | ssh://username@review.openstack.org:29418/openstack/nova.git
|
2015-04-12 20:01:03 +00:00
|
|
|
uri_normalize | ssh://username@review.openstack.org:29418/openstack/nova.git
|
2015-04-04 02:49:52 +00:00
|
|
|
uri_scheme | ssh
|
|
|
|
|
uri_userinfo | username
|
|
|
|
|
uri_host | review.openstack.org
|
|
|
|
|
uri_host_inet | _null_
|
|
|
|
|
uri_port | 29418
|
|
|
|
|
uri_path | /openstack/nova.git
|
|
|
|
|
uri_path_array | {openstack,nova.git}
|
|
|
|
|
uri_query | _null_
|
|
|
|
|
uri_fragment | _null_
|
|
|
|
|
-[ RECORD 7 ]--+----------------------------------------------------------------------------------------
|
|
|
|
|
uri | http://admin:password@192.168.0.1
|
2015-04-12 20:01:03 +00:00
|
|
|
uri_normalize | http://admin:password@192.168.0.1
|
2015-04-04 02:49:52 +00:00
|
|
|
uri_scheme | http
|
|
|
|
|
uri_userinfo | admin:password
|
|
|
|
|
uri_host | 192.168.0.1
|
|
|
|
|
uri_host_inet | 192.168.0.1
|
|
|
|
|
uri_port | _null_
|
|
|
|
|
uri_path |
|
|
|
|
|
uri_path_array | {}
|
|
|
|
|
uri_query | _null_
|
|
|
|
|
uri_fragment | _null_
|
|
|
|
|
-[ RECORD 8 ]--+----------------------------------------------------------------------------------------
|
|
|
|
|
uri | http://[FEDC:BA98:7654:3210:FEDC:BA98:7654:3210]:80/index.html
|
2015-04-12 20:01:03 +00:00
|
|
|
uri_normalize | http://[fedc:ba98:7654:3210:fedc:ba98:7654:3210]:80/index.html
|
2015-04-04 02:49:52 +00:00
|
|
|
uri_scheme | http
|
|
|
|
|
uri_userinfo | _null_
|
|
|
|
|
uri_host | FEDC:BA98:7654:3210:FEDC:BA98:7654:3210
|
|
|
|
|
uri_host_inet | fedc:ba98:7654:3210:fedc:ba98:7654:3210
|
|
|
|
|
uri_port | 80
|
|
|
|
|
uri_path | /index.html
|
|
|
|
|
uri_path_array | {index.html}
|
|
|
|
|
uri_query | _null_
|
|
|
|
|
uri_fragment | _null_
|
|
|
|
|
-[ RECORD 9 ]--+----------------------------------------------------------------------------------------
|
|
|
|
|
uri | http://[1080::8:800:200C:417A]/foo
|
2015-04-12 20:01:03 +00:00
|
|
|
uri_normalize | http://[1080:0000:0000:0000:0008:0800:200c:417a]/foo
|
2015-04-04 02:49:52 +00:00
|
|
|
uri_scheme | http
|
|
|
|
|
uri_userinfo | _null_
|
|
|
|
|
uri_host | 1080::8:800:200C:417A
|
|
|
|
|
uri_host_inet | 1080::8:800:200c:417a
|
|
|
|
|
uri_port | _null_
|
|
|
|
|
uri_path | /foo
|
|
|
|
|
uri_path_array | {foo}
|
|
|
|
|
uri_query | _null_
|
|
|
|
|
uri_fragment | _null_
|
|
|
|
|
-[ RECORD 10 ]-+----------------------------------------------------------------------------------------
|
|
|
|
|
uri | http://host:
|
2015-04-12 20:01:03 +00:00
|
|
|
uri_normalize | http://host:
|
2015-04-04 02:49:52 +00:00
|
|
|
uri_scheme | http
|
|
|
|
|
uri_userinfo | _null_
|
|
|
|
|
uri_host | host
|
|
|
|
|
uri_host_inet | _null_
|
|
|
|
|
uri_port | _null_
|
|
|
|
|
uri_path |
|
|
|
|
|
uri_path_array | {}
|
|
|
|
|
uri_query | _null_
|
|
|
|
|
uri_fragment | _null_
|
|
|
|
|
-[ RECORD 11 ]-+----------------------------------------------------------------------------------------
|
|
|
|
|
uri |
|
2015-04-12 20:01:03 +00:00
|
|
|
uri_normalize |
|
2015-04-04 02:49:52 +00:00
|
|
|
uri_scheme | _null_
|
|
|
|
|
uri_userinfo | _null_
|
|
|
|
|
uri_host | _null_
|
|
|
|
|
uri_host_inet | _null_
|
|
|
|
|
uri_port | _null_
|
|
|
|
|
uri_path |
|
|
|
|
|
uri_path_array | {}
|
|
|
|
|
uri_query | _null_
|
|
|
|
|
uri_fragment | _null_
|
|
|
|
|
-[ RECORD 12 ]-+----------------------------------------------------------------------------------------
|
|
|
|
|
uri | /
|
2015-04-12 20:01:03 +00:00
|
|
|
uri_normalize | /
|
2015-04-04 02:49:52 +00:00
|
|
|
uri_scheme | _null_
|
|
|
|
|
uri_userinfo | _null_
|
|
|
|
|
uri_host | _null_
|
|
|
|
|
uri_host_inet | _null_
|
|
|
|
|
uri_port | _null_
|
|
|
|
|
uri_path | /
|
|
|
|
|
uri_path_array | {}
|
|
|
|
|
uri_query | _null_
|
|
|
|
|
uri_fragment | _null_
|
|
|
|
|
-[ RECORD 13 ]-+----------------------------------------------------------------------------------------
|
|
|
|
|
uri | foobar
|
2015-04-12 20:01:03 +00:00
|
|
|
uri_normalize | foobar
|
2015-04-04 02:49:52 +00:00
|
|
|
uri_scheme | _null_
|
|
|
|
|
uri_userinfo | _null_
|
|
|
|
|
uri_host | _null_
|
|
|
|
|
uri_host_inet | _null_
|
|
|
|
|
uri_port | _null_
|
|
|
|
|
uri_path | foobar
|
|
|
|
|
uri_path_array | {foobar}
|
|
|
|
|
uri_query | _null_
|
|
|
|
|
uri_fragment | _null_
|
|
|
|
|
-[ RECORD 14 ]-+----------------------------------------------------------------------------------------
|
|
|
|
|
uri | /foobar
|
2015-04-12 20:01:03 +00:00
|
|
|
uri_normalize | /foobar
|
2015-04-04 02:49:52 +00:00
|
|
|
uri_scheme | _null_
|
|
|
|
|
uri_userinfo | _null_
|
|
|
|
|
uri_host | _null_
|
|
|
|
|
uri_host_inet | _null_
|
|
|
|
|
uri_port | _null_
|
|
|
|
|
uri_path | /foobar
|
|
|
|
|
uri_path_array | {foobar}
|
|
|
|
|
uri_query | _null_
|
|
|
|
|
uri_fragment | _null_
|
2015-04-12 20:01:03 +00:00
|
|
|
-[ RECORD 15 ]-+----------------------------------------------------------------------------------------
|
|
|
|
|
uri | HTTP://www.EXAMPLE.com/
|
|
|
|
|
uri_normalize | http://www.example.com/
|
|
|
|
|
uri_scheme | HTTP
|
|
|
|
|
uri_userinfo | _null_
|
|
|
|
|
uri_host | www.EXAMPLE.com
|
|
|
|
|
uri_host_inet | _null_
|
|
|
|
|
uri_port | _null_
|
|
|
|
|
uri_path | /
|
|
|
|
|
uri_path_array | {""}
|
|
|
|
|
uri_query | _null_
|
|
|
|
|
uri_fragment | _null_
|
|
|
|
|
-[ RECORD 16 ]-+----------------------------------------------------------------------------------------
|
|
|
|
|
uri | http://www.ex%41mple.com/
|
|
|
|
|
uri_normalize | http://www.example.com/
|
|
|
|
|
uri_scheme | http
|
|
|
|
|
uri_userinfo | _null_
|
|
|
|
|
uri_host | www.ex%41mple.com
|
|
|
|
|
uri_host_inet | _null_
|
|
|
|
|
uri_port | _null_
|
|
|
|
|
uri_path | /
|
|
|
|
|
uri_path_array | {""}
|
|
|
|
|
uri_query | _null_
|
|
|
|
|
uri_fragment | _null_
|
|
|
|
|
-[ RECORD 17 ]-+----------------------------------------------------------------------------------------
|
|
|
|
|
uri | eXAMPLE://a/./b/../b/%63/%7bfoo%7d
|
|
|
|
|
uri_normalize | example://a/b/c/%7Bfoo%7D
|
|
|
|
|
uri_scheme | eXAMPLE
|
|
|
|
|
uri_userinfo | _null_
|
|
|
|
|
uri_host | a
|
|
|
|
|
uri_host_inet | _null_
|
|
|
|
|
uri_port | _null_
|
|
|
|
|
uri_path | /./b/../b/%63/%7bfoo%7d
|
|
|
|
|
uri_path_array | {.,b,..,b,%63,%7bfoo%7d}
|
|
|
|
|
uri_query | _null_
|
|
|
|
|
uri_fragment | _null_
|
2015-02-18 04:23:18 +00:00
|
|
|
|
|
|
|
|
\x off
|
|
|
|
|
SELECT DISTINCT b FROM test ORDER BY b;
|
|
|
|
|
b
|
|
|
|
|
-----------------------------------------------------------------------------------------
|
|
|
|
|
|
2015-04-04 02:49:52 +00:00
|
|
|
/
|
|
|
|
|
/foobar
|
2015-04-12 20:01:03 +00:00
|
|
|
HTTP://www.EXAMPLE.com/
|
|
|
|
|
eXAMPLE://a/./b/../b/%63/%7bfoo%7d
|
2015-02-18 04:23:18 +00:00
|
|
|
foobar
|
|
|
|
|
ftp://ftp.gnu.org/gnu/bison
|
|
|
|
|
http://[1080::8:800:200C:417A]/foo
|
|
|
|
|
http://[FEDC:BA98:7654:3210:FEDC:BA98:7654:3210]:80/index.html
|
|
|
|
|
http://admin:password@192.168.0.1
|
2015-02-19 03:10:05 +00:00
|
|
|
http://host:
|
2015-04-12 20:01:03 +00:00
|
|
|
http://www.ex%41mple.com/
|
2015-02-18 04:23:18 +00:00
|
|
|
http://www.postgresql.org/
|
|
|
|
|
http://www.postgresql.org/docs/devel/static/xfunc-sql.html#XFUNC-SQL-FUNCTION-ARGUMENTS
|
|
|
|
|
https://duckduckgo.com/?q=postgresql&ia=about
|
|
|
|
|
mailto:foo@example.com
|
|
|
|
|
ssh://username@review.openstack.org:29418/openstack/nova.git
|
2015-04-12 20:01:03 +00:00
|
|
|
(17 rows)
|
2015-02-18 04:23:18 +00:00
|
|
|
|
