NAV Navbar
Allied logo Logo
matlab

PgMex


________        ______  ___            
___  __ \______ ___   |/  /________  __
__  /_/ /_  __ `/_  /|_/ /_  _ \_  |/_/
_  ____/_  /_/ /_  /  / / /  __/_>  <  
/_/     _\__, / /_/  /_/  \___//_/|_|  
        /____/

PgMex is a high-performance PostgreSQL client library for Matlab that enables a Matlab-based application to communicate with PostgreSQL database in the Matlab native way by passing data in a form of matrices, multi-dimensional arrays and structures. The library is written in pure C which gives a significant performance boost for both small and data-heavy database requests. Both Windows and Linux platforms are supported.

Key features

PgMex has the following key features:

We'll show you sample code on this pane!

API Overview

The library contains of a single package pgmex with a single function pgmexec designed for execution of various commands like connecting to a database, executing SQL queries on PostgreSQL server etc. The first input to pgmexec is always a command name with a few command-specific input parameters to follow.

import com.allied.pgmex.pgmexec;
%% connect to db
dbConn=com.allied.pgmex.pgmexec('connect','host=<myHos> user=<myUser> port=<myPort> password=<myPassword> dbname=<myDB>')
%% create schema and table
pgmexec('exec',dbConn,'CREATE SCHEMA IF NOT EXISTS demo');
pgmexec('exec',dbConn,'DROP TABLE IF EXISTS demo.demo_table');
pgResult=pgmexec('exec',dbConn,['CREATE TABLE IF NOT EXISTS demo.demo_table ('...
    't_date date,'...
    'exp_name varchar(40),'...
    'exp_conf xml,'...
    'exp_t_vec timestamp[],'...
    'exp_col_name_vec name[],'...
    'exp_res_mat float8[])'])% pgResult=uint64(3740775088)
%% get status of execution result (all is successful)
pgmexec('resultStatus',pgResult)% ans = 1
pgmexec('cmdStatus',pgResult)% ans= 'CREATE TABLE'
%% clear result
pgmexec('clear',pgResult)
%% insert the first tuple into the table and get it as text
%% create parameters structure
pgParam=pgmexec('paramCreate',dbConn)% pgParam =uint64(3843492816)
%% put values of parameters
pgmexec('putf',pgParam,...
    '%date %varchar %xml %timestamp[]@ %name[]@ %float8[]',...
    datenum('1-Dec-2016'),'Experiment #1',...
    ['<model type="struct" ><param1 type="double" >1</param1>'...
    '<param2 type="boolean" >false</param2></model>'],...
    datenum({'0:00:00';'0:00:10';'0:01:30'}),{'res1','res2'},...
    struct('valueMat',{[NaN 2.4;-2 Inf;0 NaN]},...
    'isNullMat',{[true false;false false;true false]},...
    'isValueNull',false));
% insert the correspoding tuple into the table
pgmexec('paramExec',dbConn,pgParam,...
    'INSERT INTO demo.demo_table values ($1, $2, $3, $4, $5, $6)');
% reset parameters once again
pgmexec('paramreset',pgParam)
% put new values of parameters
pgmexec('putf',pgParam,...
    '%date %varchar %xml %timestamp[]@ %name[]@ %float8[]',...
    datenum('3-Dec-2016'),'Experiment #2',[...
    '<model type="struct" ><param1 type="double" >NaN</param1>'...
    '   <param2 type="boolean" >false</param2></model>'],...
    datenum({'0:00:40';'0:02:30'}),{'res1','res2','res3'},...
    struct('valueMat',{[]},'isNullMat',{logical([])},...
    'isValueNull',{true}));
% insert one more tupole into the table
pgmexec('paramExec',dbConn,pgParam,...
    'INSERT INTO demo.demo_table values ($1, $2, $3, $4, $5, $6)');
% clear the parameters as they are not necesssary
pgmexec('paramClear',pgParam)
%% get results as binary
pgResult=pgmexec('exec',dbConn,'SELECT * FROM  demo.demo_table')
% pgResult=uint64(3849610544)
%
%% check pgResult
pgmexec('binaryTuples',pgResult)% ans=true - binary result
pgmexec('resultStatus',pgResult) %ans=2 (TUPLES_OK)
pgmexec('cmdStatus',pgResult)% ans='SELECT 2'
pgmexec('cmdTuples',pgResult)% ans='2'
pgmexec('nFields',pgResult)% ans=6
pgmexec('nTuples',pgResult)% ans=2
%% extract results
% get vector of experiment dates
STDate=pgmexec('getf',pgResult,'#date','t_date')
% STDate = struct with fields:
%          valueVec: [2?1 double]
%         isNullVec: [2?1 logical]
%    isValueNullVec: [2?1 logical]
%
tDateVec=STDate.valueVec
%
% tDateVec =[736666;736667]
%
% get names of experiments
SExpName=pgmexec('getf',pgResult,'%varchar',1)
% SExpName = struct with fields:
%           valueVec: {2?1 cell}
%          isNullVec: [2?1 logical]
%     isValueNullVec: [2?1 logical]
%
expNameCVec=SExpName.valueVec
% expNameCVec ={'Experiment #1','Experiment #2'}
%
% get results of experiments
[SExpTVec, SExpCol, SExpRes]=pgmexec('getf',pgResult,...
    '#timestamp[]@ #name[]@ #float8[]',...
    'exp_t_vec','exp_col_name_vec','exp_res_mat')
%
% SExpTVec, SExpCol, SExpRes are structures with fields:
%           valueVec: {2?1 cell}
%          isNullVec: {2?1 cell}
%     isValueNullVec: [2?1 logical]
%
% SExpTVec.valueVec={...
%    [736330;736330.000115741;736330.001041667];
%    [736330.000462963;736330.001736111]};
%
% SExpCol.valueVec={{'res1';'res2'};{'res1';'res2';'res3'}}
% SExpCol.isNullVec={[false;false];[false;false;false]}
% SExpCol.isValueNullVec=[false;false]
%
% SExpRes.valueVec={[0 2.4;-2 Inf;0 NaN];[]}
% SExpRes.isNullVec={[true false;false false;true false];logical.empty(0,0)}
% SExpRes.isValueNullVec=[false;true]
%
%% clear results
pgmexec('clear',pgResult)

Pointer arguments:

Some input and output arguments, are pointers to C structures, such as PGconnect. Matlab type for these arguments depends on the architecture of the implementation: uint32 for 32-bit or uint64 for 64-bit. These pointers often provide a handle for dynamically-allocated resources that should be freed when they are no longer needed. Free resources with special commands, such as clear and finish.

Field specification strings:

Commands putf and getf use field specification strings that essentially represent codecs for converting betwen PostgreSQL data Matlab data for specific pairs of types. The following table covers all Postgres-Matlab type pairs supported by PgMex.

PGMEX Postgres Matlab
int2 smallint int16
int4 integer int32
int8 bigint int64
float4 real single
float8 double precision double
numeric numeric char [1,N]
bool boolean logical
varchar varchar char [1,N]
text text char [1,N]
bpchar char char [1,N]
name name char [1,N]
xml xml char [1,N]
json json char [1,N]
bytea bytea uint8 [1,N]
serbytea bytea any type*
serbytea_wn bytea any type*
date date datenum
time time datenum
timetz timetz datenum
timestamp timestamp datenum
timestamptz timestamptz datenum

If ’@’ is added at the end of a type spec, then if the original value is not of a variable-length type, the value will be transformed to a column vector [N,1] (getf) or a 1D array (putf), regardless of the dimensions of the original value.

An empty array must have the dimensions [0,0] when it is given as an argument to putf. When an empty (zero-dimensional) array is read from the database, the resulting Matlab array also has the dimensions [0,0].

Variable-length inputs and outputs are string types, such as varchar, and byte array types, such as bytea. Scalar values of variable-length types are stored in row vectors, and arrays of variable-length types are stored in cell arrays. For instance, a [2,2] array of varchar may be given as {'foo','bar';'baz','qux'}.

serbytea and serbytea_wn type specs serve to store any Matlab variable as a byte array. The serialization and deserialization are performed in putf and getf respectively. The corresponding Postgres type is bytea for scalars and bytea[] for arrays. As with other variable-length types, arrays of variables should be packaged in cell arrays. When serbytea_wn type spec is used, putf always expects a special structure with NULL specifiers on input (see documentation for putf below). serbytea is used to put “naked” values, i.e. any structure will be interpreted as a Matlab variable to be serialized as is. For getf the specs serbytea and serbytea_wn are equivalent.

Date and time inputs and outputs are Matlab datenums (doubles). Since datenums do not store time zone information, time zone for timetz and timestamptz values is assumed to be UTC. The schema part of the type spec for PgMex types is pgmex, whereas the schema for standard types is pg_catalog. However, it is not necessary to specify the pgmex schema in the field spec string.

Exceptions: The first (component) part of PgMex exception identifiers is PgMex, followed by the function name and error mnemonic.

Connection Control

connect

connect opens a new database connection

INPUT:

OUTPUT:

 %% Example:
 dbConn=com.allied.pgmex.pgmexec('connect','dbname=test user=postgres');

finish

finish closes the connection to the server. Also frees memory used by the PGconn object.

com.allied.pgmex.pgmexec('finish',dbConn)

INPUT:

regular:

reset

reset resets the communication channel to the server.

 com.allied.pgmex.pgmexec('reset',dbConn)

INPUT:

regular:

DESCRIPTION:

This function will close the connection to the server and attempt to re-establish a new connection to the same server, using all the same parameters previously used. This might be useful for error recovery if a working connection is lost.

Connection Status

db

db returns the database name of the connection.

 dbName=com.allied.pgmex.pgmexec('db',dbConn)

INPUT:

regular:

OUTPUT:

user

user returns the user name of the connection.

 userName=com.allied.pgmex.pgmexec('user',dbConn)

INPUT:

regular:

OUTPUT:

pass

pass returns the password of the connection.

 connPass=com.allied.pgmex.pgmexec('pass',dbConn)

INPUT:

regular:

OUTPUT:

host

host returns the server host name of the connection.

 hostName=com.allied.pgmex.pgmexec('host',dbConn)

INPUT:

regular:

OUTPUT:

port

port returns the port of the connection.

 connPort=com.allied.pgmex.pgmexec('port',dbConn)

INPUT:

regular:

OUTPUT:

options

options Returns the command-line options passed in the connection request.

 connOpt=com.allied.pgmex.pgmexec('options',dbConn)

INPUT:

regular:

OUTPUT:

status

status returns the status of the connection.

 connStatus=com.allied.pgmex.pgmexec('status',dbConn)

INPUT:

regular:

OUTPUT:

DESCRIPTION:

The status can be one of a number of values. However, only two of these are seen outside of an asynchronous connection procedure: CONNECTION_OK (0) and CONNECTION_BAD (1). A good connection to the database has the status CONNECTION_OK. A failed connection attempt is signaled by status CONNECTION_BAD. Ordinarily, an OK status will remain so until finish command, but a communications failure might result in the status changing to CONNECTION_BAD prematurely. In that case the application could try to recover by calling reset.

errorMessage

errorMessage returns the error message most recently generated by an operation on the connection.

 errMsg=com.allied.pgmex.pgmexec('errorMessage',dbConn)

INPUT:

regular:

OUTPUT:

Most failing PgMex commands throw exceptions. This command is left for backward compatibility.

Working With Parameters

paramCreate

paramCreate creates a new PGparam object for storing command parameters.

pgParam=com.allied.pgmex.pgmexec('paramCreate',dbConn)

INPUT:

regular:

OUTPUT:

paramCount

paramCount returns the number of parameters in a PGparam object.

nParams=com.allied.pgmex.pgmexec('paramCount',pgParam)

INPUT:

regular:

OUTPUT:

paramReset

paramReset clears out any previously put parameters.

com.allied.pgmex.pgmexec('paramReset',pgParam)

INPUT:

regular:

DESCRIPTION:

This function only resets the parameter counter, but does not free any memory. It is useful for reusing the PGparam object. You must still call paramclear in the end.

paramClear

paramclear releases all resources being used by a PGparam object.

pgmexec('paramClear',pgParam)

INPUT:

regular:

putf

putf packs a set of parameters in a PGparam structure for use with a parameterized query.

Put a date, a string, a double vector and a double matrix:

pgmexec('putf',pgParam ,'%date %varchar %float8[]@ %float8[]',...
    datenum('2015-03-26'),'description',[1;2;3;4],[0.1, 0.2; 0.3, 0.4]);

Put an integer matrix with one NULL cell, a NULL value of a boolean array type (null::bool[]) and a NULL value of a varchar type (null::varchar):

SArray=struct('valueMat',[1, 2; 0, 4],'isNullMat',logical([0, 0; 1 0]));
SNull=struct('valueMat',[],'isValueNull',true);
pgmexec('putf',pgParam ,'%int4[] %bool[] %varchar',SArray,SNull,SNull);

Put a string array, a serialized Matlab object (bytea), and an array of serialized Matlab objects (bytea[]):

pgmexec('putf',pgParam ,'%text[] %serbytea %serbytea[]',...
    {'foo','bar';'baz','qux'},struct('arr',[1 2 3]),...
    {[10 20 30],'abcd',@sin});
%
pgmexec('paramClear',pgParam );

when extracted via 'getf’ command with 'serbytea’ field specification, struct('arr’,[1 2 3]) is automatically de-serialized into Matlab structure the same goes for cell elements provided as values for ’%serbytea[]’ field specification

INPUT:

regular:

OUTPUT:

none.

DESCRIPTION

SFieldVal1,…,SFieldValN inputs are expected to be formed according to the following rules

Command Execution

exec

exec submits a command to the server and waits for the result.

pgResult=com.allied.pgmex.pgmexec('exec',dbConn,command)

INPUT:

regular:

OUTPUT:

DESCRIPTION:

paramExec

paramExec submits a parameterized command to the server and waits for the result.

pgResult=com.allied.pgmex.pgmexec('paramExec',dbConn,pgParam,commandStr)

INPUT:

regular:

OUTPUT:

DESCRIPTION:

import com.allied.pgmex.pgmexec;
pgParam=pgmexec('paramCreate',dbConn);
pgmexec('putf',pgParam,'%int4 %name',10,'foo');
res=pgmexec('paramExec',dbConn,pgParam,...
    'select * from my_table where ind > $1 and code = $2');
pgmexec('paramClear',pgParam);
% do something with result...
pgmexec('clear',res);

batchParamExec

batchParamExec creates a prepared statement for a given command and executes this statement within batch mode on the server for combinations of parameters values.

Example 1: Insert 100000 tuples, each with an integer, a boolean, a string and an array 1x2 of dates

import com.allied.pgmex.pgmexec;
SData=struct();
SData.mytupleno=transpose(int32(1:100000));
SData.myticker=strcat('foo',cellfun(@num2str,num2cell(transpose(1:100000)),...
    'UniformOutput',false);
startDateVec=datenum('1-Jan-2000')+transpose(0:99999);
SData.mydate_span=[startDateVec startDateVec+30];
SData.ismytuple=randi([0 1],100000,1); % double, but convertible to bool
pgmexec('batchParamExec',dbConn,'insert into my_table1 values ($1,$2,$3,$4)',...
    '%int4 %name %date[] %bool',SData);

Example 2: Insert 100000 tuples, each with a column array of strings, a serialized function_handle and a double array, prefix for prepared statement is defined

SData=struct();
SData.mystr_arr=repmat({{'a','b','c'};{'d'};{'e','f'};{}},25000,1);
SData.myfunc_list=repmat({@sin;@cos},50000,1);
SData.mymeas_mat=repmat({[1 2];[];[1 3;2 4];0.5;[NaN;Inf]},20000,1);
% insert mystr_arr as column array due to '@' postfix
pgmexec('batchParamExec',dbConn,'insert into my_table2 values ($1,$2,$3)',...
    '%varchar[]@ %serbytea %float8[]',SData,[],[],'my_bpe_prefix');

Example 3: Insert 100000 tuples, each with an integer and a string, some elements of values are NULL

SData=struct();
SData.myind=randi([1 10],100000,1); % convertible to int4
tickerCVec={'aaa';'bb';'cccc'};
SData.myticker=tickerCVec(randi([1 3],100000,1));
SIsNull=struct();
SIsNull.myind=repmat([true;false;false;true],25000,1);
SIsNull.myticker=logical(randi([0 1],100000,1));
pgmexec('batchParamExec',dbConn,'insert into my_table3 values ($1,$2)',...
    '%int4 %name',SData,SIsNull);

Example 4: Insert 100000 tuples, each with an integer, a string of length 3, a numeric matrix of arbitrary size and a numeric array 1x2x2, some values are NULL as a whole

SData=struct();
SData.mytupleno=int64(transpose(1:100000));
currMat=['USD';'EUR';'RUB'];
SData.mycurr=currMat(randi([1 3],100000,1),:);
SData.mymeas_mat=repmat({[1 2];[];[1 3;2 4];0.5;[NaN;Inf]},20000,1);
numMat=cat(1,reshape([1 NaN 0.5 -1],1,2,2),reshape([-Inf 0.25 3 Inf],1,2,2),reshape([NaN 0 3 2.5],1,2,2));
SData.mynum_mat=numMat(randi([1 3],100000,1),:,:);
SIsValueNull=struct();
SIsValueNull.mytupleno=false(100000,1);
SIsValueNull.mycurr=repmat([true;false;true;true;false],20000,1);
SIsValueNull.mymeas_mat=logical(randi([0 1],100000,1));
SIsValueNull.mynum_mat=repmat([true;false],50000,1);
pgmexec('batchParamExec',dbConn,'insert into my_table4 values ($1,$2,$3,$4)',...
    '%int8 %varchar %float4[] %float8[]',SData,[],SIsValueNull);

INPUT:

regular:

optional:

OUTPUT:

none.

DESCRIPTION

WHAT DOES THIS COMMAND DO AND HOW

In essence, batchParamExec does the same as several sequential calls of putf and paramExec do (but without creation of pgParam through paramCreate). The differences are that batchParamExec

This may be useful for such commands as INSERT, UPDATE or DELETE, or if some stored procedure not returning results are executed on the server for some sufficiently large number of parameters values combinations.

GENERAL REMARKS

RULES FOR DATA AND INFO ON NULLS

Example 5: Prepare data for tuples, each with a char and a string of length 3

callPutVec=['C';'P'];
SData.mycall_put=callPutVec(randi([1 2],100000,1)); % use %bpchar in fieldSpecStr
currMat=['USD';'EUR';'RUB'];
SData.mycurr=currMat(randi([1 3],100000,1),:); % use %varchar in fieldSpecStr

Example 6: Prepare data for tuples, each with a numeric array 1x2x2

measMat=cat(1,reshape([1 NaN 0.5 -1],1,2,2),...
    reshape([-Inf 0.25 3 Inf],1,2,2),...
    reshape([NaN 0 3 2.5],1,2,2));
SData.mymeas_mat=measMat(...
    randi([1 3],100000,1),:,:); % use %float4[] or %float8[] in fieldSpecStr

Example 7: Prepare data and info on NULLs for tuples, each with a string array and an array of numeric matrices

SData=struct();
SData.myticker_vec=repmat(...
    {{'aaa','aa'};{'bbbb','bbbbb',''}},...
    50000,1); % use %varchar[] in fieldSpecStr
SData.mymeas_mat=repmat(...
    {[1 2];[1 3;2 4];0.5;[NaN;Inf]},...
    25000,1); % use %float4[] or %float8[] in fieldSpecStr
SIsNull=struct();
SIsNull.myticker_vec=repmat({[false true];false;[false true false]},50000,1);
SIsNull.mymeas_mat=repmat({[true false];false(2,2);true;[false;true]},25000,1);

Example 8: Prepare data and info on NULLs for tuples, each with a numeric array 1x2x2

SData=struct();
measMat=cat(1,reshape([1 NaN 0.5 -1],1,2,2),...
    reshape([-Inf 0.25 3 Inf],1,2,2),...
    reshape([NaN 0 3 2.5],1,2,2));
SData.mymeas_mat=measMat(...
    randi([1 3],100000,1),:,:); % use %float4[] or %float8[] in fieldSpecStr
SIsNull=struct();
SIsNull.mymeas_mat=logical(randi([0 1],100000,2,2));

pqExec

pqExec submits a command to the server and waits for the result (libpq version).

pgResult=com.allied.pgmex.pgmexec('pqExec',dbConn,command)

INPUT:

regular:

OUTPUT:

DESCRIPTION:

resultStatus

resultStatus returns the result status of the command.

execStatus=com.allied.pgmex.pgmexec('resultStatus',pgResult)

INPUT:

regular:

OUTPUT:

Code value Code name Code description
0 EMPTY_QUERY empty query string was executed
1 COMMAND_OK a query command that doesn’t return anything was executed properly by the backend
2 TUPLES_OK a query command that returns tuples was executed properly by the backend, PGresult contains the result tuples
3 COPY_OUT Copy Out data transfer in progress
4 COPY_IN Copy In data transfer in progress
5 BAD_RESPONSE an unexpected response was recv’d from the backend
6 NONFATAL_ERROR notice or warning message
7 FATAL_ERROR query failed
8 COPY_BOTH Copy In/Out data transfer in progress
9 SINGLE_TUPLE single tuple from larger resultset

resultErrorMessage

resultErrorMessage returns the error message associated with the command or an empty string if there was no error.

errMsg=pgmexec('resultErrorMessage',pgResult)

INPUT:

regular:

OUTPUT:

Normally, execution commands such as exec throw an exception on errors. This command is left for backward compatibility.

clear

clear frees the storage associated with a PGresult.

com.allied.pgmex.pgmexec('clear',pgResult)

INPUT:

regular:

Every command result should be freed via clear when it is no longer needed.

Retrieving Query Result

nTuples

nTuples returns the number of rows (tuples) in the query result.

nTuples=com.allied.pgmex.pgmexec('nTuples',pgResult)

INPUT:

regular:

OUTPUT:

nFields

nFields returns the number of columns (fields) in each row of the query result.

nFields=com.allied.pgmex.pgmexec('nFields',pgResult)

INPUT:

regular:

OUTPUT:

fName

fName returns the column name associated with the given column number.

fName=com.allied.pgmex.pgmexec('fName',pgResult,colInd)

INPUT:

regular:

OUTPUT:

fNumber

fNumber returns the column number associated with the given column name.

fnum=com.allied.pgmex.pgmexec('fNumber',pgResult,colName)

INPUT:

regular:

OUTPUT:

pgResult=com.allied.pgmex.pgmexec('exec',dbConn,'SELECT 1 AS FOO, 2 AS "BAR"');
%
name=com.allied.pgmex.pgmexec('fName',pgResult,0)             % foo
name=com.allied.pgmex.pgmexec('fName',pgResult,1)             % BAR
ind=com.allied.pgmex.pgmexec('fNumber',pgResult,'FOO')        % 0
ind=com.allied.pgmex.pgmexec('fNumber',pgResult,'foo')        % 0
ind=com.allied.pgmex.pgmexec('fNumber',pgResult,'BAR')        % -1
ind=com.allied.pgmex.pgmexec('fNumber',pgResult,'"BAR"')      % 1

fSize

fSize returns the size in bytes of the column associated with the given column number.

fSize=com.allied.pgmex.pgmexec('fSize',pgResult,colInd)

INPUT:

regular:

OUTPUT:

DESCRIPTION:

This function returns the size of the server’s internal representation of the data type. Accordingly, it is not really very useful to end-users.

binaryTuples

binaryTuples returns true if the PGresult contains binary data and false if it contains text data.

isBinary=com.allied.pgmex.pgmexec('binaryTuples',pgResult)

INPUT:

regular:

OUTPUT:

getValue

getValue returns a single field value of one row of a PGresult. 

valueStr=com.allied.pgmex.pgmexec('getValue',pgResult,rowInd,colInd)

INPUT:

regular:

OUTPUT:

DESCRIPTION:

getIsNull

getIsNull tests a field for a NULL value.

isNull=com.allied.pgmex.pgmexec('getIsNull',pgResult,rowInd,colInd)

INPUT:

regular:

OUTPUT:

getLength

getLength returns the actual length of a field value in bytes.

length=com.allied.pgmex.pgmexec('getLength',pgResult,rowInd,colInd)

INPUT:

regular:

OUTPUT:

DESCRIPTION:

This function returns the length of the data pointed by PQgetValue (but not necessarily by the MEX functions getValue or getf). It is not likely to be useful to end users and is left for backward-compatibility.

getf

getf gets one or more field values from PGresult using a scanf-style interface.

Select all data (i.e. select *) from a table with 2 tuples and the following columns:

( event_id bigint, t_date date, metric_name character(1), metric_value double precision[])

 import com.allied.pgmex.pgmexec;
 [SId,SDate,SName,SValue]=pgmexec('getf',pgResult,'%int8 #date %varchar %float8[]',...
     0,'t_date',1,2);
% SId.valueVec={int64(5645334);int64(645647)}
% SId.isNullVec=[false;false]
% SId.isValueNullVec=[false;false]
%
% SDate = struct with fields:
%          valueVec: [2x1 double]
%         isNullVec: [2x1 logical]
%    isValueNullVec: [2x1 logical]
%
dateVec=SDate.valueVec
%
% dateVec =[736666;736667]
%
% SName = struct with fields:
%           valueVec: {2x1 cell}
%          isNullVec: [2x1 logical]
%     isValueNullVec: [2x1 logical]
%
nameCVec=SName.valueVec
% nameCVec ={'Experiment #1','Experiment #2'}
%
% empty value for the second extracted metric:
% SValue.valueVec={[0 2.4;-2 Inf;0 NaN];[]}
% some elements in the value matrix for the first metric are null:
% SValue.isNullVec={[true false;false false;true false];logical.empty(0,0)}
% the value extracted for the second metrics is empty because it is NULL:
% SValue.isValueNullVec=[false;true]

Note that in this example in the first two output structures the 'valueVec’ field is a column vector (int64 and double respectively), while in the last two structures 'valueVec’ is a cell array. 'isNullVec’ is a logical column vector in the first three structures and a cell array of logical arrays in the last structure.

INPUT:

regular:

properties:

OUTPUT:

DESCRIPTION:

The structures SFieldValue1,…,SFieldValueN have the following fields:

If isUniformOutput is false, then in any case both valueVec and isNullVec are cell[nTuples,1], such that elements of valueVec contain values of corresponding fields while cells of isNullVec are logical matrices determinining whether these values are NULL or not. If isUniformOutput is true, then the format described above for both valueVec and isNullVec is valid only for fields with non-scalar values, for fields with scalar values valueVec is plain (not cell) array of size [nTuples,1] of these values while isNullVec is logical[nTuples,1]. Thus, if isUniformOutput is true, then values of valueVec and isNullVec obtained in general case are concatenated in simple column vectors. isValueNullVec indicates, for each tuple, whether the entire value, be it a scalar or an array, is NULL.

Retrieving Other Result Info

cmdStatus

cmdStatus returns the command status tag from the SQL command that generated the PGresult.

statusStr=com.allied.pgmex.pgmexec('cmdStatus',pgResult)

INPUT:

regular:

OUTPUT:

cmdTuples

cmdTuples returns the number of rows affected by the SQL command.

tuplesStr=com.allied.pgmex.pgmexec('cmdTuples',pgResult)

INPUT:

regular:

OUTPUT:

Evaluate

We offer a fully functional evaluation version, with a few limitations to an amount of data transferred to DB, a number of fields extracted and a number of sequential calls.

Installation

Installation process is as easy as unzipping the archive and adding the root folder with the archive content to Matlab path. However, you need to install a few prerequisites prior to using PgMex.

Prerequisites

Purchase

Obtaining a license is fast and easy. We accept purchases through Share-it that presents a number of payment options like credit card, online cheque, and paypal. If you do not wish to buy through the Share-it portal, you could make a wire transfer to our bank account. For more information about this, or for any other inquiries regarding the payment process, please contact us at sales@pgmex.alliedtesting.com

Once you click on the “Buy Now” button below, you will be redirected to Share-it. Immediately after your payment has been completed, you will receive an e-mail containing your license key and download instructions.

By purchasing and using PgMex for developing your own Matlab applications, you agree to be bound by the terms and conditions of the license agreement found here. Please review the agreement carefully before making any decision to buy. For inquiries regarding licensing, you can send us an e-mail. Licensing

The following licenses are available:

Each license includes 6 months of free updates and technical support via our developer’s forum and/or e-mails. After the initial six-month period, a renewal license may be purchased which includes product updates and support for one more year. Please note that there are no royalty/deployment fees or any additional costs other than the ones described here.

After your license has expired, you can continue to develop and release applications using our library, however, you will not be able to download new updates or receive further support from us.

Binary code

Number of developers Price/license Total Price
1-3 $250 $250-$750 Buy Now
4 and more - site license $900 Buy Now

Annual Updates - for customers already owning a license

Number of developers Price/license Total Price
1-3 $150 $150-$450 Buy Now
4 and more - site license $500 Buy Now

Source code

Please contact us at sales@pgmex.alliedtesting.com.

Number of developers Price/license Total Price
1 $1900 $1900
2 $1500 $3000
3 $1300 $3900
4 $1200 $4800
5 or more site license $5200

Academic License

Please fill the Contact Form below to send us a message.

Benchmarks

We ran a set of performance comparison tests between Matlab Database Toolbox (working via a direct JDBC connection) and PgMex library providing a connection to PostgreSQL via libpq library. The tests covered various kinds of data types and data volumes.

Inserting data

The data insertion benchmarking was performed for datainsert (and fastinsert for the most simple case of scalar numericals) from Matlab Database Toolbox and for batchParamExec from PgMex library. For more details read our paper with full benchmarking results for data insertion. Endpoint of each graph corresponds to a certain maximum data volume passed into the database by the corresponding method without any error. A data volume greater than that maximum (specific for each method) causes “out of Java heap memory” problem (Java heap size for each experiment is specified at the top of each figure).

Scalar numeric data

Scalar numeric data along with timestamps

Arrays

Retrieving data

When it comes to retrieving data from PostgreSQL in Matlab, PgMex library also outperforms Matlab Database Toolbox for various kinds of data types and sizes. More specifically for scalar types our exec and getf methods are 5-7 times faster than exec and fetch methods from Matlab Database Toolbox. For more details look at our paper with full benchmarking results for retrieval of scalar data. On the figures below we refer to the mentioned retrieval methods as getf and fetch just for simplicity. Each graph relates to one of three formats to return data available for Matlab Database Toolbox: ‘numeric’, 'cellarray’ and 'structure’. We convert data retrieved by PgMex to each of these formats to make a comparison valid. Endpoint of each graph corresponds to a certain maximum data volume successfully retrieved from the database by the corresponding method without any error. Retrieval of a data volume greater than that maximum (specific for each method) causes “out of Java heap memory” problem (Java heap size for each experiment is specified at the top of each figure).

Scalar numeric data

Scalar numeric data along with timestamps

Performance test pack

All the performance testing results above are reproducible via our open source PgMex performance test pack.

Support

Please direct all technical questions to support@pgmex.alliedtesting.com

Alternatively, you can use the developer’s forum to post your questions by following the link below:

Allied Testing’s Developer Forum

We strive to reply to your questions as soon as possible. Usually, a response in the same or next business day is provided.

About us

PgMex is developed in Financial Modelling and Analytics Department of Allied Testing by

About Allied Testing

Allied Testing is an outsourced services supplier, specialised in testing securities trading systems, financial engineering and financial systems software development. Our deep domain expertise, global footprint and the superb technical capabilities of our staff have enabled Allied Testing to become a quality assurance partner of choice to such companies as Thomson Reuters, Barclays Capital, Renaissance Capital, HSBC and Morgan.

Services

► Core QA and Testing

  • Intelligent manual testing (functional and regression)
  • Trading functionality (valid/invalid orders, order status)
  • Market data (data feeds, ticker plants, charts, etc.)
  • Balances, positions, transactions
  • End-to-end trading logic (GUI and back-end)
  • Test data preparation
  • GUI-based automation
  • Back-end automation
  • Proprietary automation tools & frameworks
  • Protocol level automation (FIX, GL, FidessaOpenAccess)
  • Complex bespoke test harnesses
  • User Acceptance Testing (UAT) Specialized QA and Testing

► Specialized QA and Testing

  • SOR and algorithmic-trading systems testing
  • Behavioural testing
  • Three levels of market simulation
  • Back testing of trading strategies
  • Performance, capacity and latency testing of trading platforms
  • Sophisticated test harnesses
  • Connectivity testing
  • Front, middle and back office testing
  • MTF and Dark Pool testing
  • Automation R&D
  • OATS/OTS, SOX, SEC, MiFID compliance testing Mobile Application Testing Services

► Mobile Application Testing Services

  • Testing services
  • Testing software functionality
  • Testing integration with internal and external systems
  • Testing application deployment
  • Testing process management
  • Managing distribution for testing and deployment
  • Design help for testing Financial Engineering

► Financial Engineering

  • In-depth financial testing
  • Trading algorithms and decision-making
  • Forecasting and descriptive modeling
  • Trading and performance analytics
  • Data mining and strategy testing QA Consulting

► QA Consulting

  • QA strategy development
  • Automation strategy
  • QA process audit
  • Test coverage analysis
  • Business analysis Support Services

► Support Services

  • Test environment maintenance and support
  • Release management
  • First and second-level support
  • Operability testing
  • Documentation
  • Systems administration

Clients

A deep domain expertise, global footprint and the superb technical capabilities of its staff have enabled Allied Testing to become a quality assurance partner of choice to the most important players on global finance and trading industry.

Representative Clients:

► Barclays Capital

Barclays Capital is the investment banking division of Barclays Bank PLC. It provides large corporate, government and institutional clients with a full spectrum of solutions to their strategic advisory, financing and risk management needs.

► Cheuvreux

Cheuvreux is a leading European equity broker and a fully-owned subsidiary of Calyon

► Deloitte Touche Tohmatsu

Deloitte Touche Tohmatsu is one of the largest professional services organizations in the world, with over 165,000 employees in 140 countries.

► Equiduct

Equiduct is a pan-European MiFID compliant platform operated by Börse Berlin AG that allows investment firms to provide the order-by-order best execution to their clients.

► EquiTec Group LLC

Equitec Group, LLC provides liquidity in listed securities. It trades primary and derivative instruments in equity, index, financial, and structured products. The company also trades on electronic exchanges in Europe. Equitec Group, LLC is based in Chicago, Illinois with additional offices in New York, San Francisco, Philadelphia, and London.

► Fidessa group

Fidessa group is the leading supplier of multi-asset trading, portfolio analysis, decision support, compliance, market data and connectivity solutions for firms involved in trading the world’s financial markets. Fidessa’s products and services are built on the simple vision of making it easier to buy, sell and own financial assets of all types on a global basis.

► Fujitsu

Fujitsu is a leading provider of IT-based business solutions for the global marketplace. With approximately 175,000 employees supporting customers in 70 countries, Fujitsu combines a worldwide corps of systems and services experts with highly reliable computing and communications products and advanced microelectronics to deliver added value to customers.

► HSBC Headquartered in London

HSBC is one of the largest banking and financial services organisations in the world. HSBC’s international network comprises around 9,500 offices in 86 countries and territories in Europe, the Asia-Pacific region, the Americas, the Middle East and Africa.

► Infinium Group

Infinium Group is a leading independent electronic trading firm specializing in alternative quantitative and arbitrage strategies.

► JPMorgan Chase & Co.

JPMorgan Chase & Co. is a leading global financial services firm with assets of $2.2 trillion and operations in more than 60 countries. The firm is a leader in investment banking, financial services for consumers, small business and commercial banking, financial transaction processing, asset management, and private equity.

► London Stock Exchange

The London Stock Exchange is at the heart of the global financial market and is home to some of the largest, most successful and dynamic companies in the world. The Exchange has built on a long history of integrity, expertise and market knowledge to become the world’s most international stock exchange. In October 2007 the Exchange merged with Borsa Italiana, creating Europe’s leading diversified exchange business.

► Questrade

Questrade Inc., headquartered in Toronto, provides Canadians with high-speed, direct access trading in the U.S. and Canadian stock and options markets as well as FX trading. Since its inception in 1999, the company has grown and diversified, and is currently ranked as Canada’s fastest-growing online brokerage.

► Quod Financial

Quod Financial is a technology company delivering multi-asset and cross-asset adaptive execution technology into the financial trading community.

► Renaissance Capital

Renaissance Capital, Russia’s leading investment bank, delivers innovative financial and investment solutions to government, corporate and institutional clients in high-opportunity emerging markets around the world.

► TD Ameritrade

TD Ameritrade is one of the world’s largest brokers providing brokerage services since 1975. Services offered include common and preferred stocks, futures, ETFs, option trades, mutual funds, fixed income, margin lending, and cash management services.

► Thomson Reuters

Thomson Reuters is the world’s leading source of intelligent information for businesses and professionals.

► ITI Asset Management

ITI Asset Management (ITIAM) is a proprietary quantitative investment company that specialises in investing in Exchange Traded Funds. ITIAM is based in the City of London, United Kingdom.

Contact us

View @AlliedTesting

Support

support@pgmex.alliedtesting.com

Sales

sales@pgmex.alliedtesting.com

Information

info@pgmex.alliedtesting.com

matlab