Generating evaluation auxiliary tables (cursor run - UDF)

The UDF (user defined function) skrpps_02.skr_create_secundary_db_tables(…) generates four intermediate tables.
These tables prepare raw data for the selected time period and the specified machines. The Reports to be carried out then, excepting the yarn demand and the expectable piece number, are based on them.

This function is implemented in pgSql,but internally it calls up UDFs which are implemented in C++.

UDF and Call-up Parameters

UDF:skrpps_02.skr_create_secundary_db_tables(…)

Parameter

Type

Description

_sDest_Schema

text

Schema, in which the generated tables should be saved ('report_01' … _03'). _sDestSchema must always be empty ‘ ‘ if temporary tables (recommended) are created.

_sTable_Prefix

text

Prefix of the generated tables, e.g. Your User Name.
Marks your table, since other users could also save tables.

_eTable_Type

skrpps_02.e_sec_tabletype

  • 'eUnlogged' for regular unlogged tables
  • 'eUnloggedTemp' for temporary tables

_sSrc_Schema

text

Source Schema ('skr' or 'skr_archive'). 'skr_archive' is only usable if you have previously loaded a secured skr database into this schema.

_mcids

integer[]

SKR machines ID of the machines to added to the secondary database.
e.g: ARRAY[1399537134,1399537135]

_starttime

starttime timestamp without time zone

Start timing of data

_endtime

starttime timestamp without time zone

End timing of data

_Iterator
[in]

integer DEFAULT 999999

You can split the cursor run to several individual call-ups of skr_create_secondary_db_tables(…) with the iterator. E.g. to get a progress bar.
The quantity of machines in _mcids will be count-down to zero. The new iterator value is the return value of this UDF, that one receives after the _Break_Timeout finishes.

_Break_Timeout

interval DEFAULT

'99999:00:00'

Time after which the cursor run shall be interrupted. E.g. to control a progress bar or to allow the cancellation.

However, the data of the last edited machine are always completed and can delay the timeout. With it a _BreakTimeout of ‘0:0:0‘ results in a termination in _mcids per machine.
Recommendation: _BreakTimeout >= ‘0:0:10‘
Due to the efforts for interrupting and restarting when creating the Secondary Database.

Code example sample_curser.sql

Creates the secondary database table in a loop.

DO

$do$

DECLARE

_Iterator int =999999;

BEGIN
WHILE _Iterator > 0 LOOP

_Iterator :=
(
    SELECT * from skrpps_02.skr_create_secundary_db_tables
    (
        'report_01'::text                             -- _Dest_Schema
        ,'sample'::text                                 -- _sTable_Prefix
        ,'eUnlogged'::skrpps_02.e_sec_tabletype    -- _eTable_Schema
        ,'skr_archive'::text                         -- _sSrc_Schema
        ,ARRAY[1399537134,1399537135,1399537136]    -- _mcids integer[]
        ,'2014-06-27 09:30:00'::timestamp         -- _starttime
        ,'2014-06-27 13:00:00'::timestamp         -- _endtime
        ,_Iterator                                        -- _Iterator integer
        ,'10'::interval                                 -- _Break_Timeout
    )

);

raise notice 'IteratorPos: % ',_Iterator ;
END LOOP;
END
$do$

The example is written in pgSql and can be executed as pgAdmin3.exe script by SQL.

A progress message appears for each processed machine during execution (Code: raise notice 'IteratorPos: % ',_Iterator).

After a successful execution of this script example, these tables are newly generated in the report_01 schema:

Important: