This document is an opensql technical document based on postgresql.

Overview

This document provides guidance on PL/pgSQL, the procedural language for PostgreSQL that is installed by default when installing the database server. 

postgres=# dx
List of installed extensions
Name | Version | Schema | Description
——————–+———+————+————————————————————————
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

• Can be used to create Functions, Procedures, and Triggers; it is a form of SQL that includes control structures.
• Inherits all User-Defined Types, Functions, Procedures, and Operators.
• PL/pgSQL operates inside the server, providing reliability.

Advantages of Using PL/pgSQL

• Operates inside the database, resulting in low process communication overhead.
• All SQL data types, operators, and functions can be used.

Note

Below is an explanation of PL/pgSQL content with examples provided as toggles.
It is recommended to try entering the queries directly to verify. 

Method

PL/pgSQL Block Structure

[<>]
DECLARE
Declare variables.
The variable's lifecycle is limited to within the block (local variables).
Variables can be shadowed within nested blocks,
and can be referenced by specifying the outer block's label.
All SQL data types are supported, and the keyword %TYPE can be used
to reference table columns.
BEGIN
Control structures
SQL operators except service operators
EXCEPTION ;
Implement error handling here.
END [label];

PL/pgSQL Block Structure Example

CREATE FUNCTION example() RETURNS integer AS $$
<< outerblock >>
DECLARE
quantity integer := 30;
BEGIN
RAISE NOTICE ‘Quantity here is %’, quantity; — Prints 30
quantity := 50;
— Create a sub block
<>
DECLARE
quantity integer := 80;
BEGIN
RAISE NOTICE ‘Quantity here is %’, quantity; — Prints 80
RAISE NOTICE ‘Outer quantity here is %’, outerblock.quantity; — Prints 50
END subblock;
RAISE NOTICE ‘Quantity here is %’, quantity; — Prints 50
RETURN quantity;
END outerblock;

$$ LANGUAGE plpgsql;

— Execution result
postgres=# SELECT example();
NOTICE: Quantity here is 30
NOTICE: Quantity here is 80
NOTICE: Outer quantity here is 50
NOTICE: Quantity here is 50

example

  50
(1 row)

Anonymous Block Structure

PL/pgSQL code can be executed once without storing it on the database server by using the DO command. Parameters cannot be used, nor can return values be received.

— Structure
DO $$
<>DECLARE
Declare variables
BEGIN
Control structures
EXCEPTION ; Error handling
END [block label];

Anonymous Block Structure Example

DO $$
<>
DECLARE
v1_int integer;
v2_txt text;
BEGIN
SELECT 1 INTO v1_int;
SELECT ‘Anonymous block test’ INTO v2_txt;
RAISE NOTICE ‘%, %’, v1_int, v2_txt;
END outblock;
$$ LANGUAGE plpgsql;

— Execution result
postgres=# DO $$
postgres$# <>
postgres$# DECLARE
postgres$# v1_int integer;
postgres$# v2_txt text;
postgres$# BEGIN
postgres$# SELECT 1 INTO v1_int;
postgres$# SELECT ‘Anonymous block test’ INTO v2_txt;
postgres$# RAISE NOTICE ‘%, %’, v1_int, v2_txt;
postgres$# END outblock;
postgres$# $$ LANGUAGE plpgsql;
NOTICE: 1, Anonymous block test
DO

Variable Declaration

Except for loop variables and variables for cursor results, all variables used inside a block must be declared in the DECLARE section.

General Syntax

name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];

The initial value of a variable can be assigned using DEFAULT, :=, or = with an expression. If no initial value is specified, the variable is assigned NULL.
Variables can also be initialized with the values of Function or Procedure parameters.
To facilitate parameter use, reserved words like $1, $2, … $n can be used as bind variables.
ALIAS functionality is also supported to improve readability.

name ALIAS FOR $n;

Variable Declaration Example

