Add pg_size_bytes() to parse human-readable size strings.

This will parse strings in the format produced by pg_size_pretty() and
return sizes in bytes. This allows queries to be written with clauses
like "pg_total_relation_size(oid) > pg_size_bytes('10 GB')".

Author: Pavel Stehule with various improvements by Vitaly Burovoy
Discussion: http://www.postgresql.org/message-id/CAFj8pRD-tGoDKnxdYgECzA4On01_uRqPrwF-8LdkSE-6bDHp0w@mail.gmail.com
Reviewed-by: Vitaly Burovoy, Oleksandr Shulgin, Kyotaro Horiguchi,
    Michael Paquier and Robert Haas

2 files changed, 148 insertions, 0 deletions

diff --git a/src/test/regress/expected/dbsize.out b/src/test/regress/expected/dbsize.out
index aa513e7eff0..20d8cb56893 100644
--- a/src/test/regress/expected/dbsize.out
+++ b/src/test/regress/expected/dbsize.out
@@ -35,3 +35,112 @@ SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM
  1000000000000000.5 | 909 TB         | -909 TB
 (12 rows)
 
+SELECT size, pg_size_bytes(size) FROM
+    (VALUES ('1'), ('123bytes'), ('1kB'), ('1MB'), (' 1 GB'), ('1.5 GB '),
+            ('1TB'), ('3000 TB'), ('1e6 MB')) x(size);
+   size   |  pg_size_bytes   
+----------+------------------
+ 1        |                1
+ 123bytes |              123
+ 1kB      |             1024
+ 1MB      |          1048576
+  1 GB    |       1073741824
+ 1.5 GB   |       1610612736
+ 1TB      |    1099511627776
+ 3000 TB  | 3298534883328000
+ 1e6 MB   |    1048576000000
+(9 rows)
+
+-- case-insensitive units are supported
+SELECT size, pg_size_bytes(size) FROM
+    (VALUES ('1'), ('123bYteS'), ('1kb'), ('1mb'), (' 1 Gb'), ('1.5 gB '),
+            ('1tb'), ('3000 tb'), ('1e6 mb')) x(size);
+   size   |  pg_size_bytes   
+----------+------------------
+ 1        |                1
+ 123bYteS |              123
+ 1kb      |             1024
+ 1mb      |          1048576
+  1 Gb    |       1073741824
+ 1.5 gB   |       1610612736
+ 1tb      |    1099511627776
+ 3000 tb  | 3298534883328000
+ 1e6 mb   |    1048576000000
+(9 rows)
+
+-- negative numbers are supported
+SELECT size, pg_size_bytes(size) FROM
+    (VALUES ('-1'), ('-123bytes'), ('-1kb'), ('-1mb'), (' -1 Gb'), ('-1.5 gB '),
+            ('-1tb'), ('-3000 TB'), ('-10e-1 MB')) x(size);
+   size    |   pg_size_bytes   
+-----------+-------------------
+ -1        |                -1
+ -123bytes |              -123
+ -1kb      |             -1024
+ -1mb      |          -1048576
+  -1 Gb    |       -1073741824
+ -1.5 gB   |       -1610612736
+ -1tb      |    -1099511627776
+ -3000 TB  | -3298534883328000
+ -10e-1 MB |          -1048576
+(9 rows)
+
+-- different cases with allowed points
+SELECT size, pg_size_bytes(size) FROM
+     (VALUES ('-1.'), ('-1.kb'), ('-1. kb'), ('-0. gb'),
+             ('-.1'), ('-.1kb'), ('-.1 kb'), ('-.0 gb')) x(size);
+  size  | pg_size_bytes 
+--------+---------------
+ -1.    |            -1
+ -1.kb  |         -1024
+ -1. kb |         -1024
+ -0. gb |             0
+ -.1    |             0
+ -.1kb  |          -102
+ -.1 kb |          -102
+ -.0 gb |             0
+(8 rows)
+
+-- invalid inputs
+SELECT pg_size_bytes('1 AB');
+ERROR:  invalid size: "1 AB"
+DETAIL:  Invalid size unit: "AB".
+HINT:  Valid units are "bytes", "kB", "MB", "GB", and "TB".
+SELECT pg_size_bytes('1 AB A');
+ERROR:  invalid size: "1 AB A"
+DETAIL:  Invalid size unit: "AB A".
+HINT:  Valid units are "bytes", "kB", "MB", "GB", and "TB".
+SELECT pg_size_bytes('1 AB A    ');
+ERROR:  invalid size: "1 AB A    "
+DETAIL:  Invalid size unit: "AB A".
+HINT:  Valid units are "bytes", "kB", "MB", "GB", and "TB".
+SELECT pg_size_bytes('9223372036854775807.9');
+ERROR:  bigint out of range
+SELECT pg_size_bytes('1e100');
+ERROR:  bigint out of range
+SELECT pg_size_bytes('1e1000000000000000000');
+ERROR:  invalid size: "1e1000000000000000000"
+SELECT pg_size_bytes('1 byte');  -- the singular "byte" is not supported
+ERROR:  invalid size: "1 byte"
+DETAIL:  Invalid size unit: "byte".
+HINT:  Valid units are "bytes", "kB", "MB", "GB", and "TB".
+SELECT pg_size_bytes('');
+ERROR:  invalid size: ""
+SELECT pg_size_bytes('kb');
+ERROR:  invalid size: "kb"
+SELECT pg_size_bytes('..');
+ERROR:  invalid size: ".."
+SELECT pg_size_bytes('-.');
+ERROR:  invalid size: "-."
+SELECT pg_size_bytes('-.kb');
+ERROR:  invalid size: "-.kb"
+SELECT pg_size_bytes('-. kb');
+ERROR:  invalid size: "-. kb"
+SELECT pg_size_bytes('.+912');
+ERROR:  invalid size: ".+912"
+SELECT pg_size_bytes('+912+ kB');
+ERROR:  invalid size: "+912+ kB"
+DETAIL:  Invalid size unit: "+ kB".
+HINT:  Valid units are "bytes", "kB", "MB", "GB", and "TB".
+SELECT pg_size_bytes('++123 kB');
+ERROR:  invalid size: "++123 kB"
diff --git a/src/test/regress/sql/dbsize.sql b/src/test/regress/sql/dbsize.sql
index c118090cc64..d10a4d7f68a 100644
--- a/src/test/regress/sql/dbsize.sql
+++ b/src/test/regress/sql/dbsize.sql
@@ -10,3 +10,42 @@ SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM
             (10.5::numeric), (1000.5::numeric), (1000000.5::numeric),
             (1000000000.5::numeric), (1000000000000.5::numeric),
             (1000000000000000.5::numeric)) x(size);
