PL / pgSQL

PL / pgSQL
Procedural extension of SQL
Basic data
Paradigms :	procedural
Publishing year:	October 30, 1998
Developer:	PostgreSQL team
Current  version :	12   (October 3, 2019)
Typing :	strong , static , explicit
Influenced by:	PL / SQL , Ada
Operating system :	Unix derivatives , Linux , Windows
License :	PostgreSQL license
postgresql.org

PL / pgSQL ( Procedural Language / PostgreSQL Structured Query Language ) is a procedural language of the object-relational database PostgreSQL . As with PL / SQL on Oracle database systems, the syntax of PL / pgSQL is closely based on Ada .

PL / pgSQL was introduced to expand the range of SQL functions; PL / pgSQL code can be stored as a stored procedure in the database itself. It supports variables , conditions , loops , functions , database cursors and exception handling . PL / pgSQL code can be called from SQL commands as well as from database triggers.

With the help of the procedural extension, SQL commands can be generated dynamically directly in the PostgreSQL server and no longer have to be transferred as text via a database interface, as is the case with e.g. B. is the case with ODBC , JDBC and OLE DB , but can be created and executed directly in the database.

use

• PL / pgSQL code can be transferred to PostgreSQL from a database front end and processed there directly.
• PL / pgSQL code can be stored permanently in the database (as a stored procedure ) in order to expand the functionality of the database.
• Via authorizations (so-called “roles”), every user or user group of the database can use the functions that are intended for their role. In this way, security against unauthorized access can be significantly improved.
• Use in database triggers
• The performance can often be increased enormously if PL / pgSQL programs are executed directly in the database, especially if this avoids communication between processes or network traffic if the database and application server run on different hardware.

Basic structure

PL / pgSQL programs consist of blocks:

DECLARE
  -- Deklarationsblock
  -- Der DECLARE Abschnitt ist optional
BEGIN
  -- Ausführungsteil
EXCEPTION
  -- Ausnahmeverarbeitung
  -- Der EXCEPTION Abschnitt ist optional
END;

example

The example writes a "Hello World" note.

-- Eine Funktion namens hallo wird angelegt.
-- "void" bedeutet, dass nichts zurückgegeben wird.
CREATE OR REPLACE FUNCTION  hallo() RETURNS void AS
  -- Der Funktionskörper wird in $$-Stringliteralen gekapselt.
  -- hier steht $body$ zwischen den $ Zeichen.
  -- Der Text zwischen den $ Zeichen muss eine Länge von mindestens 0 Zeichen aufweisen.
  $body$
    BEGIN
      RAISE NOTICE  'Hallo Welt'; -- eine Notiz wird aufgerufen
    END;
  $body$ -- Ende des Funktionskörpers
LANGUAGE plpgsql; -- die Sprache des Funktionskörpers muss angegeben werden

SELECT hallo();
 -- Die Funktion wird mit einem SELECT aufgerufen.
 -- Die Ausgabe der Notiz erfolgt in der Konsole

DROP FUNCTION hallo();
-- Löschen ("droppen") der Funktion, die wir gerade angelegt haben.

Variable definitions

Variables are DECLAREdefined in the optional section and optionally initialized.

CREATE FUNCTION foo() RETURNS void AS
$BODY$
  DECLARE
      zahl_antwort INTEGER;
      zahl_lösung INTEGER := 42;
  BEGIN
    zahl_antwort := zahl_lösung;
    RAISE NOTICE  'Die Antwort lautet %.', zahl_antwort;-- % wird durch die Variable ersetzt
    -- return true;
  END;
$BODY$ LANGUAGE plpgsql;

:=is the assignment operator used to assign a value to a variable. In the DECLARE section you can alternatively DEFAULTuse it for assignments .

Number variables

variablenname NUMERIC(precision, scale) DEFAULT wert;

To define a number variable, write the variable name followed by the variable type NUMERIC.