DO $$
DECLARE
v1_int integer;
v2_int integer DEFAULT 2;
v3_int integer := 3;
v4_int integer = 4;
BEGIN
RAISE NOTICE ‘%, %, %, %’, v1_int, v2_int, v3_int, v4_int;
END
$$ LANGUAGE plpgsql;

— Execution result
postgres=# DO $$
postgres$# DECLARE
postgres$# v1_int integer;
postgres$# v2_int integer DEFAULT 2;
postgres$# v3_int integer := 3;
postgres$# v4_int integer = 4;
postgres$# BEGIN
postgres$# RAISE NOTICE ‘%, %, %, %’, v1_int, v2_int, v3_int, v4_int;
postgres$# END
postgres$# $$ LANGUAGE plpgsql;
NOTICE: , 2, 3, 4
DO

Constant Declaration

By adding CONSTANT before the TYPE in a variable declaration, the variable can be designated as a constant.
Constants cannot have their data values changed after initialization; attempts to do so will result in errors.
If no value is assigned at initialization, the constant is created with a NULL value.

name [CONSTANT] type;

Constant Declaration Example

DO $$
DECLARE
v1_const_int constant integer;
BEGIN
SELECT 1 INTO v1_const_int;
RAISE NOTICE ‘%’, v1_const_int;
END
$$ LANGUAGE plpgsql;

— Execution result
postgres=# DO $$
postgres$# DECLARE
postgres$# v1_const_int constant integer;
postgres$# BEGIN
postgres$# SELECT 1 INTO v1_const_int;
postgres$# RAISE NOTICE ‘%’, v1_const_int;
postgres$# END
postgres$# $$ LANGUAGE plpgsql;
ERROR: variable “v1_const_int” is declared CONSTANT
LINE 5: SELECT 1 INTO v1_const_int;
^

Collation

You can specify the Collation type to use for data sorting.
If not specified, the database's Collation type is used.

PostgreSQL supports string sorting using the ko_KR type, but sorting results may vary depending on the number of characters, so for perfect Korean sorting, Collation should be set to C.

Collation Example

CREATE TABLE coll_t1(name text);
INSERT INTO coll_t1 VALUES(‘가’),(‘나’),(‘가나다’),(‘가다나’),(‘나가’);
SELECT * FROM coll_t1 ORDER BY name ASC;
SELECT * FROM coll_t1 ORDER BY name COLLATE “C” ASC;

— Execution result
postgres=# SELECT * FROM coll_t1 ORDER BY name ASC;

name

가
가
나
나
나가
나가
가나다
가나다
가다나
가다나
(10 rows)

postgres=# SELECT * FROM coll_t1 ORDER BY name COLLATE “C” ASC;

name

가
가
가나다
가나다
가다나
가다나
나
나
나가
나가
(10 rows)

Copying type

Using the %TYPE type, you can create variables that have the data type of another variable or a specific column of a table.

Even if the referenced data type changes, you don't need to worry, making it convenient for use in polymorphic functions. 
However, user-defined types cannot be copied.
 

General Syntax

variable referenced_variable%TYPE

Table Column Reference Example

VARIABLE TABLENAME.COLUMN%TYPE

Copying type Example

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id integer, password text);
INSERT INTO t1 VALUES(1, ‘pass1’);

DO $$
DECLARE
v1_int integer;
v2_copy_v1 v1_int%TYPE;
v3_copy_t1_id t1.id%TYPE;
BEGIN
SELECT id INTO v1_int FROM t1 LIMIT 1;
SELECT id INTO v2_copy_v1 FROM t1 LIMIT 1;
SELECT 3 INTO v3_copy_t1_id;
RAISE NOTICE ‘%, %, %’, v1_int, v2_copy_v1, v3_copy_t1_id;
END
$$ LANGUAGE plpgsql;

