# Lesson 15: PL/pgSQL / Arrays

> Native Markdown source page: PL/pgSQL / Arrays

---

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

---

<!-- Generated by scripts/generate_skeleton.py. -->

> [!NOTE]
> This page is extracted from the official PDF into native Markdown. Slide text, code and speaker notes are kept in reading order; the original PDF and HTML remain the layout references.
> **HTML source:** [dev1_15_plpgsql_arrays.html](https://edu.postgrespro.com/16/dev1-16-en/dev1_15_plpgsql_arrays.html)
> **Archive:** [DEV1-handouts-16-en.zip](https://edu.postgrespro.com/16/DEV1-handouts-16-en.zip)
> **PDF pages:** 24

## Page 1: PL/pgSQL / Arrays {#source-page-001}

### Copyright

© Postgres Professional, 2017–2025 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova Photo by: Oleg Bartunov (Phu monastery, Bhrikuti summit, Nepal)
### Use of Course Materials

Non-commercial use of course materials (presentations, demonstrations) is allowed without restrictions. Commercial use is possible only with the written permission of Postgres Professional. It is prohibited to make changes to the course materials.
### Feedback

Please send your feedback, comments and suggestions to: edu@postgrespro.ru
### Disclaimer

Postgres Professional assumes no responsibility for any damages and losses, including loss of income, caused by direct or indirect, intentional or accidental use of course materials. Postgres Professional company specifically disclaims any warranties on course materials. Course materials are provided “as is,” and Postgres Professional company has no obligations to provide maintenance, support, updates, enhancements, or modifications.

## Page 2: Topics {#source-page-002}

- Arrays and Their Usage in PL/pgSQL
- Loops over Array Elements
- Functions with a Variable Number of Arguments and Polymorphic Functions
- Array Usage in Tables

## Page 3: Array Types {#source-page-003}

**Array**
  - a set of numbered elements of the same type
  - one-dimensional, multidimensional
- Initialization
```text
usage without an explicit declaration (type_name[])
implicit declaration when creating a base type or a table (_type_name)
```
- Usage
  - elements as scalar values
  - array slices
  - operations on arrays: comparison, inclusion, intersection, concatenation,
  - usage with ANY or ALL instead of a subquery, etc.

### Speaker notes

Just like a composite type (a record), an array is not a scalar; it consists of several elements of another type. But unlike in records, a) all these elements are of the same type, and b) they are accessed by an integer index, not by name (here the term index is used in the mathematical sense of the word, not in the sense of a database index).

An array type does not have to be explicitly declared. When any type (or a table) is created, a new array type named \_type is also declared. To declare an array variable, all you need is to append square brackets to the name of the element type.

An array is a full-fledged SQL type: you can create table columns of this type, pass arrays as function arguments, and so on. Array elements can be used as regular scalar values. Array slices can also be used.

Arrays can be compared and checked for NULL; you can search arrays for element inclusion and intersection with other arrays, perform concatenation, etc. Arrays can also be applied in ANY/SOME and ALL constructs, similar to subqueries.

<https://postgrespro.com/docs/postgresql/16/arrays>

You can find various array functions in course handouts.

## Page 4: Initializing an Array and Referencing its Elements {#source-page-004}