After this, the precision precision and optionally a comma and the number of decimal places scale are written in round brackets. If you specify NUMERICwithout brackets, the accuracy is up to 131072 places before the decimal point and up to 16383 places after the decimal point.

In this case, the precision corresponds to the number of places the variable can contain and not to the range of values.

Selection of further data types for number variables:

SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE PRECISION

Text variables

variablenname1 VARCHAR(length) := 'Text';
VARCHAR(length) COLLATE collation_name;

To define a text variable, write the variable name followed by the variable type VARCHAR. After this, the number of characters that can be stored in the variable is written in brackets. If the text variable is to be sorted according to language or user-defined criteria, you can use the keyword COLLATEfollowed by the collation name e.g. B. use "en_US". Further data types for text variables are:

CHAR, TEXT

Boolean

variablenname BOOLEAN := TRUE;

Can TRUE, FALSEor NULLbe.

Date and Time

variablenname DATE := TO_DATE( '01.01.2005' , 'DD.MM.YYYY');

To define a date variable, write the variable name followed by the variable type DATE. PostgreSQL provides a number of functions for converting the date and time. Here was TO_DATE()used. This function converts the text between the first quotation marks into a date with the specified format between the second quotation marks. Other date and time data types are:

TIMESTAMP [(p)] [ WITHOUT TIME ZONE ]
TIMESTAMP [(p)] WITH TIME ZONE
DATE
TIME [(p)] [ WITHOUT TIME ZONE ]
TIME [(p)] WITH TIME ZONE
INTERVAL [ FIELDS ] [(p)]

With the optional statement (p), the number of digits in fractions of a second can be specified more precisely.

Specify data type via table or column

Variablenname tabellenname%ROWTYPE;
Variablenname tabellenname.spaltenname%TYPE;

%TYPEdefines a variable of the type of the specified column. %ROWTYPEdefines a variable of the type of the specified table. Because every table in PostgreSQL implicitly generates a row type with the same name, it can %ROWTYPEbe omitted.

Example:

CREATE FUNCTION foo() RETURNS void AS
$BODY$
  DECLARE
    t_row tab%ROWTYPE;
  BEGIN
    SELECT * INTO t_row FROM tab WHERE Z=1;
     RAISE NOTICE  'Y*4+Z*2= %.', t_row.y *4+ t_row.z*2;
    /*return true;*/
  END;
$BODY$ LANGUAGE plpgsql;

Control of the program sequence

Return of the function

RETURN expression;
RETURN NEXT expression;
RETURN QUERY query;
RETURN QUERY EXECUTE command-string;
RETURN QUERY EXECUTE command-string USING expression;

The RETURNreturn to the function is defined with the keyword . If the function is to SETOFreturn data records using the keyword , this can be implemented with RETURN NEXTor RETURN QUERY. With the help of USINGparameters can be inserted into the SQL command.

The following example RETURN NEXTuses:

BEGIN; -- eine Transaktion starten
  CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT); -- Tabelle foo neu erstellen
  INSERT INTO foo VALUES (1, 2, 'drei');
  INSERT INTO foo VALUES (4, 5, 'neun');
  -- Funktion getAllFoo anlegen. Die Funktion soll alle Datensätze aus foo liefern,
  -- deren fooid größer als 0 ist:
  CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS
    $BODY$ -- Beginn der PL/pgSQL Prozedur
      DECLARE
        r foo%rowtype;
      BEGIN
        FOR r IN
          SELECT * FROM foo WHERE fooid > 0
        LOOP
          -- hier könnten weitere Anweisungen stehen
          RETURN NEXT r; -- Rückgabe des aktuellen Datensatzes aus SELECT
        END LOOP;
        RETURN;
      END
    $BODY$ -- Ende der PL/pgSQL Prozedur
  LANGUAGE plpgsql;
  SELECT * FROM getallfoo(); -- Dieses Select zeigt alle Datensätze die die Funktion liefert.
ROLLBACK; -- Das war ein Test, es soll nichts gespeichert werden

Branch of the program flow

