So erstellen Sie postgresql-benutzerdefinierte Funktionen

„Wenn es erforderlich ist, viele SQL-Anweisungen mehrmals auszuführen, ist es besser, mit diesen SQL-Anweisungen eine benutzerdefinierte PostgreSQL-Funktion oder -prozedur zu schreiben. Es entfernt den doppelten Code und wiederverwendet den Code, indem die Funktion aus verschiedenen Anwendungen aufgerufen wird. In diesem Tutorial wurde die Erstellung und Verwendung verschiedener Arten von postgresql-benutzerdefinierten Funktionen erstellt und verwendet.”

Syntax

Die Syntax der PostgreSQL -Funktion ist unten angegeben.

Funktionsfunktion erstellen oder ersetzen Sie Funktionsfunktion_Name (Argument_List)
Return return_type als $ variable_name $
ERKLÄREN
Erklärung;
[…]
START

[…]
Return variable_name | Wert
ENDE;
Sprache PLPGSQL;

• Das Erstellen von Schlüsselwort wird verwendet, um eine neue benutzerdefinierte Funktion zu erstellen und das Schlüsselwort zu ersetzen, um die vorhandene Funktion zu ändern.
• Der Name der Funktion wird durch den Funktionsname definiert, und die Funktion kann ein oder mehrere Argumente annehmen. Drei Arten von Argumenten können in der Funktion verwendet werden. Diese sind IN, AUS, Und In Out. Das Standardargument der Funktion ist IN Streit. Es wird verwendet, um den Wert in die Funktion zu übergeben, und kann in der Funktion nicht geändert werden. Der AUS Das Argument wird verwendet, um den Wert aus der Funktion zurückzugeben. Der In Out Argument können verwendet werden, um den Wert an die Funktion zu übergeben und den Wert aus der Funktion zurückzugeben. Es kann in der Funktion wie nachgewiesen werden das raus Streit.
• Der Datentyp der Variablen, der von der Funktion zurückgegeben wird.
• Der Körper der Funktion beginnt mit dem Deklar -Schlüsselwort, bei dem die erforderlichen Variablen deklariert werden. Als nächstes werden die erforderlichen SQL -Anweisungen innerhalb der Beginn- und Endblöcke definiert.
• Der Name der prozeduralen Sprache wird nach dem Sprachschlüsselwort definiert, in dem die Funktion ausgeführt wird.

Voraussetzungen

A. Installieren Sie die Postgresql

Sie müssen die neueste Version von PostgreSQL -Paketen auf dem Linux -Betriebssystem installieren, bevor Sie die in diesem Tutorial gezeigten SQL -Anweisungen ausführen. Führen Sie die folgenden Befehle aus, um die Postgresql zu installieren und zu starten:

$ sudo apt-get -y install postgresql postgresql-contrib
$ sudo systemctl starten postgresql.Service

Führen Sie den folgenden Befehl aus, um sich bei PostgreSQL mit Root -Privilegien anzumelden.

$ sudo -u postgres psql

B. Sie können eine Datenbank mit dem Namen erstellen "TestDB" Durch Ausführen der folgenden SQL -Anweisung.

# Datenbank testdb erstellen;

Wenn Sie einen Tisch in die erstellen möchten testdb Datenbank, dann müssen Sie tippen "\C" Um die Datenbank zu ändern. In diesem Tutorial habe ich die Datenbank und die Tabelle nicht geändert, und alle Funktionen wurden in der Standarddatenbank mit dem Namen erstellt Postgres.

C. Erstellen Sie eine Tabelle mit dem Namen Produkte mit vier Feldern und fügen Sie vier Datensätze in die Tabelle ein, um die benutzerdefinierten Funktionen zu testen, die später in diesem Tutorial erstellt werden.

# Tabellenprodukte erstellen (
id serielle Primärschlüssel,
Nennen Sie Varchar (30),
Typ varchar (20),
Preisgeld);

D. Fügen Sie vier Datensätze in die Tabelle ein.

