Bug 6746 - Extend sys.sequences with columns showing the currentvalue and datatype
Summary: Extend sys.sequences with columns showing the currentvalue and datatype
Status: NEW
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: 11.33.3 (Apr2019)
Hardware: All All
: Normal enhancement
Assignee: SQL devs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2019-08-13 20:42 CEST by Martin van Dinther
Modified: 2019-08-13 20:42 CEST (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Martin van Dinther cwiconfidential 2019-08-13 20:42:12 CEST
User-Agent:       Mozilla/5.0 (Windows NT 6.1; rv:68.0) Gecko/20100101 Firefox/68.0
Build Identifier: 

Request to extend the system table sys.sequences with a column 'currentvalue' to also show the currentvalue for each sequence.

When implementing this request combine it with adding the column 'datatype' as described in bug 6745

Reproducible: Always

Steps to Reproduce:
select * from sys.sequences;
\d sequences
Actual Results:  
sql>select * from sys.sequences;
+-------+-------+-----------+-------+-------+-------+--------+--------+-------+
| id    | schem | name      | start | minva | maxva | increm | cachei | cycle |
:       : a_id  :           :       : lue   : lue   : ent    : nc     :       :
+=======+=======+===========+=======+=======+=======+========+========+=======+
| 22293 |  2000 | seq_22285 |     1 |     0 |     0 |      1 |      1 | false |
+-------+-------+-----------+-------+-------+-------+--------+--------+-------+
1 tuple
sql>

sql>\d sequences
CREATE TABLE "sys"."sequences" (
        "id"        INTEGER,
        "schema_id" INTEGER,
        "name"      VARCHAR(256),
        "start"     BIGINT,
        "minvalue"  BIGINT,
        "maxvalue"  BIGINT,
        "increment" BIGINT,
        "cacheinc"  BIGINT,
        "cycle"     BOOLEAN
);
sql>


Expected Results:  
sql>select * from sys.sequences;
+-------+-----------+-----------+-------+----------+----------+--------------+-----------+----------+-------+
| id    | schema_id | name      | start | minvalue | maxvalue | currentvalue | increment | cacheinc | cycle |
+=======+===========+===========+=======+==========+==========+==============+===========+==========+=======+
| 22293 |      2000 | seq_22285 |     1 |        0 |        0 |           11 |         1 |        1 | false |
+-------+-----------+-----------+-------+----------+----------+--------------+-----------+----------+-------+
1 tuple

sql>\d sequences
CREATE TABLE "sys"."sequences" (
        "id"        INTEGER,
        "schema_id" INTEGER,
        "name"      VARCHAR(256),
        "datatype"  VARCHAR(256),
        "start"     BIGINT,
        "minvalue"  BIGINT,
        "maxvalue"  BIGINT,
        "currentvalue" BIGINT,
        "increment" BIGINT,
        "cacheinc"  BIGINT,
        "cycle"     BOOLEAN
);
sql>

Alternatively we could add a system view to derive the currentvalue column:
CREATE OR REPLACE VIEW "sys"."sequences_vw" AS
select q.id, schema_id, s.name as schema, q.name, start, minvalue, maxvalue, get_value_for(s.name, q.name) as currentvalue, increment, cacheinc, cycle
from "sys"."sequences" q join "sys"."schemas" s on s.id = q.schema_id order by schema, name;