+
+SELECT size, pg_size_bytes(size) FROM
+    (VALUES ('1'), ('123bytes'), ('1kB'), ('1MB'), (' 1 GB'), ('1.5 GB '),
+            ('1TB'), ('3000 TB'), ('1e6 MB')) x(size);
+
+-- case-insensitive units are supported
+SELECT size, pg_size_bytes(size) FROM
+    (VALUES ('1'), ('123bYteS'), ('1kb'), ('1mb'), (' 1 Gb'), ('1.5 gB '),
+            ('1tb'), ('3000 tb'), ('1e6 mb')) x(size);
+
+-- negative numbers are supported
+SELECT size, pg_size_bytes(size) FROM
+    (VALUES ('-1'), ('-123bytes'), ('-1kb'), ('-1mb'), (' -1 Gb'), ('-1.5 gB '),
+            ('-1tb'), ('-3000 TB'), ('-10e-1 MB')) x(size);
+
+-- different cases with allowed points
+SELECT size, pg_size_bytes(size) FROM
+     (VALUES ('-1.'), ('-1.kb'), ('-1. kb'), ('-0. gb'),
+             ('-.1'), ('-.1kb'), ('-.1 kb'), ('-.0 gb')) x(size);
+
+-- invalid inputs
+SELECT pg_size_bytes('1 AB');
+SELECT pg_size_bytes('1 AB A');
+SELECT pg_size_bytes('1 AB A    ');
+SELECT pg_size_bytes('9223372036854775807.9');
+SELECT pg_size_bytes('1e100');
+SELECT pg_size_bytes('1e1000000000000000000');
+SELECT pg_size_bytes('1 byte');  -- the singular "byte" is not supported
+SELECT pg_size_bytes('');
+
+SELECT pg_size_bytes('kb');
+SELECT pg_size_bytes('..');
+SELECT pg_size_bytes('-.');
+SELECT pg_size_bytes('-.kb');
+SELECT pg_size_bytes('-. kb');
+
+SELECT pg_size_bytes('.+912');
+SELECT pg_size_bytes('+912+ kB');
+SELECT pg_size_bytes('++123 kB');