# In Produkte einfügen (Name, Typ, Preis)
Werte ('Samsung A40', 'Mobile', 300),
('A4', 'Maus', 20),
('Sony 42 ",' TV ', 1000),
('Samsung 3TB', 'HDD', 600);

Verschiedene Beispiele für postgresql-benutzerdefinierte Funktionen

Die Verwendung von benutzerdefinierten Funktionen mit Argument und ohne Argument in PostgreSQL wurde unten unter Verwendung mehrerer Beispiele gezeigt.

Beispiel 1: Benutzerdefinierte Funktion ohne Argument und Rückgabewert

Führen Sie die folgende SQL -Anweisung aus, um eine Funktion mit dem Namen zu erstellen Funktion1 () ohne Argument, und die Funktion gibt nichts zurück. Die Funktion aktualisiert den Datensatz der Produkttabelle, in der der ID -Wert 3 nach der Ausführung ist.

# Funktionsfunktion erstellen oder ersetzen Sie die Funktion1 ()
Kehrt leer als
$$
START
--Aktualisieren Sie den Preiswert des dritten Datensatzes
Aktualisieren Sie Produkte Set Preis = 1500 wobei ID = 3;
ENDE;
$$
Sprache 'PLPGSQL';

Die folgende Ausgabe wird angezeigt, wenn die Funktion erfolgreich in der erstellt wird Postgres Datenbank.

Führen Sie die folgende SQL -Anweisung aus, um alle Aufzeichnungen des Produkte Tabelle vor der Ausführung Funktion1 ().

# Aus Produkten auswählen;

Die folgende Ausgabe zeigt die eingefügten Datensätze der Produkte Tisch.

Führen Sie die folgende SQL -Anweisung aus, um die Funktion auszuführen.

# Function1 () auswählen;

Die folgende Ausgabe zeigt, dass eine Zeile durch Aufrufen der Funktion beeinflusst wird Funktion1 (). Der Wert des Preisfelds der Tabelle wird aktualisiert, wenn der ID -Wert 3 beträgt.

Führen Sie die folgende SQL -Anweisung aus, um alle Aufzeichnungen des Produkte Tabelle nach der Ausführung Funktion1 ().

# Aus Produkten auswählen;

Die folgende Ausgabe zeigt, dass der Preiswert der Produkttabelle dort aktualisiert wurde, wo der ID -Wert 3 beträgt und jetzt 1500 ist.

Beispiel 2: Benutzerdefinierte Funktion mit in Argument

Das Standardargument der Funktion ist in Argument. Führen Sie die folgende SQL -Anweisung aus, um eine Funktion mit dem Namen zu erstellen Funktion2 () mit zwei Argumenten, und die Funktion gibt nichts zurück. Die Funktion fügt einen neuen Datensatz in die ein Produkte Tabelle mit den Argumentwerten.

# Function Function2 erstellen oder ersetzen
Kehrt leer als
$$
START
--Fügen Sie einen neuen Datensatz ein
In Produkte einfügen (Name, Typ, Preis)
Werte (Name, Typ, Preis);
ENDE;
$$
Sprache 'PLPGSQL';

Die folgende Ausgabe wird angezeigt, wenn die Funktion erfolgreich in der erstellt wird Postgres Datenbank.

Führen Sie die folgende SQL -Anweisung aus, um die Funktion Drei in Argumentwerten auszuführen. Dies sind "iPhone", "Mobile" und 2000.

# Function2 ('iPhone', 'Mobile', 2000);

Die folgende Ausgabe zeigt, dass eine Zeile durch Aufrufen der Funktion beeinflusst wird Funktion2 (), und ein neuer Datensatz wird in die Tabelle eingefügt.

Führen Sie die folgende SQL -Anweisung aus, um alle Aufzeichnungen des Produkte Tabelle nach der Ausführung Funktion2 ().

# Aus Produkten auswählen;

Die folgende Ausgabe zeigt, dass ein neuer Datensatz in die eingefügt wurde Produkte Tisch.

Beispiel-3: Benutzerdefinierte Funktion ohne Argument

Das Out -Argument wird verwendet, um den Wert aus der Funktion zurückzugeben. Führen Sie die folgende SQL -Anweisung aus, um eine Funktion mit dem Namen zu erstellen Funktion3 () Mit einem Ausgangsargument des Geldtyps gibt die Funktion Daten vom Geldtyp zurück. Die Funktion gibt den zugewiesenen Wert des Out -Arguments zurück.

# Funktionsfunktion erstellen oder ersetzen 3 (Preisausgabe)
Gibt Geld zurück als
$$
START
--Weisen Sie dem Argument einen Wert zu
Preis: = 1000;
ENDE;
$$
Sprache 'PLPGSQL';

Die folgende Ausgabe wird angezeigt, wenn die Funktion erfolgreich in der erstellt wird Postgres Datenbank.

Führen Sie die folgende SQL -Anweisung aus, um Datensätze aus der Produkttabelle auf der Grundlage des von Function3 zurückgegebenen Werts zu löschen.

# Aus Produkten löschen wobei Preis <= function3();

Die folgende Ausgabe zeigt, dass drei Zeilen durch Aufrufen der Funktion betroffen sind Funktion3 (). Der zurückgegebene Wert der Funktion beträgt 1000 Produkte Tisch, wo die Ausweis Werte sind 1, 2, Und 4.

Führen Sie die folgende SQL -Anweisung aus, um alle Aufzeichnungen des Produkte Tabelle nach der Ausführung Funktion3 (). Die Ausgabe zeigt, dass zwei Datensätze in der Tabelle vorhanden sind, in der die Preis Die Werte betragen mehr als 1000.

# Aus Produkten auswählen;

Die folgende Ausgabe zeigt, dass 3 Datensätze aus dem gelöscht wurden Produkte Tisch.

Beispiel-4: Benutzerdefinierte Funktion mit Inout-Argument

Das In -out -Argument wird in der Funktion verwendet, um Werte in die Funktion zu übergeben und Werte aus der Funktion zurückzugeben. Führen Sie die folgende SQL -Anweisung aus, um eine Funktion mit dem Namen zu erstellen Berechnung() mit einem In -out -Argument des Float -Typs und einem Argument des Ganzzahltyps. Die Funktion gibt Daten vom Float -Typ zurück. Die Funktion wird mit dem Verkaufsbetrag ausgeführt, der ein Float -Wert ist, und der Rabatt, der eine Ganzzahl sein wird. Der Verkaufsbetrag nach dem Rabatt wird basierend auf den Argumentwerten berechnet. Als nächstes wird der berechnete Verkaufsbetrag aus der Funktion mithilfe des Out -Arguments zurückgegeben.

# Erstellen oder Ersetzen von Funktion berechnen (
sales_amount in out float, Rabatt int)
Returns Float As
$$
START
--Berechnen Sie den Rabattbetrag
sales_amount: = sales_amount - (sales_amount*rabatt/100);
ENDE;
$$
Sprache 'PLPGSQL';

