A SQL procedure in PostgreSQL
Tags: postgres, sqlprocedure, sql, procedure
Published: 2023-12-25 04:40:00

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

### A [SQL procedure](https://www.postgresql.org/docs/current/sql-createprocedure.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-createprocedure.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.

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

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

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

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

- 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 [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/sql-createprocedure.html) 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/a-sql-function-in-postgresql-54oh) explains a SQL function.
*[My post](https://dev.to/hyperkai/a-plpgsql-function-in-postgresql-3j9f) explains a PL/pgSQL 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 `value` to the caller as shown below:

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

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

```sql
CREATE PROCEDURE my_proc(IN value INT, OUT result INT)
AS $$
UPDATE test SET num = num + value;
SELECT num FROM test;
$$
LANGUAGE SQL; -- Here
```

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

```sql
CREATE PROCEDURE my_proc(IN value INT, OUT result INT)
LANGUAGE SQL
AS $$
UPDATE test SET num = num + value RETURNING num;
-- SELECT num FROM test;          -- ↑ Here ↑
$$;
```

*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 can have `VOID` type in a SQL function while an OUT parameter cannot in a PL/pgSQL function.

- The default value of an `OUT` parameter is `NULL`.

- You must set `AS` clause with delimiter to a SQL 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 SQL procedure. *[My answer](https://stackoverflow.com/questions/12144284/what-are-used-for-in-pl-pgsql/77657886#77657886) explains it.

- Using `BEGIN ... END` statement in a SQL procedure gets error while a PL/pgSQL procedure doesn't get error:

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

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

Then, you can call `my_proc(3)` 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 `VOID` type to return nothing as shown below. *Type conversion is needed to `VOID` type and [my answer](https://stackoverflow.com/questions/13809547/convert-integer-to-string-in-postgresql/77723276#77723276) explains how to convert type:

```sql
CREATE PROCEDURE my_proc(IN value INT, OUT result VOID)
LANGUAGE SQL                                    -- ↑ Here
AS $$
UPDATE test SET num = num + value RETURNING 'world'::VOID;
-- SELECT num FROM test;                     -- ↑ Here ↑
$$;
```

Then, calling `my_proc(3, 'hello'::VOID)` returns nothing which is `VOID` type and `3` is added to `num` as shown below. *Type conversion is needed to `VOID` type and you can use [pg_typeof()](https://www.postgresql.org/docs/current/functions-info.html) to check the type `'Hello'::VOID` which is `VOID`:

```sql
postgres=# CALL my_proc(3, 'hello'::VOID);
 result
--------

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

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 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 PROCEDURE my_proc(INOUT value INT)
BEGIN ATOMIC
LANGUAGE SQL
  RETURN 2 + value;
END;
```

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 procedure:

```sql
CREATE PROCEDURE my_proc(INOUT value INT)
-- LANGUAGE SQL
RETURN 2 + value;
```

Then, calling `my_proc(3)` adds returns `5` as shown below:

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

And, you can use `BEGIN ATOMIC ... END` clause 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 PROCEDURE my_proc(INOUT value INT)
LANGUAGE SQL
BEGIN ATOMIC
  UPDATE test SET num = num + value;
  SELECT num FROM test;
END;
```

Or:

```sql
CREATE PROCEDURE my_proc(INOUT value INT)
LANGUAGE SQL
BEGIN ATOMIC                        -- ↓ Here
  UPDATE test SET num = num + value RETURNING num;
END;
```

Or:

```sql
CREATE PROCEDURE my_proc(INOUT value INT)
LANGUAGE SQL
BEGIN ATOMIC
  UPDATE test SET num = num + value;
  RETURN (SELECT num FROM test);
END;
```

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

```sql
CREATE PROCEDURE my_proc(INOUT value INT)
-- LANGUAGE SQL
BEGIN ATOMIC
  UPDATE test SET num = num + value;
  SELECT num 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)
```