Declaring a variable and initializing an array:
```text
=> DO $$
DECLARE
    a integer[2]; -- the size is ignored
BEGIN
    a := ARRAY[10,20,30];
    RAISE NOTICE '%', a;
    -- by default, one-based indexing is used
    RAISE NOTICE 'a[1] = %, a[2] = %, a[3] = %', a[1], a[2], a[3];
    -- array slice
    RAISE NOTICE 'Slice [2:3] = %', a[2:3];
    -- assign values to the array slice
    a[2:3] := ARRAY[222,333];
    -- output the array
    RAISE NOTICE '%', a;
END;
$$ LANGUAGE plpgsql;
```
```text
NOTICE:  {10,20,30}
NOTICE:  a[1] = 10, a[2] = 20, a[3] = 30
NOTICE:  Slice [2:3] = {20,30}
NOTICE:  {10,222,333}
DO
```
A one-dimensional array can be constructed element by element: it will be expanded automatically if required. If you omit some of the elements, they will receive NULL values.
What will be displayed?
```text
=> DO $$
DECLARE
    a integer[];
BEGIN
    a[2] := 10;
    a[3] := 20;
    a[6] := 30;
    RAISE NOTICE '%', a;
END;
$$ LANGUAGE plpgsql;
```
```text
NOTICE:  [2:6]={10,20,NULL,NULL,30}
DO
```
Since element numbering begins with a value other than one, the array itself is preceded by a range of element indexes.
We can define a composite type and create an array of this type:
```sql
=> CREATE TYPE currency AS (amount numeric, code text);
```
```sql
CREATE TYPE
```
```text
=> DO $$
DECLARE
    c currency[];  -- composite type array
BEGIN
    -- assign values to array elements
    c[1].amount := 10;  c[1].code := 'RUB';
    c[2].amount := 50;  c[2].code := 'KZT';
    RAISE NOTICE '%', c;
END
$$ LANGUAGE plpgsql;
```
```text
NOTICE:  {"(10,RUB)","(50,KZT)"}
DO
```
Another way to construct an array is to use a subquery:
```sql
=> DO $$
DECLARE
    a integer[];
BEGIN
    a := ARRAY( SELECT n FROM generate_series(1,3) n );
    RAISE NOTICE '%', a;
END
$$ LANGUAGE plpgsql;
```

## Page 5: Page 5 {#source-page-005}

```text
NOTICE:  {1,2,3}
DO
```
You can also do the inverse operation: convert an array into a table:
```sql
=> SELECT unnest( ARRAY[1,2,3] );
```
```text
 unnest
--------
      1
      2
      3
(3 rows)
```
Fun fact: the IN clause with a list of values is transformed into a search operation over the array:
```sql
=> EXPLAIN (costs off)
SELECT * FROM generate_series(1,10) g(id) WHERE id IN (1,2,3);
```
```text
                 QUERY PLAN
---------------------------------------------
 Function Scan on generate_series g
   Filter: (id = ANY ('{1,2,3}'::integer[]))
(2 rows)
```
A two-dimensional array is a rectangle matrix. Memory for the matrix is allocated at initialization. A literal looks like an array of arrays with the same number of elements. Here we have applied another initialization method that uses a string literal.
Once initialized, a multidimensional array cannot be expanded.
```text
=> DO $$
DECLARE
    a integer[][] := '{
        { 10, 20, 30},
        {100,200,300}
    }';
BEGIN
    RAISE NOTICE 'Two-dimensional array : %', a;
    RAISE NOTICE 'Unlimited array slice [2:] = %', a[2:];
    -- assign values to the slice
    a[2:] := ARRAY[ARRAY[111, 222, 333]];
    -- output the whole array again
    RAISE NOTICE '%', a;
    -- cannot be expanded in any dimension
    a[4][4] := 1;
END
$$ LANGUAGE plpgsql;
```
```text
NOTICE:  Two-dimensional array : {{10,20,30},{100,200,300}}
NOTICE:  Unlimited array slice [2:] = {{100,200,300}}
NOTICE:  {{10,20,30},{111,222,333}}
ERROR:  array subscript out of range
CONTEXT:  PL/pgSQL function inline_code_block line 15 at assignment
```

## Page 6: Arrays and Loops {#source-page-006}

**A regular loop over element indexes**
  - array\_lower
  - array\_upper
- A FOREACH loop over array elements
  - this approach is easier, but it does not provide access to indexes

### Speaker notes

To iterate through array elements, you can simply set up an integer FOR loop using functions that return the minimum and the maximum index of the array.

But there is also a specialized loop: FOREACH. In this case, a loop variable iterates through the elements, not their indexes. That’s why the variable must be of the same type as the array elements (as always, if the elements are records, you can replace a single composite variable with several scalar ones).

If a loop contains the SLICE clause, it will iterate through array slices. For example, the rows of a two-dimensional array will be treated as its one-dimensional slices.

<https://postgrespro.com/docs/postgresql/16/plpgsql-control-structures#PLP> GSQL-FOREACH-ARRAY