The program sequence can be controlled with the aid of conditions. Depending on the situation, the commands are processed in the section provided.

IF THEN

IF boolean_expression THEN statements; END IF;
IF boolean_expression THEN statements; ELSE statements; END IF;
IF boolean_expression THEN statements; ELSIF boolean_expression THEN statements; END IF;
IF boolean_expression THEN statements; ELSIF boolean_expression THEN statements; ELSE statements; END IF;

IFchecks whether a condition is met. If the condition is true, the code after THENand is executed and otherwise skipped. With ELSIFfurther conditions can be added. If ELSEthere is one , the following code is executed if none of the conditions apply. If more than one of the conditions is met, only the first true condition is carried out and all other true conditions are skipped. The keywords ELSEIFand ELSIFare synonyms .

CASE WHEN

CASE search_expression WHEN expressions THEN statements; END CASE;
CASE search_expression WHEN expressions THEN statements; ELSE statements; END CASE;
CASE WHEN boolean_expression THEN statements; END CASE;
CASE WHEN boolean_expression THEN statements; ELSE statements; END CASE;
-- Optional darf "WHEN … THEN …" beliebig oft vorkommen;

The CASEstatement offers two different options, in the first case CASEa search expression is specified after it . This expression is WHENsearched for in the expression that follows, and the code after the is THENexecuted. After that WHEN, several expressions separated by commas can be evaluated. In the second case followed by CASEthe WHENright, and thereafter a condition is specified. If this applies, the code is THENexecuted after . In either case, the ELSEfollowing code is executed if nothing is found. with END CASEthe will CASEstatement concluded. As with before IF THEN, only the section of code following the first valid condition is executed.

Example for branching the program sequence with IF THENand CASE WHEN:

CREATE FUNCTION foo(int) RETURNS void AS
$BODY$
  DECLARE
    i INTEGER := $1;
  BEGIN
    if i > 50 then
      RAISE NOTICE  'true %', i;
    ELSIF i > 25 then
      RAISE NOTICE  '1. elsif %', i;
    ELSIF i > 20 then
      RAISE NOTICE  '2. elsif %', i;
    ELSE
      RAISE NOTICE  'if false else %', i;
    END IF;
    CASE I
      WHEN 21,23,25,27 THEN
        RAISE NOTICE  '1. einfache when %', i;
      WHEN 22,24,26,28 THEN
        RAISE NOTICE  '2. einfache when %', i;
      ELSE
        RAISE NOTICE  'einfache case else %', i;
      END CASE;
    CASE
      WHEN I BETWEEN 20 and 25 THEN
        RAISE NOTICE  '1. gesuchte when %', i;
      WHEN I BETWEEN 26 and 30 THEN
        RAISE NOTICE  '2. gesuchte when %', i;
      ELSE
        RAISE NOTICE  'gesuchte case else %', i;
      END CASE;
  END;
$BODY$ LANGUAGE plpgsql;
Select foo(27);

Ausgabe mit pgAdmin:
HINWEIS:  1. elsif 27
HINWEIS:  1. einfache when 27
HINWEIS:  2. gesuchte when 27
Total query runtime: 35 ms.
1 row retrieved.

grind

With the key words LOOP, EXIT, CONTINUE, WHILE, FORand FOREACHstatement blocks can be run through repeatedly.

Simple LOOP loop

LOOP
  statements;
  EXIT;
END LOOP;
 <<label>>
LOOP
    statements;
    EXIT  label  WHEN boolean_expression ;
END LOOP  label ;

The simple LOOPloop ends as soon as EXITthe loop or RETURNthe function is ended. Follows behind the EXITone WHENwith a condition that the loop is left only when the condition is fulfilled. PL / pgSQL allows you to name loops. In LOOPany case, the block will EXITrun through to, regardless of whether the condition was already met before. The CONTINUE WHENstatement can be used to conditionally execute part of the loop.

The name of a block is specified between double, larger, smaller characters << >> such as <<label>> above. The naming improves the readability of the code.