postgres=# DROP TABLE IF EXISTS t1;
DROP TABLE
postgres=# CREATE TABLE t1 (id integer, password text);
CREATE TABLE
postgres=# INSERT INTO t1 VALUES(1, ‘pass1’);
INSERT 0 1
postgres=#
postgres=# DO $$
postgres$# DECLARE
postgres$# v1_int integer;
postgres$# v2_copy_v1 v1_int%TYPE;
postgres$# v3_copy_t1_id t1.id%TYPE;
postgres$# BEGIN
postgres$# SELECT id INTO v1_int FROM t1 LIMIT 1;
postgres$# SELECT id INTO v2_copy_v1 FROM t1 LIMIT 1;
postgres$# SELECT 3 INTO v3_copy_t1_id;
postgres$# RAISE NOTICE ‘%, %, %’, v1_int, v2_copy_v1, v3_copy_t1_id;
postgres$# END
postgres$# $$ LANGUAGE plpgsql;
NOTICE: 1, 1, 3
DO

Row type

Also called row variables or row type variables, they can hold the result of SELECT or FOR queries as rows.
Values assigned to columns held by the variable can be accessed in the form of row_variable_name.column_name.

You can use the name of a table or view as a data type.
Although PostgreSQL does not require specifying %ROWTYPE because table names cannot be used as variable names, it is recommended for better compatibility with other DBMSs.

General Syntax

name table_name%ROWTYPE;
name composite_type_name;

Row type Example

DROP IF EXISTS TABLE t1;
CREATE TABLE t1 (id integer, password text);
INSERT INTO t1 VALUES(1, ‘pass1’);

DO $$
DECLARE
v1_row t1%ROWTYPE;
BEGIN
RAISE NOTICE ‘%, %’, v1_row.id, v1_row.password;
SELECT * INTO v1_row FROM t1;
RAISE NOTICE ‘%, %’, v1_row.id, v1_row.password;
END
$$ LANGUAGE plpgsql;

— Execution result
postgres=# DROP TABLE IF EXISTS t1;
DROP TABLE
postgres=# CREATE TABLE t1 (id integer, password text);
CREATE TABLE
postgres=# INSERT INTO t1 VALUES(1, ‘pass1’);
INSERT 0 1
postgres=#
postgres=# DO $$
postgres$# DECLARE
postgres$# v1_row t1%ROWTYPE;
postgres$# BEGIN
postgres$# RAISE NOTICE ‘%, %’, v1_row.id, v1_row.password;
postgres$# SELECT * INTO v1_row FROM t1;
postgres$# RAISE NOTICE ‘%, %’, v1_row.id, v1_row.password;
postgres$# END
postgres$# $$ LANGUAGE plpgsql;
NOTICE: ,
NOTICE: 1, pass1
DO

Record type

The record type is similar to the row type but does not have column structure information before receiving row data.
It is a variable that obtains the necessary column structure when data is assigned via SELECT or FOR queries.

If assigned data from queries with different structures, the record variable's column information changes.
Accessing the variable before structure assignment will cause a runtime error.

General Syntax

name RECORD;

Record Type Example

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id integer, password text);
INSERT INTO t1 VALUES(1, ‘pass1’);

DO $$
DECLARE
v1_rec RECORD;
BEGIN
— RAISE NOTICE ‘%, %’, v1_rec.id, v1_rec.password;
SELECT * INTO v1_rec FROM t1;
RAISE NOTICE ‘%, %’, v1_rec.id, v1_rec.password;
END
$$ LANGUAGE plpgsql;

— Execution result
postgres=# DROP TABLE IF EXISTS t1;
DROP TABLE
postgres=# CREATE TABLE t1 (id integer, password text);
CREATE TABLE
postgres=# INSERT INTO t1 VALUES(1, ‘pass1’);
INSERT 0 1
postgres=#
postgres=# DO $$
postgres$# DECLARE
postgres$# v1_rec RECORD;
postgres$# BEGIN
postgres$# — RAISE NOTICE ‘%, %’, v1_rec.id, v1_rec.password;
postgres$# SELECT * INTO v1_rec FROM t1;
postgres$# RAISE NOTICE ‘%, %’, v1_rec.id, v1_rec.password;
postgres$# END
postgres$# $$ LANGUAGE plpgsql;
NOTICE: 1, pass1
DO

