Revision c8e1916d-b51e-4ef4-9cdc-9087f523c74e - Stack Overflow

A PL/pgSQL function:

- can run multiple SQL statements with `BEGIN ... END;` statement getting multiple values with multiple parameters from the caller. *Be careful, `BEGIN ... END;` statement is not [transaction][1].

- can have IN, OUT and INOUT parameters.

- can return a value with [RETURN statement][2], an `OUT` or `INOUT` parameters to the caller. 

- cannot use transaction with e.g. `BEGIN;`, `COMMIT;`, etc because there is error.

*[The doc][3] explains a PL/pgSQL function in detail and [my answer][4] explains a [SQL function][5].

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

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

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

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

Now, you can create `addition(value INTEGER)` function which adds `value` to `num` and returns `value` to the caller as shown below. *You must set `RETURNS <type>` clause or `OUT` or `INOUT` parameters which I explain later and `AS <delimiter>` clause and `BEGIN ... END;` statement to a PL/pgSQL function otherwise there is error and `RETURNS <type>` clause can have `VOID` type to return nothing and you can also use other delimiter ['][6] instead of [$$][6] to create the body of a PL/pgSQL function and you can use [SELECT ... INTO][7] statement to put a retrieved value into a variable and if a return value type doesn't match `RETURNS <type>` clause or `OUT` or `INOUT` parameters, there is error and lastly, you must set `plpgsql` to `LANGUAGE` to create a PL/pgSQL function:

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

Then, you can call `addition(3)` with [SELECT statement][8], then `5` is returned and `3` is added to `num` as shown below:

```sql
postgres=# SELECT addition(3);
 addition
----------
 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:

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

Or:

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

Then, you can call `addition(3)` with `SELECT` statement, then nothing is returned and `3` is added to `num` as shown below:

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

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

Be careful, using `VOID` type with `RETURN value;` in a PL/pgSQL function gets [the error][9] as shown below because their types `VOID` and `INTEGER` are not the same while a SQL function doesn't get error:

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

> ERROR: RETURN cannot have a parameter in function returning void 
> LINE 5: RETURN value; -- Here

And, not using `BEGIN ... END;` in a PL/pgSQL function gets the error as shown below while a SQL function doesn't get error:

```sql
CREATE FUNCTION addition(value INTEGER) RETURNS INTEGER AS $$
-- BEGIN -- Error
UPDATE test set num = num + value;
SELECT num INTO value FROM test;
RETURN value;
-- END; -- Error
$$ LANGUAGE plpgsql;
```

> ERROR: syntax error at or near "UPDATE" 
> LINE 2: UPDATE test set num = num + value;

And, using transaction with e.g. `BEGIN;`, `COMMIT;`, etc in a PL/pgSQL function gets the error as shown below:

```sql
CREATE FUNCTION addition(value INTEGER) RETURNS INTEGER AS $$
BEGIN
BEGIN;
UPDATE test set num = num + value;
SELECT num INTO value FROM test;
RETURN value;
COMMIT;
END;
$$ LANGUAGE plpgsql;
```

> ERROR: syntax error at or near ";" 
> LINE 3: BEGIN;

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

An `IN` parameter can get the value from the caller and actually, with and without `IN` is the same so `addition(IN value INTEGER)` and `addition(value INTEGER)` are the same: 

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

Then, you can call `addition(3)` with `SELECT` statement, then `5` is returned and `3` is added to `num` as shown below:

```sql
postgres=# SELECT addition(3);
 addition
----------
 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 and actually, 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 and if you set `RETURN` statement with an `OUT` parameter, you must set `RETURN;` to return nothing otherwise there is error:

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

Then, you can call `addition()` with `SELECT` statement, then `5` is returned and `3` is added to `num` as shown below:

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

But, calling `addition(3)` gets the error as shown below because an `OUT` parameter cannot get a value from the caller as I said above:

```sql
postgres=# SELECT addition(3);
ERROR: function addition(integer) does not exist
LINE 1: SELECT addition(3);
 ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
```

And, 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 and `RETURN` statement must be `RETURN;` to return nothing as shown below otherwise there is error:

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

So, if the types of an `OUT` parameter (`INTEGER`) and `RETURNS <type>` clause (`VOID`) are not the same, there is the error as shown below:

```sql
CREATE FUNCTION addition(OUT value INTEGER) RETURNS VOID AS $$
BEGIN -- ↑ Here -- ↑ Here
UPDATE test set num = num + 3;
SELECT num INTO value FROM test;
-- RETURN; -- Unset
END;
$$ LANGUAGE plpgsql;
```

> function result type must be integer because of OUT parameters

And, setting `VOID` type to an `OUT` parameter gets the error as shown below:

```sql
CREATE FUNCTION addition(OUT value VOID) AS $$
BEGIN -- ↑ Here
UPDATE test set num = num + 3;
SELECT num INTO value FROM test;
-- RETURN; -- Unset
END;
$$ LANGUAGE plpgsql;
```

> ERROR: PL/pgSQL functions cannot accept type void

And, setting `RETURN value;` instead of `RETURN;` with an `OUT` parameter gets the error as shown below:

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

> ERROR: RETURN cannot have a parameter in function with OUT parameters 
> LINE 5: RETURN value;

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

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

Then, you can call `addition(3)` with `SELECT` statement, then `5` is returned and `3` is added to `num` as shown below:

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


 [1]: https://www.postgresql.org/docs/current/tutorial-transactions.html
 [2]: https://www.postgresql.org/docs/current/plpgsql-control-structures.html
 [3]: https://www.postgresql.org/docs/current/plpgsql.html
 [4]: https://stackoverflow.com/questions/69519777/create-a-function-in-postgresql/77598433#77598433
 [5]: https://www.postgresql.org/docs/current/xfunc-sql.html
 [6]: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING
 [7]: https://www.postgresql.org/docs/current/sql-selectinto.html
 [8]: https://www.postgresql.org/docs/current/sql-select.html
 [9]: https://stackoverflow.com/questions/17698503/plpgsql-error-return-cannot-have-a-parameter-in-function-returning-void/77609986#77609986