## Page 7: Arrays and Loops {#source-page-007}

You can set up a loop that iterates through index values of array elements. The second parameter of the array\_lower and array\_upper functions defines the array dimension (1 denotes a one-dimensional array).
```text
=> DO $$
DECLARE
    a integer[] := ARRAY[10,20,30];
BEGIN
    FOR i IN array_lower(a,1)..array_upper(a,1) LOOP
        RAISE NOTICE 'a[%] = %', i, a[i];
    END LOOP;
END
$$ LANGUAGE plpgsql;
```
```text
NOTICE:  a[1] = 10
NOTICE:  a[2] = 20
NOTICE:  a[3] = 30
DO
```
If you do not need to know index values, it’s easier to iterate directly though the elements:
```text
=> DO $$
DECLARE
    a integer[] := ARRAY[10,20,30];
    x integer;
BEGIN
    FOREACH x IN ARRAY a LOOP
        RAISE NOTICE '%', x;
    END LOOP;
END
$$ LANGUAGE plpgsql;
```
```text
NOTICE:  10
NOTICE:  20
NOTICE:  30
DO
```
Index iteration in a two-dimensional array:
```text
=> DO $$
DECLARE
    -- double square brackets are optional
    a integer[] := ARRAY[
        ARRAY[ 10, 20, 30],
        ARRAY[100,200,300]
];
BEGIN
    FOR i IN array_lower(a,1)..array_upper(a,1) LOOP -- over rows
        FOR j IN array_lower(a,2)..array_upper(a,2) LOOP -- over columns
            RAISE NOTICE 'a[%][%] = %', i, j, a[i][j];
        END LOOP;
    END LOOP;
END
$$ LANGUAGE plpgsql;
```
```text
NOTICE:  a[1][1] = 10
NOTICE:  a[1][2] = 20
NOTICE:  a[1][3] = 30
NOTICE:  a[2][1] = 100
NOTICE:  a[2][2] = 200
NOTICE:  a[2][3] = 300
DO
```
You do not need a nested loop to iterate through the elements of a two-dimensional array:

## Page 8: Page 8 {#source-page-008}

```text
=> DO $$
DECLARE
    a integer[] := ARRAY[
        ARRAY[ 10, 20, 30],
        ARRAY[100,200,300]
    ];
    x integer;
BEGIN
    FOREACH x IN ARRAY a LOOP
        RAISE NOTICE '%', x;
    END LOOP;
```
```text
END
$$ LANGUAGE plpgsql;
```
```text
NOTICE:  10
NOTICE:  20
NOTICE:  30
NOTICE:  100
NOTICE:  200
NOTICE:  300
DO
```
You can also loop through slices, not just individual elements, in a similar way. The SLICE value must be an integer, no larger than the array size, and the variable to store the slices into must be an array itself. Here is a loop over one-dimensional slices as an example:
```text
=> DO $$
DECLARE
    a integer[] := ARRAY[
        ARRAY[ 10, 20, 30],
        ARRAY[100,200,300]
    ];
    x integer[];
BEGIN
    FOREACH x SLICE 1 IN ARRAY a LOOP
        RAISE NOTICE '%', x;
    END LOOP;
END
$$ LANGUAGE plpgsql;
```
```text
NOTICE:  {10,20,30}
NOTICE:  {100,200,300}
DO
```

## Page 9: Arrays and Routines {#source-page-009}

**Routines with a variable number of arguments**
  - all optional arguments must be of the same type
  - optional arguments are passed to the routine as an array
  - the last parameter array is defined by the VARIADIC mode
- Polymorphic routines
  - support arguments of various types;
  - the actual type is defined at run time
  - can use additional polymorphic pseudotypes anyarray, anynonarray,
  - anycompatiblearray and anycompatiblenonarrray
  - can have a variable number of arguments

### Speaker notes

Using arrays, you can create routines (functions and procedures) with a variable number of arguments.

While parameters with default values have to be explicitly specified in routine declaration, optional arguments can be passed with no limit: they are provided as an array. Consequently, all of them must be of the same type (or a compatible one, if anycompatible or anycompatiblearray is used).

