A SQL function in PostgreSQL
Tags: postgres, sqlfunction, sql, function
Published: 2023-12-29 14:16:54

### A [SQL function](https://www.postgresql.org/docs/current/xfunc-sql.html):

- can have zero or more queries with or without `AS` clause or [BEGIN ATOMIC ... END clause](https://www.postgresql.org/about/featurematrix/detail/393/) getting zero or more values with zero or more parameters from the caller. *`BEGIN ATOMIC ... END` clause works only for `LANGUAGE SQL` according to [the doc](https://www.postgresql.org/docs/current/sql-createfunction.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.

- cannot have [DECLARE clause](https://www.postgresql.org/docs/current/plpgsql-declarations.html) otherwise there is error.

- cannot 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).

- 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 return a value with the last statement to the caller with [SELECT statement](https://www.postgresql.org/docs/current/sql-select.html) 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) using `AS` clause or `BEGIN ATOMIC ... END` clause.

- can return a value with the last statement to the caller with [RETURN statement](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-RETURN) not using `AS` clause or using `BEGIN ATOMIC ... END` clause.

- can return a value with the last statement to the caller with [VALUES statement](https://www.postgresql.org/docs/current/queries-values.html) using `AS` clause or `BEGIN ATOMIC ... END` clause. *[My answer](https://stackoverflow.com/questions/40030693/how-to-create-a-function-that-returns-a-setof-type-in-postgresql#77977350) explains it.

- cannot have `BEGIN ... END` clause which can have multiple queries otherwise there is error while a PL/pgSQL function can have it. *Be careful, `BEGIN ... END` clause is not [transaction](https://www.postgresql.org/docs/current/tutorial-transactions.html).

- cannot have [SELECT INTO statement](https://www.postgresql.org/docs/current/sql-selectinto.html) otherwise there is error.

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

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

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

- cannot control transaction with `START TRANSACTION`, `BEGIN`, `ROLLBACK`, `COMMIT` etc otherwise there is [the error](https://stackoverflow.com/questions/27671140/error-return-type-mismatch-in-function-declared-to-return#77623207).

*[The doc](https://www.postgresql.org/docs/current/xfunc-sql.html) explains a SOL function.
*[My post](https://dev.to/hyperkai/a-plpgsql-function-in-postgresql-3j9f) explains a PL/pgSQL function.
*[My post](https://dev.to/hyperkai/a-sql-procedure-in-postgresql-41e0) explains a SOL procedure.
*[My post](https://dev.to/hyperkai/a-plpgsql-procedure-31nd) explains a PL/pgSQL 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 $$
UPDATE test SET num = num + value;
SELECT num FROM test;
$$ LANGUAGE SQL;
```

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

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

Or, you can use `UPDATE` statement with `RETURNING num` instead of `SELECT num FROM test;` as shown below:

```sql
CREATE FUNCTION my_func(value INT) RETURNS INT
AS $$
UPDATE test SET num = num + value RETURNING num;
$$ LANGUAGE SQL;                  -- ↑ Here ↑
```

*Memos:

- You must set `RETURNS <type>` clause or `OUT` or `INOUT` parameters which I explain later to a SQL 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 SQL function. *[My answer](https://stackoverflow.com/questions/12144284/what-are-used-for-in-pl-pgsql/77657886#77657886) explains it.

- In a SQL function, the result of the last statement is the return value whose type must match `RETURNS <type>` clause or `OUT` or `INOUT` parameters except `VOID` type so in the example above, the result of `SELECT num FROM test;` is the return value whose type actually matches `RETURNS INT` because `num` value is also `INT` so if the type doesn't match `RETURNS <type>` clause, there is error.

- You must set `LANGUAGE SQL` to create a SQL 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 SQL` in 2 positions as shown above.

Then, you can call `my_func(3)` with `SELECT` statement, 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 to return nothing as shown below:

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

Then, calling `my_func(3)` returns nothing  then `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 return a value with `RETURN` statement not using `AS` clause as shown below. *Using `RETURN` statement with `AS` clause gets [the error](https://stackoverflow.com/questions/33438511/error-syntax-error-at-or-near-return-in-postgresql/77630318#77630318):

```sql
CREATE FUNCTION my_func(value INT) RETURNS INT
LANGUAGE SQL
RETURN 2 + value;
```

Or, you can unset `LANGUAGE SQL` when returning a value with `RETURN` statement not using `AS` clause as shown below but it is still a SQL function:

```sql
CREATE FUNCTION my_func(value INT) RETURNS INT
-- LANGUAGE SQL
RETURN 2 + value;
```

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

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

And, you can use `BEGIN ATOMIC ... END` clause in a SQL function as shown below. *In `BEGIN ATOMIC ... END` clause, you can return a value with `SELECT` statement, `RETURNING` clause or `RETURN` statement and you can use both `BEGIN ATOMIC` and `BEGIN ATOMIC;`:

```sql
CREATE FUNCTION my_func(value INT) RETURNS INT
LANGUAGE SQL
BEGIN ATOMIC
  UPDATE test SET num = num + value;
  SELECT num FROM test; -- Here
END;
```

Or:

```sql
CREATE FUNCTION my_func(value INT) RETURNS INT
LANGUAGE SQL
BEGIN ATOMIC                   -- ↓ ↓ Here ↓ ↓
  UPDATE test SET num = num + value RETURNING num;
END;
```

Or:

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

Or, you can unset `LANGUAGE SQL` when using `BEGIN ATOMIC ... END` clause as shown below but it is still a SQL function:

```sql
CREATE FUNCTION my_func(value INT) RETURNS INT
-- LANGUAGE SQL
BEGIN ATOMIC;
  UPDATE test SET num = num + value;
  SELECT num FROM test;
END;
```

Then, calling `my_func(3)` returns nothing, then `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)
```

In addition, you can use `IN`, `OUT` and `INOUT` parameters in a SQL 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
UPDATE test SET num = num + value;
SELECT num FROM test;
$$ LANGUAGE SQL;
```

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
UPDATE test SET num = num + 3;
SELECT num FROM test;
$$ LANGUAGE SQL;
```

Or:

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

*Memo:

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

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

- 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).

- 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).

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          -- ↑ Unset ↑
UPDATE test SET num = num + value;
SELECT num FROM test;
$$ LANGUAGE SQL;
```

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)
```


  [1]: https://www.postgresql.org/docs/current/xfunc-sql.html
  [2]: https://www.postgresql.org/about/featurematrix/detail/393/
  [3]: https://www.postgresql.org/docs/current/sql-createfunction.html
  [4]: https://www.postgresql.org/docs/current/sql-select.html
  [5]: https://www.postgresql.org/docs/current/dml-returning.html
  [6]: https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-RETURN
  [7]: https://www.postgresql.org/docs/current/tutorial-transactions.html
  [8]: https://www.postgresql.org/docs/current/plpgsql-declarations.html
  [9]: https://stackoverflow.com/questions/66132466/are-plpgsql-functions-atomic
  [10]: https://stackoverflow.com/questions/69519777/create-a-function-in-postgresql/77608956#77608956
  [11]: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING
  [12]: https://stackoverflow.com/questions/12144284/what-are-used-for-in-pl-pgsql/77657886#77657886
  [13]: https://stackoverflow.com/questions/33438511/error-syntax-error-at-or-near-return-in-postgresql/77630318#77630318
  [14]: https://stackoverflow.com/questions/3157753/declare-local-variables-in-postgresql/77633234#77633234