In this example the named block "ablock" is run through until j = 42. As soon as the j becomes greater than 21, notes are issued:

CREATE OR REPLACE FUNCTION foo(int) RETURNS integer AS
$BODY$
  DECLARE
    i INTEGER:=$1;
    j INTEGER:=0;
  BEGIN
    <<ablock>> -- Ein Schleife wird mit ablock benannt.
    LOOP
      j:=j+7;
      EXIT ablock WHEN j>=i; -- Die Schleife endet wenn j>=i wird
      CONTINUE ablock WHEN j<(i/2) ; -- falls j größer dem halben i ist wird der folgende Block durchlaufen:
        RAISE NOTICE ' %', j;
    END LOOP ablock;
    RETURN j;
  END;
$BODY$ LANGUAGE plpgsql;
Select foo(42);

Ausgabe mit pgAdmin:
HINWEIS:   21
HINWEIS:   28
HINWEIS:   35
Total query runtime: 27 ms.
1 row retrieved.

WHILE LOOP loop

WHILE boolean_expression LOOP
    statements;
END LOOP

The simple WHILE LOOPloop is only run through if the input condition is met and ends as soon as the condition no longer applies. This variant can also be named.

FOR loops

FOR varname IN  expression .. expression  LOOP -- FOR i IN REVERSE 10..2 BY 2 LOOP
    statements;
END LOOP;

The FORloop counts an index variable of the type INTEGERthat is also created automatically from a specified start value to a specified end value. The start and target values ​​are separated by two points. If the keyword REVERSE is specified after the IN, the value counts down from the larger to the smaller. In any case, the increment must be indicated positively, even when counting down.

The FORloop can also be used to iterate through a query:

FOR wertliste IN query LOOP
    statements;
END LOOP

The “list of values” is a variable that takes over the fields of the “query”. The variable value-list accepts each line of the query once when the query is run through. The loop body is then run through with this line.

If you use an explicit cursor instead of a query defined “locally” in the FOR loop (or an SQL string via EXECUTE), the loop variable “list of values” (analogous for the loop index of a numeric FOR loop) declared implicitly.

CREATE FUNCTION foo() RETURNS void AS
$body$
  DECLARE
    meinaktuellerview RECORD; -- die Variable meinaktuellerview wird als Type RECORD festgelegt.
  BEGIN
    RAISE NOTICE 'Refreshing materialized views...';
    FOR meinaktuellerview IN SELECT viewname, viewsql FROM fooviews ORDER BY foo.fooid LOOP
      -- jetzt beinhaltet "meinaktuellerview" einen Datensatz aus der Tabelle fooviews
      RAISE NOTICE 'Ersetzen des materialisierten views %s ...', quote_ident(meinaktuellerview.viewname);
      EXECUTE 'TRUNCATE TABLE ' || quote_ident(meinaktuellerview.viewname); -- Inhalt aus einer Tabelle löschen
      EXECUTE 'INSERT INTO '
        || quote_ident(meinaktuellerview.viewname) || ' ' || meinaktuellerview.viewsql;
      -- eine in der Tabelle gespeicherte Abfrage wird an eine Tabelle angefügt.
    END LOOP;
    RAISE NOTICE 'Erledigt: materialisierte Views sind aktuell.';
    END;
$body$ LANGUAGE plpgsql;

Web links

• PostgreSQL 9.2 Documentation (English)
• PostgreSQL: The Official Manual - Chapter 38. PL / pgSQL: SQL procedural language ( Memento from March 15, 2010 in the Internet Archive ) (refers to the outdated version 7.3.3)
• PL / pgSQL tutorial with many examples (English)

Individual evidence

1. ^ PostgreSQL: License page at PostgreSQL.org ; As of September 17, 2011 (English).
2. ↑ PostgreSQL 9.1.0 Documentation, PL / pgSQL - SQL Procedural Language, Structure of PL / pgSQL. PostgreSQL.org, September 12, 2011, accessed September 17, 2011 .