Conditional Operators and loops

Conditional Statements

PL/pgSQL provides two types of conditional statements.

• IF-THEN statement
• CASE-WHEN-THEN statement

IF-THEN Statement

Checks a condition and executes if it is met.
The IF-THEN statement provides three keywords as follows.
All IF-THEN statements must end with END IF;.

IF boolean-expression THEN
…
[ELSIF boolean-expression THEN
…]
[ELSE
…]
END IF;

IF-THEN Statement Example

DO $$
DECLARE
result TEXT;
number INTEGER := 1;
BEGIN
IF number = 0 THEN
result := ‘zero’;
ELSIF number > 0 THEN
result := ‘positive’;
ELSIF number < 0 THEN
result := ‘negative’;
ELSE
result := ‘NULL’;
END IF;
RAISE NOTICE ‘%’,result;
END;
$$ LANGUAGE plpgsql;

— Execution result
postgres=# DO $$
postgres$# DECLARE
postgres$# result TEXT;
postgres$# number INTEGER := 1;
postgres$# BEGIN
postgres$# IF number = 0 THEN
postgres$# result := ‘zero’;
postgres$# ELSIF number > 0 THEN
postgres$# result := ‘positive’;
postgres$# ELSIF number < 0 THEN
postgres$# result := ‘negative’;
postgres$# ELSE
postgres$# result := ‘NULL’;
postgres$# END IF;
postgres$# RAISE NOTICE ‘%’,result;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
NOTICE: positive
DO

CASE-WHEN-THEN

CASE-WHEN-THEN statements, like IF-THEN, check conditions and execute if met.
All CASE-WHEN-THEN statements must end with END CASE;.

CASE statements can have two forms:

• Comparing the value of an operand
• Comparing specific conditions per WHEN clause

Comparing the value of an operand

CASE search-expression
WHEN expression [, expression [ … ]] THEN
statements
[ WHEN expression [, expression [ … ]] THEN
statements
… ]
[ ELSE
statements ]
END CASE;

CASE-WHEN Example Comparing Operand Values

DO $$
DECLARE
x integer := 1;
msg TEXT;
BEGIN
CASE x
WHEN 1, 2 THEN
msg := ‘one or two’;
ELSE
msg := ‘other value than one or two’;
END CASE;
RAISE NOTICE ‘%’,msg;
END;
$$ LANGUAGE plpgsql;

— Execution result
postgres=# DO $$
postgres$# DECLARE
postgres$# x integer := 1;
postgres$# msg TEXT;
postgres$# BEGIN
postgres$# CASE x
postgres$# WHEN 1, 2 THEN
postgres$# msg := ‘one or two’;
postgres$# ELSE
postgres$# msg := ‘other value than one or two’;
postgres$# END CASE;
postgres$# RAISE NOTICE ‘%’,msg;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
NOTICE: one or two
DO

Comparing specific conditions per WHEN clause

CASE
WHEN boolean-expression THEN
statements
[ WHEN boolean-expression THEN
statements
… ]
[ ELSE
statements ]
END CASE;

Example Comparing Specific Conditions per WHEN Clause

DO $$
DECLARE
x integer := 1;
msg TEXT;
BEGIN
CASE
WHEN x BETWEEN 0 AND 10 THEN
msg := ‘value is between zero and ten’;
WHEN x BETWEEN 11 AND 20 THEN
msg := ‘value is between eleven and twenty’;
END CASE;
RAISE NOTICE ‘%’,msg;
END;
$$ LANGUAGE plpgsql;

— Execution result
postgres=# DO $$
postgres$# DECLARE
postgres$# x integer := 1;
postgres$# msg TEXT;
postgres$# BEGIN
postgres$# CASE
postgres$# WHEN x BETWEEN 0 AND 10 THEN
postgres$# msg := ‘value is between zero and ten’;
postgres$# WHEN x BETWEEN 11 AND 20 THEN
postgres$# msg := ‘value is between eleven and twenty’;
postgres$# END CASE;
postgres$# RAISE NOTICE ‘%’,msg;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
NOTICE: value is between zero and ten
DO

