Übersicht über gespeicherte Prozeduren in Amazon Redshift

Gespeicherte Prozeduren werden üblicherweise verwendet, um die Logik für Datentransformation, Datenvalidierung und die geschäftsspezifische Logik zu verkapseln. Durch das Zusammenfassen mehrerer SQL-Schritte in eine gespeicherte Prozedur können Sie die Austauschläufe zwischen Ihren Anwendungen und der Datenbank reduzieren.

Für eine fein abgestimmte Zugriffskontrolle können Sie zur Ausführung von Funktionen gespeicherte Prozeduren erstellen, ohne einem Benutzer Zugriff auf die zugrunde liegenden Tabellen zu gewähren. So kann beispielsweise nur der Eigentümer oder ein Superuser eine Tabelle kürzen, während ein Benutzer Schreibrechte benötigt, um Daten in eine Tabelle einzufügen. Anstatt einem Benutzer Rechte für die zugrunde liegenden Tabellen zu erteilen, können Sie eine gespeicherte Prozedur erstellen, die die Aufgabe ausführt. Anschließend erteilen Sie dem Benutzer Rechte zum Ausführen der gespeicherten Prozedur.

Eine gespeicherte Prozedur mit dem Sicherheitsattribut DEFINER wird mit den Rechten des Eigentümers der gespeicherten Prozedur ausgeführt. Standardmäßig hat eine gespeicherte Prozedur die INVOKER-Sicherheit, d. h. die Prozedur verwendet die Rechte des Benutzers, der die Prozedur aufruft.

Verwenden Sie den CREATE PROCEDURE-Befehl zum Erstellen einer gespeicherten Prozedur. Verwenden Sie den CALL-Befehl zum Ausführen einer Prozedur. Beispiele dazu folgen später in diesem Abschnitt.

Anmerkung

Einige Clients zeigen beim Erstellen einer in Amazon Redshift gespeicherten Prozedur möglicherweise den folgenden Fehler an.

ERROR: 42601: [Amazon](500310) unterminated dollar-quoted string at or near "$$

Dieser Fehler tritt auf, wenn der Client die CREATE PROCEDURE-Anweisung mit Semikolons zur Trennung von Anweisungen und dem Dollarzeichen ($) für Zitate nicht korrekt parsen kann. Dies führt dazu, dass nur ein Teil der Anweisung an den Amazon-Redshift-Server gesendet wird. Dieser Fehler kann oftmals umgangen werden, indem die Run as batch- oder die Execute selected-Option des Clients verwendet wird.

Verwenden Sie beispielsweise mit dem Aginity-Client die Option Run entire script as batch. Bei Verwendung von SQL Workbench/J empfehlen wir Version 124. Bei Verwendung von SQL Workbench/J Version 125 sollten Sie ein anderes Trennzeichen verwenden, um das Problem zu umgehen.

CREATE PROCEDURE enthält SQL-Anweisungen mit einem Semikolon (;). Wenn ein anderes Trennzeichen, wie ein Schrägstrich (/), festgelegt und am Ende der CREATE PROCEDURE-Anweisung platziert wird, wird die Anweisung zur Verarbeitung an den Amazon-Redshift-Server gesendet. Im Folgenden sehen Sie ein Beispiel.

CREATE OR REPLACE PROCEDURE test()
AS $$
BEGIN
  SELECT 1 a;
END;
$$
LANGUAGE plpgsql
;
/

Weitere Informationen finden Sie unter Alternative Trennzeichen in der SQL Workbench/J-Dokumentation. Oder verwenden Sie einen Client mit besserer Unterstützung für das Parsen von CREATE PROCEDURE-Anweisungen, z. B. den Abfrage-Editor in der Amazon-Redshift-Konsole oder TablePlus.

Themen

• Benennen von gespeicherten Prozeduren
• Sicherheit und Berechtigungen für gespeicherte Prozeduren
• Rückgabe einer Ergebnismenge
• Verwalten von Transaktionen
• Aufspüren von Fehlern
• Protokollieren von gespeicherten Prozeduren
• Überlegungen zur Unterstützung für gespeicherte Prozeduren

Das folgende Beispiel zeigt eine Prozedur ohne Ausgabeparameter. Standardmäßig handelt es sich bei Parametern um Eingabe-(IN)-Parameter.

CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar)
AS $$
BEGIN
  RAISE INFO 'f1 = %, f2 = %', f1, f2;
END;
$$ LANGUAGE plpgsql;

call test_sp1(5, 'abc');
INFO: f1 = 5, f2 = abc
CALL   

Anmerkung

Wenn Sie gespeicherte Prozeduren schreiben, empfehlen wir eine bewährte Methode zum Sichern sensibler Werte:

Nehmen Sie keine Hartkodierung für sensible Informationen in der Logik der gespeicherten Prozedur vor.hardco Weisen Sie beispielsweise kein Benutzerkennwort in einer CREATE USER-Anweisung im Text einer gespeicherten Prozedur zu. Dies stellt ein Sicherheitsrisiko dar, da hartkodierte Werte als Schema-Metadaten in Katalogtabellen aufgezeichnet werden können. Übergeben Sie stattdessen mithilfe von Parametern sensible Werte wie Passwörter als Argumente an die gespeicherte Prozedur.

Weitere Informationen zu gespeicherten Prozeduren finden Sie unter CREATE PROCEDURE und Erstellen von gespeicherten Prozeduren in Amazon Redshift. Weitere Informationen zu Katalogtabellen finden Sie unter Systemkatalogtabellen.

Das folgende Beispiel zeigt eine Prozedur mit Ausgabeparametern. Es gibt Eingabe (IN)-, Eingabe und Ausgabe (INOUT)- sowie Ausgabe (OUT)-Parameter.

CREATE OR REPLACE PROCEDURE test_sp2(f1 IN int, f2 INOUT varchar(256), out_var OUT varchar(256))
AS $$
DECLARE
  loop_var int;
BEGIN
  IF f1 is null OR f2 is null THEN
    RAISE EXCEPTION 'input cannot be null';
  END IF;
  DROP TABLE if exists my_etl;
  CREATE TEMP TABLE my_etl(a int, b varchar);
    FOR loop_var IN 1..f1 LOOP
        insert into my_etl values (loop_var, f2);
        f2 := f2 || '+' || f2;
    END LOOP;
  SELECT INTO out_var count(*) from my_etl;
END;
$$ LANGUAGE plpgsql;


call test_sp2(2,'2019');

         f2          | column2
---------------------+---------
 2019+2019+2019+2019 | 2
(1 row)