A PL/pgSQL function in PostgreSQL
Tags: postgres, plpgsqlfunction, plpgsql, function
Published: 2023-12-28 16:51:52

### A [PL/pgSQL function](https://www.postgresql.org/docs/current/plpgsql.html):

- can have zero or more queries with `BEGIN ... END` statement getting zero or more values with zero or more parameters from the caller. *Be careful, `BEGIN ... END` clause is not [transaction](https://www.postgresql.org/docs/current/tutorial-transactions.html).

- can have `IN`, `OUT`, `INOUT` and `VARIADIC` parameters. *[My answer](https://stackoverflow.com/questions/33802583/what-does-variadic-declarations-mean-in-postgresql/77724225#77724225) explains a `VARIADIC` parameter.

- can have [DECLARE clause](https://www.postgresql.org/docs/current/plpgsql-declarations.html). *[My post](https://dev.to/hyperkai/declarations-in-postgresql-357o) explains it.

- can have type-only parameters. *[My post](https://dev.to/hyperkai/declarations-in-postgresql-357o) explains it.

- can have the aliases of parameters e.g. `$1`, `$2`, etc. *[My post](https://dev.to/hyperkai/declarations-in-postgresql-357o) explains it.

- can have [SELECT INTO statement](https://www.postgresql.org/docs/current/sql-selectinto.html).

- can have [RETURN NEXT](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-RETURN-NEXT) and [RETURN QUERY statement](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-RETURN-NEXT).

  - [My post](https://dev.to/hyperkai/return-next-statement-in-postgresql-1lmi) explains `RETURN NEXT` statement.

  - [My post](https://dev.to/hyperkai/return-query-statement-in-postgresql-2j4i) explains `RETURN QUERY` statement.

- can have [PERFORM statement](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL).

- can have [EXECUTE statement](https://www.postgresql.org/docs/current/sql-execute.html). *[My post](https://dev.to/hyperkai/prepare-and-execute-statements-in-postgresql-3bhc) explains how to use `EXECUTE` statement in a function.

- cannot have [SELECT statement](https://www.postgresql.org/docs/current/sql-select.html) without `INTO` or [INSERT](https://www.postgresql.org/docs/current/sql-insert.html), [UPDATE](https://www.postgresql.org/docs/current/sql-update.html) or [DELETE](https://www.postgresql.org/docs/current/sql-delete.html) statement with [RETURNING clause](https://www.postgresql.org/docs/current/dml-returning.html) otherwise there is [the error](https://stackoverflow.com/questions/23946735/postgresql-query-has-no-destination-for-result-data#77642993) as a non-substatement.

- can return a value with [RETURN statement](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-RETURN), an `OUT` or `INOUT` parameters to the caller. 

- is atomic by default running in a single transaction so if there is error, it is rollbacked automatically. *[My answer](https://stackoverflow.com/questions/12778209/are-postgresql-functions-transactional#77637176) explains it.

- cannot control transaction with `START TRANSACTION`, `BEGIN`, `ROLLBACK`, `COMMIT`, etc otherwise there is error.

- is similar to a [MySQL function](https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html).

*[The doc](https://www.postgresql.org/docs/current/plpgsql.html) explains a PL/pgSQL function.
*[My post](https://dev.to/hyperkai/a-sql-function-in-postgresql-54oh) explains a SQL function.
*[My post](https://dev.to/hyperkai/a-plpgsql-procedure-31nd) explains a PL/pgSQL procedure.
*[My post](https://dev.to/hyperkai/a-sql-procedure-in-postgresql-41e0) explains a SOL procedure.
*[My post](https://dev.to/hyperkai/do-statement-in-postgresql-533p) explains `DO` statement.

*You should use PL/pgSQL language because you can do much more things with it compared with SQL language.

For example, you create `test` table as shown below:

```sql
CREATE TABLE test (
  num INT
);
```

Then, you insert the row whose `num` is `2` into `test` table as shown below:

```sql
INSERT INTO test (num) VALUES (2);
```

Now, you can create `my_func()` function which adds `value` to `num` and returns `value` to the caller as shown below:

```sql
CREATE FUNCTION my_func(value INT) RETURNS INT
AS $$
BEGIN
  UPDATE test SET num = num + value;
  SELECT num INTO value FROM test;
  RETURN value;
END;
$$ LANGUAGE plpgsql;
```

Or, you can change the position of `LANGUAGE plpgsql` as shown below:

```sql
CREATE FUNCTION my_func(value INT) RETURNS INT
LANGUAGE plpgsql -- Here
AS $$
BEGIN
  UPDATE test SET num = num + value;
  SELECT num INTO value FROM test;
  RETURN value;
END;
$$;
```

*Memos:

- You must set `RETURNS <type>` clause or `OUT` or `INOUT` parameters which I explain later otherwise there is error.

- You must set `AS <delimiter>` clause and `BEGIN ... END` clause to a PL/pgSQL function otherwise there is error.

- `RETURNS <type>` clause can have `VOID` type to return nothing.

- You can also use other delimiter ['](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS) instead of [$$](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING) to create the body of a PL/pgSQL function. *[My answer](https://stackoverflow.com/questions/12144284/what-are-used-for-in-pl-pgsql/77657886#77657886) explains it.

- You can use `SELECT INTO` statement to put a retrieved value into a variable.

- If a return value type doesn't match `RETURNS <type>` clause or `OUT` or `INOUT` parameters, there is error.

- If you unset `RETURN` statement when `RETURNS <type>` clause is not `VOID` and an `OUT` or `INOUT` parameter is not used, there is [the error](https://stackoverflow.com/questions/26907118/control-reached-end-of-function-without-return/77726870#77726870).

- You must set `LANGUAGE plpgsql` to create a PL/pgSQL function otherwise there is [the error](https://stackoverflow.com/questions/39607334/postgres-function-creation-error-no-language-specified-sql-state-42p13#77636611).

- You can set `LANGUAGE plpgsql` in 2 positions as shown above.

Then, you can call `my_func(3)` with [SELECT statement](https://www.postgresql.org/docs/current/sql-select.html), then `5` is returned and `3` is added to `num` as shown below:

```sql
postgres=# SELECT my_func(3);
 my_func
---------
       5
(1 row)

postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)
```

And, you can use `VOID` type with or without `RETURN;` to return nothing as shown below. *Using `VOID` type with `RETURN <some-value>;` in a PL/pgSQL function gets [the error](https://stackoverflow.com/questions/17698503/plpgsql-error-return-cannot-have-a-parameter-in-function-returning-void/77609986#77609986) while a SQL function doesn't get the error:

```sql
CREATE FUNCTION my_func(value INT) RETURNS VOID
AS $$                                    -- ↑ Here
BEGIN
  UPDATE test SET num = num + value;
  SELECT num INTO value FROM test;
  RETURN; -- Here
END;
$$ LANGUAGE plpgsql;
```

Or:

```sql
CREATE FUNCTION my_func(value INT) RETURNS VOID
AS $$                                    -- ↑ Here
BEGIN
  UPDATE test SET num = num + value;
  SELECT num INTO value FROM test;
  -- RETURN; -- Here
END;
$$ LANGUAGE plpgsql;
```

Then, calling `my_func(3)` returns nothing and `3` is added to `num` as shown below:

```sql
postgres=# SELECT my_func(3);
 my_func
---------

(1 row)

postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)
```

And, you can use `PERFORM` statement as shown below:

```sql
CREATE FUNCTION my_func(value INT) RETURNS INT
AS $$
BEGIN
  UPDATE test SET num = num + value;
  PERFORM num FROM test; -- Here
  RETURN 10;
END;
$$ LANGUAGE plpgsql;
```

Then, calling `my_func(3)` returns `10` and `3` is added to `num` as shown below:

```sql
postgres=# SELECT my_func(3);
 my_func
---------
      10
(1 row)

postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)
```

In addition, you can use `IN`, `OUT` and `INOUT` parameters in a PL/pgSQL function as shown below.

An `IN` parameter can get a value from the caller but cannot return a value to the caller. *The parameter with and without `IN` is the same so `my_func(IN value INT)` and `my_func(value INT)` are the same: 

```sql
CREATE FUNCTION my_func(IN value INT) RETURNS INT
AS $$                -- ↑↑ Here
BEGIN
  UPDATE test SET num = num + value;
  SELECT num INTO value FROM test;
  RETURN value;
END;
$$ LANGUAGE plpgsql;
```

Then, calling `my_func(3)` returns `5` and `3` is added to `num` as shown below:

```sql
postgres=# SELECT my_func(3);
 my_func
---------
       5
(1 row)

postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)
```

An `OUT` parameter can return a value to the caller but cannot get a value from the caller:

```sql
CREATE FUNCTION my_func(OUT value INT) RETURNS INT
AS $$                 -- ↑ Here
BEGIN
  UPDATE test SET num = num + 3;
  SELECT num INTO value FROM test;
  RETURN;
END;
$$ LANGUAGE plpgsql;
```

Or:

```sql
CREATE FUNCTION my_func(OUT value INT) /* RETURNS INT */
AS $$                 -- ↑ Here         -- ↑ Unset ↑
BEGIN
  UPDATE test SET num = num + 3;
  SELECT num INTO value FROM test;
  -- RETURN; -- Unset
END;
$$ LANGUAGE plpgsql;
```

*Memos:

- An `OUT` parameter and `RETURNS <type>` clause are the same except that an `OUT` parameter cannot have `VOID` type in a PL/pgSQL function while an `OUT` parameter can in a SQL function.

- When you set an `OUT` parameter, you can unset `RETURNS <type>` clause and `RETURN` statement.

- You can still set `RETURNS <type>` clause and `RETURN` statement with an `OUT` parameter but the types of an `OUT` parameter and `RETURNS <type>` clause must be the same otherwise there is [the error](https://stackoverflow.com/questions/39283412/function-result-type-must-be-integer-because-of-out-parameters-in-postgresql#77615323) and `RETURN` statement must be `RETURN;` to return nothing otherwise there is [the error](https://stackoverflow.com/questions/45725699/return-cannot-have-a-parameter-in-function-with-out-parameters#77616607).

- Passing a value to an `OUT` parameter gets [the error](https://stackoverflow.com/questions/24751372/error-function-does-not-exist-and-hint-no-function-matches-the-given-name#77618099).

- Setting `VOID` type to an `OUT` parameter gets error.

Then, calling `my_func()` returns `5` and `3` is added to `num` as shown below:

```sql
postgres=# SELECT my_func();
 my_func
---------
       5
(1 row)

postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)
```

An `INOUT` parameter is the combination of `IN` and `OUT` parameters to get a value from the caller and to return a value to the caller: 

```sql
CREATE FUNCTION my_func(INOUT value INT) RETURNS INT
AS $$                  -- ↑ Here
BEGIN
  UPDATE test SET num = num + value;
  SELECT num INTO value FROM test;
  RETURN;
END;
$$ LANGUAGE plpgsql;
```

Or:

```sql
CREATE FUNCTION my_func(INOUT value INT) /* RETURNS INT */
AS $$                  -- ↑ Here          -- ↑ Unset ↑
BEGIN
  UPDATE test SET num = num + value;
  SELECT num INTO value FROM test;
  -- RETURN; -- Unset
END;
$$ LANGUAGE plpgsql;
```

Then, calling `my_func(3)` returns `5` and `3` is added to `num` as shown below:

```sql
postgres=# SELECT my_func(3);
 my_func
---------
       5
(1 row)

postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)
```