Example of Using Qbim for a Test Object

Installing the Extension

create extension qbim cascade;

Creating a Test Object

create table test (
	id bigserial not null,
	sd timestamp not null default current_timestamp,
	ed timestamp not null default 'infinity'::timestamp,
	name text not null,
	state bigint not null,
	val bigint not null,
	dsc text not null,
	constraint cls_id_sd_ed_excl exclude using gist (id with =, tsrange(sd, ed) with &&) where ((sd <> ed))
);
create index test_id_sd_ed_excl on test using gist (id, tsrange(sd, ed)) where (sd <> ed);

comment on table test is 'Test object';
comment on column test.id is 'Identifier';
comment on column test.sd is 'Version start time';
comment on column test.ed is 'Version end time';
comment on column test.name is 'Name of the object test instance';
comment on column test.state is 'Instance state (CLS#3)';
comment on column test.val is 'Some value';
comment on column test.dsc is 'Description';

Description in Object Description Tables

select qbim_describe_object(i_table_name => 'test'::text, i_obj_id => null::bigint);

Note
If a specific value is passed as the i_obj_id parameter, you need to update the sequence value obj_id_seq to prevent conflicts of the identifier uniqueness.

For example,

select qbim_describe_object(i_table_name => 'test'::text, i_obj_id => 73::bigint);

select setval('obj_id_seq',73);

where 73 in this example is the maximum id of the obj table.

Creating an Object Implementation

Note
From here on, when calling interface methods, a random user ID '463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid is specified, which is used when registering an action.

select qbim_create(1::bigint,null::text,null::bigint,
  '-infinity'::timestamp,null::timestamp,
  '463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid,
  'name','Test record'::text,
  'state',1::bigint,
  'val',5::bigint
);

Changing an Object — Creating Versions

select qbim_change(1::bigint,null::text,1::bigint,
  '2021-03-30 13:35:30.0'::timestamp,null::timestamp,
  '463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid,
  'state',2::bigint
);

select qbim_change(1::bigint,null::text,1::bigint,
  '2021-03-30 13:36:30.0'::timestamp,null::timestamp,
  '463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid,
  'state',3::bigint
);

select qbim_change(1::bigint,null::text,1::bigint,
  '2021-03-30 13:35:30.0'::timestamp,'2021-03-30 13:36:00.0'::timestamp,
  '463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid,
  'val',6::bigint
);

select qbim_change(1::bigint,null::text,1::bigint,
  '2021-03-30 13:35:20.0'::timestamp,'2021-03-30 13:36:15.0'::timestamp,
  '463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid,
  'val',7::bigint
);

select qbim_change(1::bigint,null::text,1::bigint,
  '2021-03-30 13:36:00.0'::timestamp,null::timestamp,
  '463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid,
  'val',9::bigint
);

select qbim_change(1::bigint,null::text,1::bigint,
  '2021-03-30 13:35:20.0'::timestamp,'2021-03-30 13:36:15.0'::timestamp,
  '463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid,
  'val',10::bigint,
  'state', 6::bigint
);

select qbim_change(1::bigint,null::text,1::bigint,
  '2021-03-30 13:35:20.0'::timestamp,'2021-03-30 13:36:30.0'::timestamp,
  '463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid,
  'val',11::bigint,
  'state', 7::bigint
);

Selecting, Viewing Versions

It can be launched at different stages of object modification.

Request for a Point-in-Time Version

select * from test
where '2021-03-30 13:35:45.0'::timestamp <@ tsrange(sd,ed)
order by id, sd, ed;

Request for All Versions Except "Collapsed" Ones

select * from test where sd<>ed
order by id, sd, ed;

Request for All Versions

select * from test
order by id, sd, ed;

Closing the Version

select qbim_close(1::bigint,null::text,1::bigint,
  '2021-03-30 13:35:15.0'::timestamp,
  '463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid
);

Action Registration

select
    r_object, lnk_dt, fct_dt, action, pid, userid
from action;
r_objectlnk_dtfct_dtactionpiduserid
1:1-infinity2021-05-05 11:47:43.95759512522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:1-infinity2021-05-05 11:48:08.11745932522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-03-30 13:35:302021-05-05 11:48:08.11764922522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-03-30 13:35:302021-05-05 11:48:09.59536332522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-03-30 13:36:302021-05-05 11:48:09.59645722522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:1-infinity2021-05-05 11:48:10.98879932522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-03-30 13:35:302021-05-05 11:48:10.98905332522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-03-30 13:35:302021-05-05 11:48:10.98926422522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:1-infinity2021-05-05 11:48:12.51561132522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-03-30 13:35:302021-05-05 11:48:12.51667932522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-03-30 13:36:002021-05-05 11:48:12.51769232522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-03-30 13:35:202021-05-05 11:48:12.51882922522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-03-30 13:35:302021-05-05 11:48:12.51975522522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-03-30 13:35:302021-05-05 11:48:15.09410332522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-03-30 13:36:152021-05-05 11:48:15.09430232522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-03-30 13:36:302021-05-05 11:48:15.09448932522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-03-30 13:36:002021-05-05 11:48:15.09466322522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-03-30 13:36:302021-05-05 11:48:15.09482922522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:1-infinity2021-05-05 11:48:17.11089132522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-03-30 13:35:202021-05-05 11:48:17.11109432522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-03-30 13:35:302021-05-05 11:48:17.11128632522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-03-30 13:36:002021-05-05 11:48:17.11147632522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-03-30 13:35:202021-05-05 11:48:17.11169222522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:1-infinity2021-05-05 11:48:19.3658432522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-03-30 13:35:202021-05-05 11:48:19.36607732522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-03-30 13:36:152021-05-05 11:48:19.36626532522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-03-30 13:35:202021-05-05 11:48:19.36644322522463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-03-30 13:35:152021-05-05 11:51:57.07987842522463404e8-ee5d-4597-b9a9-e3b69e29d18a

The field values ​​are described in Section Description of Versioned Tables.

Note
The fct_dt and pid fields may differ; they are provided in this table as an example.

Copyright © KVANTOM LLC, 2019-2026