PostgreSql routines

In addition to the functions (PostgreSQL routine), from PostgreSQL 11 the database also supports stored procedures to enable server-side programming. These two routine types have different properties and functions:

Comparison of stored procedures and functions
	Stored Procedures	Functions
OUT parameters	+	-
Return value	-	+
Can be used in queries	-	+
Supports transactions	+	-

These properties require different interfaces with the TwinCAT Database Server. As with other supported databases, Stored Procedures are executed using the FB_SQLStoredProcedureEvt function block. Functions can be integrated in SQL commands, which are called via FB_PLCDBCmdEvt or FB_SQLCommandEvt.

PostgreSQL uses "RefCursor" for returning data sets of the routines. The TwinCAT Database Server automatically evaluates these "RefCursor" and returns the data set referenced in them. It is not possible to resolve multiple "RefCursor".

Calling a Stored Procedure

Procedures can be called via the FB_SQLStoredProcedureEvt.

Example (SQL)

SQL script for creating a procedure:

CREATE OR REPLACE PROCEDURE "public"."SP_getLastData"(
    INOUT result_data refcursor)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    open result_data for SELECT * FROM "myTable_Double" LIMIT 10;
END
$BODY$;

If the procedure defines one (or more) "RefCursor" as output parameter(s), this (or the first) is automatically interpreted and the resulting data sets are stored in the buffer for the FB_SQLResultEvt. The data type "RefCursor" is treated like a string by the TwinCAT Database Server.

Example (TwinCAT 3 in ST)

VAR
    fbSqlDatabase   : FB_SQLDatabaseEvt(sNetID := '', tTimeout := T#5S);
    ParaInfo        : ST_SQLSPParameter; 
END_VAR

ParaInfo.sParameterName     := '@result_data';
ParaInfo.eParameterType     := E_SPParameterType.InputOutput;
ParaInfo.eParameterDataType := E_ColumnType.RefCursor; // 19
ParaInfo.nParameterSize     := 81;

IF fbSQLDatabase.CreateSP('"public"."SP_getLastData"', ADR(ParaInfo), SIZEOF(ParaInfo), ADR(fbSQLStoredProcedure)) THEN
    IF fbSQLDatabase.bError THEN
        nState:=255;
    ELSE
        nState:= nState+1;
    END_IF
END_IF

The FB_SQLStoredProcedureEvt uses the Stored Procedure previously linked with FB_SQLDatabaseEvt.CreateSP()

VAR
    fbSQLStoredProcedure : FB_SQLStoredProcedureEvt(sNetID:='', tTimeout := T#5S);
    sRefCursor            : STRING; 
    tcMessage            : I_TcMessage;
END_VAR

IF fbSQLStoredProcedure.ExecuteDataReturn(pParameterStrc := ADR(sRefCursor), cbParameterStrc:= SIZEOF(sRefCursor), pSQLDBResult := ADR(fbSqlResult)) THEN
    IF fbSQLStoredProcedure.bError THEN
        tcMessage := fbSQLStoredProcedure.ipTcResult;
        nState := 255; 
    ELSE
        nState := nState+1; 
    END_IF    
END_IF

FB_SQLResultEvt can then be used to read the data.

Calling a function

Functions can be called within SQL commands.

Example (SQL)

SQL script for creating a function:

CREATE OR REPLACE FUNCTION "public"."F_getLastData"()
    RETURNS refcursor
    LANGUAGE 'plpgsql'
AS $BODY$
DECLARE result_data refcursor;
BEGIN
    open result_data for SELECT * FROM "myTable_Double" ORDER BY "ID" DESC LIMIT 10;
    return result_data;
END;$BODY$;

The following SQL command is used to call the function:

SELECT "public"."F_getLastData"();

The call itself returns a "RefCursor". This is automatically interpreted by the TwinCAT Database Server.

Example (TwinCAT 3 in ST)

The FB_SQLCommandEvt uses the command created by FB_SQLDatabaseEvt.CreateCmd().

VAR
    fbSqlCommand : FB_SQLCommandEvt(sNetID := '', tTimeout := T#5S);
    tcMessage    : I_TcMessage;
END_VAR

sCmd := 'SELECT "public"."getLastData"();';

// call sql command
IF fbSQLCommand.ExecuteDataReturn(ADR(sCmd), SIZEOF(sCmd), ADR(fbSqlResult)) THEN
    IF fbSQLCommand.bError THEN
        tcMessage := fbSQLCommand.ipTcResult;
        nState := 255; // error state
    ELSE
        nState := nState+1; 
    END_IF
END_IF

FB_SQLResultEvt can then be used to read the data sets.

	It is advisable to use this program code in a State Machine.

Download: TF6420_BestPractise_PostgreSql_Routines.zip