Die folgende Ausgabe wird angezeigt, wenn die Funktion erfolgreich in der erstellt wird Postgres Datenbank.

Führen Sie die folgende Erklärung aus, um die Funktion mit 10000 als Verkaufsbetrag und 5 als Rabattbetrag auszuführen

#Select berechnen (10000, 5) als Rabatt_Amount;

Der 5% von 10000 beträgt 500 und 10000-500 = 9500. Die folgende Ausgabe zeigt den Verkaufsbetrag nach einem Rabatt von 5%.

Beispiel-5: Benutzer-Dgegründet Funktion mit Die Bedingte Aussage

Führen Sie die folgende SQL -Anweisung aus, um eine Funktion mit dem Namen zu erstellen Search_product () mit einem Argument, und die Funktion gibt die Daten des VARCHAR -Typs zurück. In dieser Funktion wurde der Weg zur Deklaration der Funktionsvariablen und der Verwendung der bedingten Anweisung gezeigt. Die Funktionsvariable benannt Produktname wurde mit dem definiert Name Art der Produkte Tisch. Der Wert von in Argument, tp, wird mit dem Wert der abgestimmt Typ Feld der Produkte Tisch. Wenn in der Tabelle keine Übereinstimmung gefunden wird, wird mit der Nachricht ein Fehler angesprochen, „Produkttyp existiert nicht“. Wenn eine Übereinstimmung gefunden wird, dann der Wert der Name Das Feld dieses Produkttyps wird in der Funktionsvariablen gespeichert und die Variable wird aus der Funktion zurückgegeben.