The last parameter in routine declaration must be marked as VARIADIC; it must be of an array type.

<https://postgrespro.com/docs/postgresql/16/xfunc-sql#XFUNC-SQL-VARIAD> IC-FUNCTIONS

We have already mentioned polymorphic routines which can accept arguments of various types. Routine declaration uses a special polymorphic pseudotype, while the actual type is defined at run time based on the types of the passed arguments.

There are special polymorphic types anyarray and anycompatiblearray (and anynonarray and anycompatiblenonarrray for non-arrays).

These types can be used when passing a variable number of arguments via a VARIADIC parameter.

<https://postgrespro.com/docs/postgresql/16/xfunc-sql#XFUNC-SQL-POLYM> ORPHIC-FUNCTIONS

## Page 10: Arrays and Routines {#source-page-010}

When discussing polymorphism and overloading as part of the SQL. Procedures lecture, we created the maximum function to compare three numbers and find the greatest one. Now let’s generalize this function, so that it can be used with an arbitrary number of arguments. For this purpose, we’ll declare a single VARIADIC parameter:
```sql
=> CREATE FUNCTION maximum(VARIADIC a integer[]) RETURNS integer
AS $$
DECLARE
    x integer;
    maxsofar integer;
BEGIN
    FOREACH x IN ARRAY a LOOP
        IF x IS NOT NULL AND (maxsofar IS NULL OR x > maxsofar) THEN
            maxsofar := x;
        END IF;
    END LOOP;
    RETURN maxsofar;
END
$$ IMMUTABLE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
Let’s try it out:
```sql
=> SELECT maximum(12, 65, 47);
```
```text
 maximum
---------
      65
(1 row)
```
```sql
=> SELECT maximum(12, 65, 47, null, 87, 24);
```
```text
 maximum
---------
      87
(1 row)
```
```sql
=> SELECT maximum(null, null);
```
```text
 maximum
---------
(1 row)
```
To complete this illustration, we can make this function polymorphic as well, so that it takes any data type (which supports comparison operators, of course).
```sql
=> DROP FUNCTION maximum(integer[]);
```
```sql
DROP FUNCTION
```
Polymorphic types anycompatiblearray and anycompatible (also anyarray and anyelement) must match each other: anycompatiblearray = anycompatible&#91;&#93;, anyarray = anyelement&#91;&#93;; We need a variable of the same type as an array element. But it cannot be declared as anycompatible: it must have an actual type. The %TYPE construct helps us out here.
```sql
=> CREATE FUNCTION maximum(VARIADIC a anycompatiblearray, maxsofar OUT anycompatible)
AS $$
DECLARE
    x maxsofar%TYPE;
BEGIN
    FOREACH x IN ARRAY a LOOP
        IF x IS NOT NULL AND (maxsofar IS NULL OR x > maxsofar) THEN
            maxsofar := x;
        END IF;
    END LOOP;
END
$$ IMMUTABLE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
Let’s check the result:

## Page 11: Page 11 {#source-page-011}

```sql
=> SELECT maximum(12, 65, 47);
```
```text
 maximum
---------
      65
(1 row)
```
```sql
=> SELECT maximum(12.1, 65.3, 47.6);
```
```text
 maximum
---------
    65.3
(1 row)
```
```sql
=> SELECT maximum(12, 65.3, 15e2, 3.14);
```
```text
 maximum
---------
    1500
(1 row)
```
Now our function is almost completely analogous to the greatest expression provided in SQL.

## Page 12: An Array or a Table? {#source-page-012}

