Add pg_sequences view

Like pg_tables, pg_views, and others, this view contains information
about sequences in a way that is independent of the system catalog
layout but more comprehensive than the information schema.

To help implement the view, add a new internal function
pg_sequence_last_value() to return the last value of a sequence.  This
is kept separate from pg_sequence_parameters() to separate querying
run-time state from catalog-like information.

Reviewed-by: Andreas Karlsson <andreas@proxel.se>

4 files changed, 68 insertions, 5 deletions

diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 5e2962c681d..031e8c2ef53 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1615,6 +1615,20 @@ UNION ALL
     l.label
    FROM (pg_shseclabel l
      JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid))));
+pg_sequences| SELECT n.nspname AS schemaname,
+    c.relname AS sequencename,
+    pg_get_userbyid(c.relowner) AS sequenceowner,
+    p.start_value,
+    p.minimum_value AS min_value,
+    p.maximum_value AS max_value,
+    p.increment AS increment_by,
+    p.cycle_option AS cycle,
+    p.cache_size,
+    pg_sequence_last_value((c.oid)::regclass) AS last_value
+   FROM (pg_class c
+     LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))),
+    LATERAL pg_sequence_parameters(c.oid) p(start_value, minimum_value, maximum_value, increment, cycle_option, cache_size)
+  WHERE ((NOT pg_is_other_temp_schema(n.oid)) AND (c.relkind = 'S'::"char"));
 pg_settings| SELECT a.name,
     a.setting,
     a.unit,
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 4ffbe92ab37..c5413e09f3b 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -300,20 +300,39 @@ SELECT nextval('sequence_test2');
        5
 (1 row)
 
+CREATE SEQUENCE sequence_test3;  -- not read from, to test is_called
 -- Information schema
 SELECT * FROM information_schema.sequences WHERE sequence_name IN
-  ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
+  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
    'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
   ORDER BY sequence_name ASC;
  sequence_catalog | sequence_schema |   sequence_name    | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value |    maximum_value    | increment | cycle_option 
 ------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+--------------
  regression       | public          | sequence_test2     | bigint    |                64 |                       2 |             0 | 32          | 5             | 36                  | 4         | YES
+ regression       | public          | sequence_test3     | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
  regression       | public          | serialtest2_f2_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
  regression       | public          | serialtest2_f3_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
  regression       | public          | serialtest2_f4_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
  regression       | public          | serialtest2_f5_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
  regression       | public          | serialtest2_f6_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
-(6 rows)
+(7 rows)
+
+SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
+FROM pg_sequences
+WHERE sequencename IN
+  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
+   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+  ORDER BY sequencename ASC;
+ schemaname |    sequencename    | start_value | min_value |      max_value      | increment_by | cycle | cache_size | last_value 
+------------+--------------------+-------------+-----------+---------------------+--------------+-------+------------+------------
+ public     | sequence_test2     |          32 |         5 |                  36 |            4 | t     |          1 |          5
+ public     | sequence_test3     |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |           
+ public     | serialtest2_f2_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
+ public     | serialtest2_f3_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
+ public     | serialtest2_f4_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
+ public     | serialtest2_f5_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
+ public     | serialtest2_f6_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
+(7 rows)
 
 -- Test comments
 COMMENT ON SEQUENCE asdf IS 'won''t work';
diff --git a/src/test/regress/expected/sequence_1.out b/src/test/regress/expected/sequence_1.out
index 05da2bf1ade..81642449278 100644
--- a/src/test/regress/expected/sequence_1.out
+++ b/src/test/regress/expected/sequence_1.out
@@ -300,20 +300,39 @@ SELECT nextval('sequence_test2');
        5
 (1 row)
 
+CREATE SEQUENCE sequence_test3;  -- not read from, to test is_called
 -- Information schema
 SELECT * FROM information_schema.sequences WHERE sequence_name IN
-  ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
+  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
    'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
   ORDER BY sequence_name ASC;
  sequence_catalog | sequence_schema |   sequence_name    | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value |    maximum_value    | increment | cycle_option 
 ------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+--------------
  regression       | public          | sequence_test2     | bigint    |                64 |                       2 |             0 | 32          | 5             | 36                  | 4         | YES
+ regression       | public          | sequence_test3     | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
  regression       | public          | serialtest2_f2_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
  regression       | public          | serialtest2_f3_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
  regression       | public          | serialtest2_f4_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
  regression       | public          | serialtest2_f5_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
  regression       | public          | serialtest2_f6_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
-(6 rows)
+(7 rows)
+
+SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
+FROM pg_sequences
+WHERE sequencename IN
+  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
+   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+  ORDER BY sequencename ASC;
+ schemaname |    sequencename    | start_value | min_value |      max_value      | increment_by | cycle | cache_size | last_value 
+------------+--------------------+-------------+-----------+---------------------+--------------+-------+------------+------------
+ public     | sequence_test2     |          32 |         5 |                  36 |            4 | t     |          1 |          5
+ public     | sequence_test3     |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |           
+ public     | serialtest2_f2_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
+ public     | serialtest2_f3_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
+ public     | serialtest2_f4_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
+ public     | serialtest2_f5_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
+ public     | serialtest2_f6_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
+(7 rows)
 
 -- Test comments
 COMMENT ON SEQUENCE asdf IS 'won''t work';
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 98a2e7db36d..e2b6b633930 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -138,12 +138,23 @@ SELECT nextval('sequence_test2');
 SELECT nextval('sequence_test2');
 SELECT nextval('sequence_test2');
 
+
+CREATE SEQUENCE sequence_test3;  -- not read from, to test is_called
+
+
 -- Information schema
 SELECT * FROM information_schema.sequences WHERE sequence_name IN
-  ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
+  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
    'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
   ORDER BY sequence_name ASC;
 
+SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
+FROM pg_sequences
+WHERE sequencename IN
+  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
+   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+  ORDER BY sequencename ASC;
+
 -- Test comments
 COMMENT ON SEQUENCE asdf IS 'won''t work';
 COMMENT ON SEQUENCE sequence_test2 IS 'will work';