Loops

PL/pgSQL provides the following loops:

• LOOP-EXIT
• WHILE loop
• FOR IN loop

• FOREACH IN ARRAY loop

   

LOOP-EXIT Loop

An infinite loop that ends when EXIT or RETURN is called.
EXIT can have a condition specified with WHEN.
CONTINUE can be used to skip the remaining statements and move to the next iteration when a condition is met.

[ <> ]
LOOP
[CONTINUE [LABEL] [WHEN condition]];
statements
[EXIT [LABEL] [WHEN condition]];
END LOOP [ label ];

LOOP-EXIT Loop Example

DO $$
DECLARE
x integer := 0;
BEGIN
LOOP
x = x + 1;
CONTINUE WHEN x = 5;
RAISE NOTICE ‘x : %’,x;
EXIT WHEN x > 10;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— Execution result
postgres=# DO $$
postgres$# DECLARE
postgres$# x integer := 0;
postgres$# BEGIN
postgres$# LOOP
postgres$# x = x + 1;
postgres$# CONTINUE WHEN x = 5;
postgres$# RAISE NOTICE ‘x : %’,x;
postgres$# EXIT WHEN x > 10;
postgres$# END LOOP;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
NOTICE: x : 1
NOTICE: x : 2
NOTICE: x : 3
NOTICE: x : 4
NOTICE: x : 6
NOTICE: x : 7
NOTICE: x : 8
NOTICE: x : 9
NOTICE: x : 10
NOTICE: x : 11
DO

WHILE Loop

A loop that repeats infinitely until the condition specified in WHILE is met.

If you want the loop to repeat infinitely when the condition is not met, you can use WHILE NOT. Like other loops, CONTINUE and EXIT can be used.

[ <> ]
WHILE boolean-expression LOOP
[CONTINUE [LABEL] [WHEN condition]];
statements
END LOOP [ label ];

[ <> ]
WHILE NOT boolean-expression LOOP
[CONTINUE [LABEL] [WHEN condition]];
statements
END LOOP [ label ];

WHILE Loop Example

DO $$
DECLARE
x integer := 0;
BEGIN
WHILE x < 11 LOOP
x = x + 1;
CONTINUE WHEN x = 5;
RAISE NOTICE ‘x : %’,x;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— Execution result
postgres=# DO $$
postgres$# DECLARE
postgres$# x integer := 0;
postgres$# BEGIN
postgres$# WHILE x < 11 LOOP
postgres$# x = x + 1;
postgres$# CONTINUE WHEN x = 5;
postgres$# RAISE NOTICE ‘x : %’,x;
postgres$# END LOOP;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
NOTICE: x : 1
NOTICE: x : 2
NOTICE: x : 3
NOTICE: x : 4
NOTICE: x : 6
NOTICE: x : 7
NOTICE: x : 8
NOTICE: x : 9
NOTICE: x : 10
NOTICE: x : 11
DO

WHILE NOT Loop Example

DO $$
DECLARE
x integer := 0;
BEGIN
WHILE NOT x > 10 LOOP
x = x + 1;
CONTINUE WHEN x = 5;
RAISE NOTICE ‘x : %’,x;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— Execution result
postgres=# DO $$
postgres$# DECLARE
postgres$# x integer := 0;
postgres$# BEGIN
postgres$# WHILE NOT x > 10 LOOP
postgres$# x = x + 1;
postgres$# CONTINUE WHEN x = 5;
postgres$# RAISE NOTICE ‘x : %’,x;
postgres$# END LOOP;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
NOTICE: x : 1
NOTICE: x : 2
NOTICE: x : 3
NOTICE: x : 4
NOTICE: x : 6
NOTICE: x : 7
NOTICE: x : 8
NOTICE: x : 9
NOTICE: x : 10
NOTICE: x : 11
DO

FOR-IN Loop