![Source slide page 12: An Array or a Table?](images/page-012.png){#source-figure-012 caption="Source slide page 12: An Array or a Table?"}

```text
1
...
{A}
```
```text
2
...
{B,C,D}
```
```text
3
...
{A,C}
```
```text
1
1
```
```text
1
A
...
```
```text
2
2
```
```text
1
...
```
```text
2
B
...
```
```text
2
3
```
```text
2
...
```
```text
2
4
```
```text
3
C
...
```
```text
3
...
```
```text
3
1
```
```text
4
D
...
```
```text
3
3
```

### Speaker notes

A traditional relational approach assumes that a table stores atomic values (first normal form). The SQL language has no tools for peeking into composite values.

That’s why a traditional approach relies on creating an additional table connected to the main one by a many-to-many relationship.

Nevertheless, we can create a table with a column of an array type. PostgreSQL offers a rich set of array functions; the search for an array element can be sped up using special indexes (covered in the DEV2 course).

This approach can be convenient: we get a concise representation that does not require any joins. For example, arrays are extensively used in PostgreSQL system catalog.

The choice of approach depends on the goals and the operations required. Consider the example in the demo.

## Page 13: An Array or a Table? {#source-page-013}

Imagine that we are designing a database for writing a blog. The blog contains some posts, and we would like to tag them.
The traditional approach is to create a separate table for tags. For example:
```sql
=> CREATE TABLE posts(
    post_id integer PRIMARY KEY,
    message text
);
```
```sql
CREATE TABLE
```
```sql
=> CREATE TABLE tags(
    tag_id integer PRIMARY KEY,
    name text
);
```
```sql
CREATE TABLE
```
Let’s connect posts and tags by a many-to-many relationship via an additional table:
```sql
=> CREATE TABLE posts_tags(
    post_id integer REFERENCES posts(post_id),
    tag_id integer REFERENCES tags(tag_id)
);
```
```sql
CREATE TABLE
```
Let’s fill our tables with text data:
```sql
=> INSERT INTO posts(post_id,message) VALUES
    (1, 'I set my password to “incorrect”.'),
    (2, 'Your future is whatever you make it, so make it a good one.');
```
```sql
INSERT 0 2
```
```sql
=> INSERT INTO tags(tag_id,name) VALUES
    (1, 'my past and thoughts'), (2, 'technology'), (3, 'family');
```
```sql
INSERT 0 3
```
```sql
=> INSERT INTO posts_tags(post_id,tag_id) VALUES
    (1,1), (1,2), (2,1), (2,3);
```
```sql
INSERT 0 4
```
Now we can display posts and tags:
```sql
=> SELECT p.message, t.name
FROM posts p
     JOIN posts_tags pt ON pt.post_id = p.post_id
     JOIN tags t ON t.tag_id = pt.tag_id
ORDER BY p.post_id, t.name \gx
```
```text
-[ RECORD 1 ]--------------------------------------------------------
message | I set my password to “incorrect”.
name    | my past and thoughts
-[ RECORD 2 ]--------------------------------------------------------
message | I set my password to “incorrect”.
name    | technology
-[ RECORD 3 ]--------------------------------------------------------
message | Your future is whatever you make it, so make it a good one.
name    | family
-[ RECORD 4 ]--------------------------------------------------------
message | Your future is whatever you make it, so make it a good one.
name    | my past and thoughts
```
Or we can do it a bit differently to get an array of tags. We are going to use an aggregate function for this purpose:
```sql
=> SELECT p.message, array_agg(t.name ORDER BY t.name) tags
FROM posts p
    JOIN posts_tags pt ON pt.post_id = p.post_id
    JOIN tags t ON t.tag_id = pt.tag_id
GROUP BY p.post_id
ORDER BY p.post_id \gx
```

## Page 14: Page 14 {#source-page-014}

```text
-[ RECORD 1 ]--------------------------------------------------------
message | I set my password to “incorrect”.
tags    | {"my past and thoughts",technology}
-[ RECORD 2 ]--------------------------------------------------------
message | Your future is whatever you make it, so make it a good one.
tags    | {family,"my past and thoughts"}
```
We can find all posts with a particular tag:
```sql
=> SELECT p.message
FROM posts p
    JOIN posts_tags pt ON pt.post_id = p.post_id
    JOIN tags t ON t.tag_id = pt.tag_id
WHERE t.name = 'my past and thoughts'
ORDER BY p.post_id;
```
```text
                           message
-------------------------------------------------------------
 I set my password to “incorrect”.
 Your future is whatever you make it, so make it a good one.
(2 rows)
```
We may also need to find all the unique tags, and it is really easy:
```sql
=> SELECT t.name
FROM tags t
ORDER BY t.name;
```
```text
         name
----------------------
 family
 my past and thoughts
 technology
(3 rows)
```
Now let’s try another approach to this task. Suppose the tags are stored as a text array right inside the table with posts.
```sql
=> DROP TABLE posts_tags;
```
```sql
DROP TABLE
```
```sql
=> DROP TABLE tags;
```
```sql
DROP TABLE
```
```sql
=> ALTER TABLE posts ADD COLUMN tags text[];
```
```sql
ALTER TABLE
```
There are no tag IDs, but we don’t really need them.
```sql
=> UPDATE posts SET tags = '{"my past and thoughts","technology"}'
WHERE post_id = 1;
```
```sql
UPDATE 1
```
```sql
=> UPDATE posts SET tags = '{"my past and thoughts","family"}'
WHERE post_id = 2;
```
```sql
UPDATE 1
```
Now it’s easier to display all posts:
```sql
=> SELECT p.message, p.tags
FROM posts p
ORDER BY p.post_id \gx
```
```text
-[ RECORD 1 ]--------------------------------------------------------
message | I set my password to “incorrect”.
tags    | {"my past and thoughts",technology}
-[ RECORD 2 ]--------------------------------------------------------
message | Your future is whatever you make it, so make it a good one.
tags    | {"my past and thoughts",family}
```
It is also easy to find all posts with the same tag (using the intersection operator &&).
This operation can be sped up using GIN index, and the query won’t require searching through the whole table of posts.

## Page 15: Page 15 {#source-page-015}

```sql
=> SELECT p.message
FROM posts p
```
```sql
WHERE p.tags && '{"my past and thoughts"}'
ORDER BY p.post_id;
```
```text
                           message
-------------------------------------------------------------
 I set my password to “incorrect”.
 Your future is whatever you make it, so make it a good one.
(2 rows)
```
But it is quite hard to get the list of all tags. It requires unnesting all the tag arrays into a big table, and the search for unique values in this table is quite resource-intensive.
```sql
=> SELECT DISTINCT unnest(p.tags) AS name
FROM posts p;
```
```text
         name
----------------------
 family
 technology
 my past and thoughts
(3 rows)
```
We can clearly see data duplication here.
Thus, both approaches have the right to be applied.
In simple cases, arrays look more straightforward and work well.
In more complex scenarios (imagine that we would like to store the date of tag creation together with its name, or we need to use check constraints), the traditional approach becomes more preferable.

## Page 16: Takeaways {#source-page-016}

**An array consists of numbered elements of the same data type**
- An array column is an alternative to a separate table: it offers convenient operations on arrays and index support
- Arrays enable you to create functions with a variable number of arguments

## Page 17: Practice {#source-page-017}

1. Create a function add\_book for adding a new book.
  - The function must take two arguments: the name of the book and
  - an array of author IDs. It must return the ID of the added book.
  - Check that the application now allows adding books.
1.
```text
FUNCTION add_book(title text, authors integer[])
RETURNS integer
```

## Page 18: 1. The add_book Function {#source-page-018}

```sql
=> CREATE FUNCTION add_book(title text, authors integer[])
RETURNS integer
AS $$
DECLARE
    book_id integer;
    id integer;
    seq_num integer := 1;
BEGIN
    INSERT INTO books(title)
        VALUES(title)
        RETURNING books.book_id INTO book_id;
    FOREACH id IN ARRAY authors LOOP
        INSERT INTO authorship(book_id, author_id, seq_num)
            VALUES (book_id, id, seq_num);
        seq_num := seq_num + 1;
    END LOOP;
    RETURN book_id;
END
$$ VOLATILE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```

## Page 19: Practice+ {#source-page-019}

1. Implement a function map that takes two arguments: array of real numbers and the name of an additional function that takes a single parameter of the real type.
  - The map function must apply the additional function to each
  - element of the array and return the result.
- 2. Implement a function reduce that takes two arguments: an array of real numbers and the name of an additional function that takes two arguments of the real type.
  - The function must return a real number calculated by
  - sequentially folding the array from left to right.
- 3. Make the functions map and reduce polymorphic.

### Speaker notes

1. For example:

```text
map(ARRAY[4.0,9.0],'sqrt') → ARRAY[2.0,3.0]
```

2. For example:

```text
reduce(ARRAY[1.0,3.0,2.0,0.5],'greatest') → 3.0
```

In this case, the value is calculated as follows:

```text
greatest( greatest( greatest(1.0,3.0), 2.0 ), 0.5 )
```

## Page 20: 1. The map Function {#source-page-020}

```sql
=> CREATE DATABASE plpgsql_arrays;
```
```sql
CREATE DATABASE
```
```sql
=> \c plpgsql_arrays
```
```text
You are now connected to database "plpgsql_arrays" as user "student".
```
```sql
=> CREATE FUNCTION map(a INOUT float[], func text)
AS $$
DECLARE
    i integer;
    x float;
BEGIN
    IF cardinality(a) > 0 THEN
        FOR i IN array_lower(a,1)..array_upper(a,1) LOOP
            EXECUTE format('SELECT %I($1)',func) USING a[i] INTO x;
            a[i] := x;
        END LOOP;
    END IF;
END
$$ IMMUTABLE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
INTO a&#91;i&#93; doesn’t work, need a separate variable.
```sql
=> SELECT map(ARRAY[4.0,9.0,16.0],'sqrt');
```
```text
   map
---------
 {2,3,4}
(1 row)
```
```sql
=> SELECT map(ARRAY[]::float[],'sqrt');
```
```text
 map
-----
 {}
(1 row)
```
Another implementation with a FOREACH loop:
```sql
=> CREATE OR REPLACE FUNCTION map(a float[], func text) RETURNS float[]
AS $$
DECLARE
    x float;
    b float[]; -- empty array
BEGIN
    FOREACH x IN ARRAY a LOOP
        EXECUTE format('SELECT %I($1)',func) USING x INTO x;
        b := b || x;
    END LOOP;
    RETURN b;
END
$$ IMMUTABLE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
```sql
=> SELECT map(ARRAY[4.0,9.0,16.0],'sqrt');
```
```text
   map
---------
 {2,3,4}
(1 row)
```
```sql
=> SELECT map(ARRAY[]::float[],'sqrt');
```
```text
 map
-----
(1 row)
```

## Page 21: 2. The reduce Function {#source-page-021}

```sql
=> CREATE FUNCTION reduce(a float[], func text) RETURNS float
AS $$
DECLARE
    i integer;
    r float := NULL;
BEGIN
    IF cardinality(a) > 0 THEN
        r := a[array_lower(a,1)];
        FOR i IN array_lower(a,1)+1 .. array_upper(a,1) LOOP
            EXECUTE format('SELECT %I($1,$2)',func) USING r, a[i]
                INTO r;
        END LOOP;
    END IF;
    RETURN r;
END
$$ IMMUTABLE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
greatest (as well as least) is not a function but a built-in conditional expression, and cannot be used directly due to escaping:
```sql
=> SELECT reduce( ARRAY[1.0,3.0,2.0], 'greatest');
```
```sql
ERROR:  function greatest(double precision, double precision) does not exist
LINE 1: SELECT "greatest"($1,$2)
               ^
HINT:  No function matches the given name and argument types. You might need to add
explicit type casts.
QUERY:  SELECT "greatest"($1,$2)
CONTEXT:  PL/pgSQL function reduce(double precision[],text) line 9 at EXECUTE
```
Instead, use the maximum function implemented in the demo.
```sql
=> CREATE FUNCTION maximum(VARIADIC a anycompatiblearray, maxsofar OUT anycompatible)
AS $$
DECLARE
    x maxsofar%TYPE;
BEGIN
    FOREACH x IN ARRAY a LOOP
        IF x IS NOT NULL AND (maxsofar IS NULL OR x > maxsofar) THEN
            maxsofar := x;
        END IF;
    END LOOP;
END
$$ IMMUTABLE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
```sql
=> SELECT reduce(ARRAY[1.0,3.0,2.0], 'maximum');
```
```text
 reduce
--------
      3
(1 row)
```
```sql
=> SELECT reduce(ARRAY[1.0], 'maximum');
```
```text
 reduce
--------
      1
(1 row)
```
```sql
=> SELECT reduce(ARRAY[]::float[], 'maximum');
```
```text
 reduce
--------
(1 row)
```
The FOREACH option:

## Page 22: Page 22 {#source-page-022}

```sql
=> CREATE OR REPLACE FUNCTION reduce(a float[], func text) RETURNS float
AS $$
DECLARE
    x float;
    r float;
    first boolean := true;
```
```sql
BEGIN
    FOREACH x IN ARRAY a LOOP
        IF first THEN
            r := x;
            first := false;
        ELSE
            EXECUTE format('SELECT %I($1,$2)',func) USING r, x INTO r;
        END IF;
    END LOOP;
    RETURN r;
END
$$ IMMUTABLE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
```sql
=> SELECT reduce(ARRAY[1.0,3.0,2.0], 'maximum');
```
```text
 reduce
--------
      3
(1 row)
```
```sql
=> SELECT reduce(ARRAY[1.0], 'maximum');
```
```text
 reduce
--------
      1
(1 row)
```
```sql
=> SELECT reduce(ARRAY[]::float[], 'maximum');
```
```text
 reduce
--------
(1 row)
```
### 3. Polymorphic Function Variants
The map function.
```sql
=> DROP FUNCTION map(float[],text);
```
```sql
DROP FUNCTION
```
```sql
=> CREATE FUNCTION map(
    a anyarray,
    func text,
    elem anyelement DEFAULT NULL
)
RETURNS anyarray
AS $$
DECLARE
    x elem%TYPE;
    b a%TYPE;
BEGIN
    FOREACH x IN ARRAY a LOOP
        EXECUTE format('SELECT %I($1)',func) USING x INTO x;
        b := b || x;
    END LOOP;
    RETURN b;
END
$$ IMMUTABLE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
A dummy parameter of the anyelement type is required to declare a variable of the same type inside the function.
```sql
=> SELECT map(ARRAY[4.0,9.0,16.0],'sqrt');
```

## Page 23: Page 23 {#source-page-023}

```text
                           map
---------------------------------------------------------
 {2.000000000000000,3.000000000000000,4.000000000000000}
(1 row)
```
```sql
=> SELECT map(ARRAY[]::float[],'sqrt');
```
```text
 map
-----
(1 row)
```
Example with a different data type:
```sql
=> SELECT map(ARRAY[' a ','  b','c  '],'btrim');
```
```text
   map
---------
 {a,b,c}
(1 row)
```
The reduce function.
```sql
=> DROP FUNCTION reduce(float[],text);
```
```sql
DROP FUNCTION
```
```sql
=> CREATE FUNCTION reduce(
    a anyarray,
    func text,
    elem anyelement DEFAULT NULL
)
RETURNS anyelement
AS $$
DECLARE
    x elem%TYPE;
    r elem%TYPE;
    first boolean := true;
BEGIN
    FOREACH x IN ARRAY a LOOP
        IF first THEN
            r := x;
            first := false;
        ELSE
            EXECUTE format('SELECT %I($1,$2)',func) USING r, x INTO r;
        END IF;
    END LOOP;
    RETURN r;
END
$$ IMMUTABLE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
```sql
=> CREATE FUNCTION add(x anyelement, y anyelement) RETURNS anyelement
AS $$
BEGIN
    RETURN x + y;
END
$$ IMMUTABLE LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
```sql
=> SELECT reduce(ARRAY[1,-2,4], 'add');
```
```text
 reduce
--------
      3
(1 row)
```
```sql
=> SELECT reduce(ARRAY['a','b','c'], 'concat');
```
```text
 reduce
--------
 abc
(1 row)
```
```sql
=> \c postgres
```

## Page 24: Page 24 {#source-page-024}

```text
You are now connected to database "postgres" as user "student".
```
```sql
=> DROP DATABASE plpgsql_arrays;
```
```sql
DROP DATABASE
```