Erstellen und Verwenden einer Funktion in Azure Database for PostgreSQL

Abgeschlossen

Sie haben bereits erfahren, dass PostgreSQL verschiedene Sprachen unterstützt. Funktionen können in vier verschiedene Typen kategorisiert werden:

• In SQL geschriebene Funktionen
• In einer unterstützten prozeduralen Sprache wie PL.pgSQL geschriebene Funktionen
• Interne Funktionen
• Funktionen der Programmiersprache C

Darüber hinaus kann der Zweck der Funktion auch als flüchtig, unveränderlich oder stabil kategorisiert werden.

Eine flüchtige Funktion (Standardeinstellung) kann die Datenbank ändern und gibt nicht unbedingt jedes Mal das gleiche Ergebnis zurück, selbst wenn dieselben Eingabeparameter verwendet werden. Daher muss diese Funktion bei jedem Aufruf neu ausgewertet werden.

Eine stabile Funktion kann die Datenbank nicht ändern und liefert immer dasselbe Ergebnis, wenn sie mit den gleichen Argumenten übergeben und innerhalb der gleichen Anweisung ausgeführt wird. Wenn diese Funktion mehrmals aufgerufen wird, kann der Abfrageoptimierer die Ergebnisse des letzten Aufrufs verwenden.

Eine unveränderliche Funktion kann die Datenbank nicht ändern und gibt unabhängig von der Abfrage, die sie aufruft, dieselben Ergebnisse zurück, wenn ihr die gleichen Argumente übergeben werden.

Die Flüchtigkeit einer Funktion hat großen Einfluss auf die Effizienz, mit der der Abfrageoptimierer sie behandelt.

Erstellen einer Funktion

Eine Funktion gibt einen einzelnen Wert zurück und kann innerhalb einer SELECT-Anweisung verwendet werden.

Die Syntax zum Erstellen einer Funktion ist wie folgt:

CREATE [OR REPLACE] FUNCTION
myfunction ([inputparam] type {default})
RETURNS returntype AS
$$
SQL body
$$
LANGUAGE 'language_name';
CREATE FUNCTION

Wie bei gespeicherten Prozeduren wird das Symbol $$ verwendet, um die Zeichenfolge zu beginnen und zu beenden.

Funktionen akzeptieren die folgenden Parameter:

• name: kann optional den Namen des Schemas enthalten.
• argmode: der Modus des Arguments. Kann IN, OUT, INOUT oder VARIADIC sein. Standard ist IN. VARDIAC steht für eine nicht definierte Anzahl von Eingabeargumenten desselben Typs, auf die OUT-Argumente folgen. Die Argumente OUT und INOUT können nicht zusammen mit den Notationen für RETURNS TABLE verwendet werden.
• argname: Argumentname.
• argtype: Typ der Argumentdaten. Es kann sich um Basis-, zusammengesetzte oder Domänentypen handeln oder um einen Verweis auf einen Tabellenspaltentyp. Der Typ der Spalte wird als table_name.column_name%TYPE geschrieben. Mit diesem Datentyp können Sie eine Funktion von Änderungen der Tabellendefinition unabhängig gestalten.
• t_expr: ein Standardwert (desselben Typs), wenn der Parameter nicht angegeben ist. Nur die Parameter IN und INOUT haben einen Standardwert. Eingabeparameter, die auf einen Parameter mit einem Standardwert folgen, müssen ebenfalls Standardwerte haben.
• rettype: Der Rückgabedatentyp, der ein Basis-, zusammengesetzter oder Domänentyp sein kann oder auf einen Spaltentyp der Tabelle verweist. Wenn die Funktion keinen Wert zurückgibt, geben Sie den Rückgabetyp als „void“ an. Wenn die Parameter OUT oder INOUT vorhanden sind, kann die Klausel RETURNS weggelassen werden. Falls vorhanden, muss sie mit dem Ergebnistyp übereinstimmen, der durch die Ausgabeparameter impliziert wird: RECORD, wenn es mehrere Ausgabeparameter gibt, oder der gleiche Typ wie der einzelne Ausgabeparameter. Der Modifizierer SETOF gibt an, dass die Funktion mehrere Elemente und nicht nur ein einzelnes Element zurückgibt. Auf den Typ einer Spalte wird durch Schreiben von table_name verwiesen.
• column_name: der Name einer Ausgabespalte in der RETURNS TABLE-Syntax. Dadurch wird ein benannter OUT-Parameter deklariert, mit der Ausnahme, dass RETURNS TABLE auch RETURNS SETOF impliziert.
• column_type: der Datentyp einer Ausgabespalte in der RETURNS TABLE-Syntax.
• lang_name: die Sprache, in der die Prozedur geschrieben wurde. Der Standardwert ist „sql“, wenn sql_body angegeben ist. Kann „sql“, „c“, „internal“ oder der Name einer benutzerdefinierten Prozedursprache sein, z. B. plpgsql.

Geben Sie dem Abfrageoptimierer die Schlüsselwörter IMMUTABLE, STABLE oder VOLATILE als Hinweis auf die Funktion an. VOLATILE ist die Standardeinstellung.

Aufrufen einer Funktion

Eine Funktion kann in einer Abfrage verwendet werden, wenn Sie ihr alle relevanten Parameter übergeben. Beispiel:

SELECT myfunction(3), CatID, CatName
    FROM myCats

Integrierte Funktionen

PostgreSQL bietet viele integrierte Funktionen, die Sie in Ihren Abfragen verwenden können. Diese eignen sich für Vergleiche, das Aggregieren von Daten, mathematische Funktionen usw. In der Onlinedokumentation finden Sie eine vollständige Liste der PostgreSQL-Funktionen.

Ein Beispiel einer integrierten Zeichenfolgenfunktion ist substring.

substring (*string* text [ FROM *start* integer ] [ FOR *count* integer ] ) → text

Diese Funktion akzeptiert drei Eingabeparameter:

• String (Typ „Text“)
• FROM start (Typ „Integer“)
• FOR Anzahl (Typ „Integer“)

„substring“ gibt einen Teil des Eingabetexts zurück, beginnend mit dem Zeichen start und endend nach count Zeichen. Zum Beispiel:

substring('Thomas' from 2 for 3) → hom
substring('Thomas' from 3) → omas
substring('Thomas' for 2) → Th

Diese Funktion ist identisch mit substr:

substr ( *string* text, *start* integer [, *count* integer ] ) → text
substr('alphabet', 3) → phabet
substr('alphabet', 3, 2) → ph

Hinweis

Wenn Sie mit Funktionen vertraut sind, werden Sie bemerkt haben, dass in der ersten Version Schlüsselwörter statt Kommas zur Trennung der Argumente verwendet werden. PostgreSQL stellt beide Versionen dieser Funktionen bereit.