# PL/pgSQL Language

> Source chapter 3.6 for PostgreSQL 11.

---

LLMS index: [llms.txt](/en/llms.txt)

---

<div class="feature-source-note"><span class="feature-edition-badge">GA</span><span><strong>Source pages:</strong> PDF 55-60</span><span><a href="https://github.com/nori-shinoda/documents/blob/1773200c5362476c4a549222b7d17fa91f546cae/postgresql11_ga_new_features_en_20181022-1.pdf">Original PDF on GitHub</a></span></div>

<p class="feature-page-marker" id="pgfeat-11-plpgsql-pdf-page-55">PDF page 55</p>

This article explains the enhancement of the PL/pgSQL language and new objects using PL/pgSQL.

#### 3.6.1 PROCEDURE object {#pg11-3-6-1-procedure-object}

In PostgreSQL 11, PROCEDURE was added as a new schema object. PROCEDURE is almost the same object as FUNCTION without a return value. PROCEDURE is created with the CREATE PROCEDURE statement. Unlike the CREATE FUNCTION statement, there are no RETURNS clause, ROWS clause, PARALLEL clause, CALLED ON clause, etc.

**Example 68.** PROCEDURE creation

```text
postgres=> CREATE PROCEDURE proc1(INOUT p1 TEXT) AS
    $$
    BEGIN
        RAISE NOTICE 'Parameter: %', p1 ;
    END ;
    $$
    LANGUAGE plpgsql ;
CREATE PROCEDURE
```

```sql
 Information on the created procedure can be referred to from the pg_proc catalog as FUNCTION.
 To execute PROCEDURE, use the CALL statement instead of the SELECT statement. Parentheses
(()) are necessary regardless of the presence or absence of parameters. INOUT can be specified for the
PROCEDURE parameter.
```

**Example 69.** Execute PROCEDURE \(1\)

```text
postgres=> CALL proc1('test') ;
NOTICE:  Parameter: test
CALL
  p1
------
 test
(1 row)
```

```text
A parameter name can be specified in the CALL statement.
```

<p class="feature-page-marker" id="pgfeat-11-plpgsql-pdf-page-56">PDF page 56</p>

**Example 70.** Execute PROCEDURE \(2\)

```text
postgres=> CALL proc1(p1=>'test') ;
NOTICE:  Parameter: test
CALL
  p1
------
 test
(1 row)
```

```text
 To display a definition of created PROCEDURE from the psql command, use the \df command same
as FUNCTION. PROCEDURE shows the Type column as "proc". And the value of type column for
FUNCTION was changed to "func". For PROCEDURE, the "prorettype" column of the pg_proc
catalog becomes 0.
```

**Example 71.** List of PROCEDURE

```text
postgres=> \df
                      List of functions
 Schema | Name  | Result data type | Argument data types | Type
--------+-------+------------------+---------------------+------
 public | func1 | text             | text                | func
 public | proc1 |                  | INOUT p1 text       | proc
(2 rows)
```

```text
 Even when referring to or changing the PROCEDURE definition from the psql command, it can be
executed in the same way as FUNCTION.
```

<p class="feature-page-marker" id="pgfeat-11-plpgsql-pdf-page-57">PDF page 57</p>

**Example 72.** Display PROCEDURE definition

```text
postgres=> \sf proc1
CREATE OR REPLACE PROCEDURE public.proc1(INOUT p1 text)
 LANGUAGE plpgsql
AS $procedure$
    BEGIN
        RAISE NOTICE 'Parameter: %', $1 ;
    END ;
    $procedure$
```

##### Transaction control {#pg11-p57-transaction-control}

Within procedures, it is possible to control transactions. In PL/pgSQL procedure, COMMIT statement and ROLLBACK statement can be used. It is now possible to control transactions within cursor loops.

**Example 73.** Transaction control in PROCEDURE

```sql
CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i) ;
        IF i % 2 = 0 THEN
            COMMIT ;
        ELSE
            ROLLBACK ;
        END IF ;
    END LOOP ;
END ;
$$ ;
```

```text
 The syntax for transaction control is also provided in languages other than PL/pgSQL.
```

<p class="feature-page-marker" id="pgfeat-11-plpgsql-pdf-page-58">PDF page 58</p>

PL/Python plpy.commit() / plpy.rollback() PL/Perl spi_commit() / spi_rollback() PL/Tcl commit / rollback

##### Change of CREATE / ALTER TRIGGER statement {#pg11-p58-change-of-create-alter-trigger-statement}

Since it was possible to distinguish between PROCEDURE object and FUNCTION object, EXECUTE PROCEDURE clause of CREATE TRIGGER statement and ALTER TRIGGER statement can also use EXECUTE FUNCTION clause. Both meanings are the same.

#### 3.6.2 Enhancement of variable definition {#pg11-3-6-2-enhancement-of-variable-definition}

The following enhancements have been added to PL/pgSQL.

##### CONSTANT variables {#pg11-p58-constant-variables}

Constants can now be declared by adding the CONSTANT clause to the variable. If an initial value is not specified, no error occurs and the value of the variable is set as NULL. Syntax

```text
DECLARE variable_name CONSTANT data_type [ := initial_value ] ;
```

**Example 74.** CONSTANT clause and changing value error

```text
postgres=> do $$
    DECLARE
       cons1 CONSTANT NUMERIC := 1 ;
    BEGIN
       cons1 := 2 ;
    END ;
    $$ ;
ERROR:  variable "cons1" is declared CONSTANT
LINE 5:   cons1 := 2 ;
^
```

<p class="feature-page-marker" id="pgfeat-11-plpgsql-pdf-page-59">PDF page 59</p>

##### NOT NULL variables {#pg11-p59-not-null-variables}

It is now possible to specify a NOT NULL constraint on a variable. An initial value is required at the time of declaration. An error occurs if an initial value is not specified. Also, assignment of NULL value will result in an error. Syntax

```text
DECLARE variable_name data_type NOT NULL := initial_value ;
```

**Example 75.** NOT NULL clause and initial value

```text
postgres=> do $$
    DECLARE
       nn1 NUMERIC NOT NULL ;
    BEGIN
       RAISE NOTICE 'nn1 = %', nn1 ;
    END ;
    $$ ;
ERROR:  variable "nn1" must have a default value, since it's declared
NOT NULL
LINE 3:   nn1 NUMERIC NOT NULL ;
                        ^
```

##### SET TRANSACTION statement {#pg11-p59-set-transaction-statement}

In PostgreSQL 11, It is possible to execute the SET TRANSACTION statement in the PL/pgSQL block.

<p class="feature-page-marker" id="pgfeat-11-plpgsql-pdf-page-60">PDF page 60</p>

**Example 76.** SET TRANSACTION statement

```text
postgres=> DO LANGUAGE plpgsql $$
  BEGIN
      PERFORM 1 ;
      COMMIT ;
      SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
      PERFORM 1 ;
      RAISE INFO '%', current_setting('transaction_isolation') ;
      COMMIT ;
  END ;
  $$ ;
INFO:  repeatable read
DO
```