PostgreSql Routines

Neben den Functions (PostgreSQL Routine) werden seit PostgreSQL 11 auch Stored Procedures von der Datenbank unterstützt, um serverseitige Programmierung zu ermöglichen. Diese beiden Routine-Typen haben unterschiedliche Eigenschaften und Funktionen:

Vergleich von Stored Procedures und Functions
	Stored Procedures	Functions
OUT Parameter	+	-
Rückgabewert	-	+
Kann in Abfragen genutzt werden	-	+
Unterstützt Transaktionen	+	-

Diese Eigenschaften erfordern unterschiedliche Schnittstellen mit dem TwinCAT Database Server. Wie bei anderen unterstützten Datenbanken, werden Stored Procedures über den Funktionsbaustein FB_SQLStoredProcedureEvt ausgeführt. Functions können in SQL-Befehle eingebaut werden, welche über den FB_PLCDBCmdEvt oder den FB_SQLCommandEvt aufgerufen werden.

PostgreSQL nutzt für die Rückgabe von Datensätzen der Routinen „RefCursor“. Der TwinCAT Database Server wertet diese „RefCursor“ automatisch aus und gibt den darin referenzierten Datensatz zurück. Die Auflösung multipler „RefCursor“ ist nicht möglich.

Aufruf einer Stored Procedure

Procedures können über den FB_SQLStoredProcedureEvt aufgerufen werden.

Beispiel (SQL)

SQL Script zum Erstellen einer 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$;

Falls die Prozedur einen (oder mehrere) „RefCursor“ als Ausgangsparameter definiert, wird dieser (bzw. der erste) automatisch interpretiert und die resultierenden Datensätze für den FB_SQLResultEvt in den Zwischenspeicher abgelegt. Der Datentyp „RefCursor“ wird vom TwinCAT Database Server wie ein String behandelt.

Beispiel (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

Der FB_SQLStoredProcedureEvt nutzt die zuvor mit FB_SQLDatabaseEvt.CreateSP() verknüpfte Stored Procedure

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

Nachfolgend kann der FB_SQLResultEvt genutzt werden, um die Daten auszulesen.

Aufruf einer Function

Functions können innerhalb von SQL Befehlen aufgerufen werden.

Beispiel (SQL)

SQL Script zum Erstellen einer 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$;

Zum Aufrufen der Function wird folgender SQL Befehl verwendet:

SELECT "public"."F_getLastData"();

Der Aufruf selbst gibt einen „RefCursor“ zurück. Dieser wird vom TwinCAT Database Server automatisch interpretiert.

Beispiel (TwinCAT 3 in ST)

Der FB_SQLCommandEvt nutzt das vom FB_SQLDatabaseEvt.CreateCmd() erstellte Kommando.

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

Nachfolgend kann der FB_SQLResultEvt genutzt werden, um die Datensätze auszulesen.

	Es wird empfohlen diesen Programmcode in einer State Machine zu nutzen.

Download: TF6420_BestPractise_PostgreSgl_Routines.zip