A PL/pgSQL procedure in PostgreSQL
Tags: postgres, plpgsqlprocedure, plpgsql, procedure
Published: 2023-12-22 21:06:49

[Buy Me a Coffee](ko-fi.com/superkai)☕

### A [PL/pgSQL procedure](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-PROCEDURE):

- can have zero or more queries with `BEGIN ... END` clause 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).

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

- can have [RETURN statement](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-RETURN) without a value which is `RETURN;`.

- cannot have `RETURN` statement with a value e.g. `RETURN 2;` otherwise there is error.

- 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) as a non-substatement otherwise there is [the error](https://stackoverflow.com/questions/23946735/postgresql-query-has-no-destination-for-result-data#77642993).

- cannot have `RETURNS <type>` clause otherwise there is error.

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

- can control transaction with `ROLLBACK` and `COMMIT`.

- cannot control transaction with `START TRANSACTION` and `BEGIN` otherwise there is error.

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

*[The doc](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-PROCEDURE) explains a PL/pgSQL procedure.
*[My post](https://dev.to/hyperkai/a-sql-procedure-in-postgresql-41e0) explains a SQL procedure.
*[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-function-in-postgresql-54oh) explains a SQL function.
*[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_proc()` procedure which adds `value` to `num` and returns `result` to the caller as shown below:

```sql
CREATE PROCEDURE my_proc(IN value INT, OUT result INT)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE test SET num = num + value;
  SELECT num INTO result FROM test;
END;
$$;
```

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

```sql
CREATE PROCEDURE my_proc(IN value INT, OUT result INT)
AS $$
BEGIN
  UPDATE test SET num = num + value;
  SELECT num INTO result FROM test;
END;
$$
LANGUAGE plpgsql;
```

*Memos:

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

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

- An `OUT` parameter cannot have `VOID` type in a SQL function otherwise there is error while an OUT parameter cannot have `VOID` type in a PL/pgSQL function:

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

- 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 procedure. *[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 `OUT` or `INOUT` parameters, there is error.

- You must set `LANGUAGE plpgsql` to create a PL/pgSQL procedure 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_proc(3, 10)` with [CALL statement](https://www.postgresql.org/docs/current/sql-call.html), then `5` is returned and `3` is added to `num` as shown below. *`my_proc()` without the 2nd argument or the 2nd argument whose type is not `INT` get [the error](https://stackoverflow.com/questions/74207350/error-calling-procedure-in-posgres-no-procedure-matches-the-given-name-and-argu/77723048#77723048):

```sql
postgres=# CALL my_proc(3, 10);
 result
--------
      5
(1 row)

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

And, you can use the `INOUT` parameter which is the combination of `IN` and `OUT` parameters to get a value from the caller and to return a value to the caller as shown below:

```sql
CREATE PROCEDURE my_proc(INOUT value INT)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE test SET num = num + value;
  SELECT num INTO value FROM test;
END;
$$;
```

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

```sql
postgres=# CALL my_proc(3);
 value
-------
     5
(1 row)

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

And, you can use `RETURN` statement without a value which is `RETURN;` to exit `my_proc()` as shown below:

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

Then, calling `my_proc(3)` returns `3` and nothing is added to `num` as shown below. *The `INOUT` parameter `value` is not changed from `3` to `5`:

```sql
postgres=# CALL my_proc(3);
 value
-------
     3
(1 row)

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

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

```sql
CREATE PROCEDURE my_proc(INOUT value INT)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE test SET num = num + value;
  PERFORM num FROM test; -- Here
END;
$$;
```

Then, calling `my_proc(3)` returns `3` and `2` is added to `num` as shown below. *The `INOUT` parameter `value` is not changed from `3` to `5`:

```sql
postgres=# CALL my_proc(3);
 value
-------
     3
(1 row)

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

And, you can control transaction with `ROLLBACK` and `COMMIT` as shown below. *Using `START TRANSACTION` and `BEGIN` gets error:

```sql
CREATE PROCEDURE my_proc(INOUT value INT)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE test SET num = num + value;
  ROLLBACK;
  SELECT num INTO value FROM test;
  COMMIT;
END;
$$;
```

Then, calling `my_proc(3)` returns `2` and `3` is not added to `num` as shown below:

```sql
postgres=# CALL my_proc(3);
 value
-------
     2
(1 row)

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