# Function Search_Product (TP Varchar) erstellen oder ersetzen) oder ersetzen Sie es
Gibt Varchar as zurück
$$
ERKLÄREN
protous_name produkte.Nennen%Typ;
START
-- Suchen Sie den Produktnamen basierend auf dem Typ
Wählen Sie den Namen in product_name aus
Aus Produkten
Wo Produkte.Typ = tp;
Wenn nicht gefunden, dann
Erhöhen Sie den Produkttyp nicht.';
Ende wenn;
Return product_name;
ENDE;
$$
Sprache 'PLPGSQL';

Die folgende Ausgabe wird angezeigt, wenn die Funktion erfolgreich in der erstellt wird Postgres Datenbank.

Führen Sie die folgende SQL -Anweisung aus, um alle Datensätze aus der Produkttabelle zu lesen.

# Aus Produkten auswählen;

Führen Sie die folgende SQL -Anweisung aus, um die Funktionssuche_product () mit dem Produkttyp auszuführenHandy, Mobiltelefon”.

# Wählen Sie Search_Product ('Mobile');

Führen Sie die folgende SQL -Anweisung aus, um die Funktionssuche_product () mit dem Produkttyp auszuführenAC”.

# Wählen Sie Search_Product ('ac');

Die folgende Ausgabe zeigt, dass die Produkte Die Tabelle enthält zwei Datensätze von Typwerten, “Fernseher" Und "Handy, Mobiltelefon”. Die Ausgabe der ersten Ausführung der Funktion hat „iPhon“ zurückgegeben, da der Produkttyp „Mobile“ in der Tabelle vorhanden ist. Die Ausgabe der zweiten Ausführung der Funktion hat eine Fehlermeldung zurückgegeben, da der Produkttyp „AC“ in der Tabelle nicht vorhanden ist.

Beispiel-6: Benutzerdefinierte Funktion mit aggregierter Funktion

Jede von SQL unterstützte aggregierte Funktion kann in der PostgreSQL -Funktion verwendet werden. Führen Sie die folgende SQL -Anweisung aus, um eine Funktion mit dem Namen zu erstellen count_product () ohne Argument, und die Funktion gibt die Daten des Ganzzahltyps zurück. Die Aggregatfunktion count () wurde in der Funktion verwendet, mit der die Anzahl der Zeilen basierend auf der spezifischen Bedingung zählt wird. Hier wurde in der Funktion eine Funktionsvariable mit dem Namen Total_Product deklariert, um den Rückgabewert der Funktion count () zu speichern. Die Funktion count () wurde in der Abfrage ausgewählt, um die Datensätze zu zählen, bei denen der Produktname mit dem Wort „Sony“ beginnt.

# Funktion erstellen oder ersetzen Sie die Funktion count_product ()
Gibt int int as zurück
$$
ERKLÄREN
Total_Product int;
START
--Zählen Sie die Gesamtzahl des jeweiligen Produkts
Wählen Sie die Anzahl (*) in Total_Product aus Produkten aus
Wo Name "Sony%" mag;
Return Total_Product;
ENDE;
$$
Sprache PLPGSQL;

Die folgende Ausgabe wird angezeigt, wenn die Funktion erfolgreich in der erstellt wird Postgres Datenbank.

Führen Sie den folgenden Befehl aus, um die Funktion count_product () auszuführen,.

# Wählen Sie count_product ();

Die folgende Ausgabe zeigt, dass die Funktion count () den Wert 1 zurückgibt, da nur ein Datensatz vorhanden ist, der dem Wert entspricht, "Sony".

Abschluss

Der Zweck der Verwendung von PostgreSQL -Funktionen und unterschiedlichen Möglichkeiten der Verwendung von PostgreSQL -Funktionen mit IN, OUT und In -Out -Argumenten wurden in diesem Tutorial durch Erstellen mehrerer PostgreSQL -Funktionen beschrieben. Das Argument wird verwendet, um Wert in die Funktion einzubeziehen. Das Out -Argument wird verwendet, um den Wert aus der Funktion zurückzugeben. Das In -out -Argument wird verwendet, um den Eingabewert in die Funktion zu übernehmen und die Ausgabe aus der Funktion zurückzugeben. Die Funktion kann auch ohne Argument erstellt werden. Ich hoffe.