The loop variable increments or decrements over the range specified in IN range.
If REVERSE is specified, the loop variable decrements instead of increments.

The BY clause can be used to set the increment or decrement step.
Like other loops, CONTINUE and WHEN can be used.

[ <> ]
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP
statements
END LOOP [ label ];

You can also execute loops over all rows of a query result.

[ <> ]
FOR target IN query LOOP
statements
END LOOP [ label ];

FOR-IN Loop Example

DO $$
BEGIN
FOR x IN 1..10 LOOP
CONTINUE WHEN x=5;
RAISE NOTICE ‘x : %’,x;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— Execution result
postgres=# DO $$
postgres$# BEGIN
postgres$# FOR x IN 1..10 LOOP
postgres$# CONTINUE WHEN x=5;
postgres$# RAISE NOTICE ‘x : %’,x;
postgres$# END LOOP;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
NOTICE: x : 1
NOTICE: x : 2
NOTICE: x : 3
NOTICE: x : 4
NOTICE: x : 6
NOTICE: x : 7
NOTICE: x : 8
NOTICE: x : 9
NOTICE: x : 10
DO

FOR-IN Loop Over Query Results Example

DO $$
DECLARE
row RECORD;
BEGIN
FOR row IN SELECT * FROM pg_database
LOOP
CONTINUE WHEN row.oid=1;
RAISE NOTICE ‘oid : % | datname : %’, row.oid, row.datname;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— Execution result
postgres=# DO $$
postgres$# DECLARE
postgres$# row RECORD;
postgres$# BEGIN
postgres$# FOR row IN SELECT * FROM pg_database
postgres$# LOOP
postgres$# CONTINUE WHEN row.oid=1;
postgres$# RAISE NOTICE ‘oid : % | datname : %’, row.oid, row.datname;
postgres$# END LOOP;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
NOTICE: oid : 14486 | datname : postgres
NOTICE: oid : 14485 | datname : template0
DO

FOREACH IN ARRAY Loop

A loop that iterates over elements in the array specified in IN ARRAY.
Like other loops, CONTINUE and WHEN can be used.

SLICE can be used to iterate over elements of multidimensional arrays.
To iterate over elements of multidimensional arrays, the loop variable must be of ARRAY type.

[ <> ]
FOREACH target [ SLICE number ] IN ARRAY expression LOOP
statements
END LOOP [ label ];

FOREACH IN ARRAY Loop Example

DO $$
DECLARE
x int[] := ARRAY[1,2,3,5,6];
i int;
BEGIN
FOREACH i IN ARRAY x
LOOP
RAISE NOTICE ‘row : %’,i;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— Execution result
postgres=# DO $$
postgres$# DECLARE
postgres$# x int[] := ARRAY[1,2,3,5,6];
postgres$# i int;
postgres$# BEGIN
postgres$# FOREACH i IN ARRAY x
postgres$# LOOP
postgres$# RAISE NOTICE ‘row : %’,i;
postgres$# END LOOP;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
NOTICE: row : 1
NOTICE: row : 2
NOTICE: row : 3
NOTICE: row : 5
NOTICE: row : 6
DO

FOREACH IN ARRAY Loop Example – Multidimensional Array

DO $$
DECLARE
x int[] := ARRAY[[1,2],[3,4],[5,6],[7,8]];
i int[];
BEGIN
FOREACH i SLICE 1 IN ARRAY x
LOOP
RAISE NOTICE ‘row : %’,i;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— Execution result
postgres=# DO $$
postgres$# DECLARE
postgres$# x int[] := ARRAY[[1,2],[3,4],[5,6],[7,8]];
postgres$# i int[];
postgres$# BEGIN
postgres$# FOREACH i SLICE 1 IN ARRAY x
postgres$# LOOP
postgres$# RAISE NOTICE ‘row : %’,i;
postgres$# END LOOP;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
NOTICE: row : {1,2}
NOTICE: row : {3,4}
NOTICE: row : {5,6}
NOTICE: row : {7,8}
DO