> This page location: PostgreSQL PL/pgSQL > CREATE PROCEDURE
> Full Neon documentation index: https://neon.com/docs/llms.txt

# PostgreSQL CREATE PROCEDURE

**Summary**: in this tutorial, you will learn how to use the PostgreSQL `CREATE PROCEDURE` statement to create new stored procedures.

## Introduction to PostgreSQL CREATE PROCEDURE statement

So far, you have learned how to [define user-defined functions](https://neon.com/postgresql/postgresql-plpgsql/postgresql-create-function) using the `create function` statement.

A drawback of user-defined functions is that they cannot execute [transactions](../postgresql-tutorial/postgresql-transaction). In other words, inside a user-defined function, you cannot [start a transaction](../postgresql-tutorial/postgresql-transaction), and commit or rollback it.

PostgreSQL 11 introduced stored procedures that support transactions.

To define a new stored procedure, you use the `create procedure` statement with the following syntax:

```plsql
create [or replace] procedure procedure_name(parameter_list)
language plpgsql
as $$
declare
-- variable declaration
begin
-- stored procedure body
end; $$
```

In this syntax:

- First, specify the name of the stored procedure after the `create procedure` keywords.
- Second, define parameters for the stored procedure. A stored procedure can accept zero or more parameters.
- Third, specify `plpgsql` as the procedural language for the stored procedure. Note that you can use other procedural languages for the stored procedure such as SQL, C, etc.
- Finally, use the dollar-quoted string constant syntax to define the body of the stored procedure.

Parameters in stored procedures can have the `in` and `inout` modes but cannot have the `out` mode.

A stored procedure does not return a value. You cannot use the `return` statement with a value inside a store procedure like this:

```plsql
return expression;
```

However, you can use the `return` statement without the `expression` to stop the stored procedure immediately:

```plsql
return;
```

If you want to return a value from a stored procedure, you can use parameters with the `inout` mode.

## PostgreSQL CREATE PROCEDURE statement examples

We will use the following `accounts` table for the demonstration:

```plsql
drop table if exists accounts;

create table accounts (
    id int generated by default as identity,
    name varchar(100) not null,
    balance dec(15,2) not null,
    primary key(id)
);

insert into accounts(name,balance)
values('Bob',10000);

insert into accounts(name,balance)
values('Alice',10000);
```

The following statement shows the data from the `accounts` table:

```plsql
select * from accounts;
```

Output:

```text
 id | name  | balance
----+-------+----------
  1 | Bob   | 10000.00
  2 | Alice | 10000.00
(2 rows)
```

The following example creates a stored procedure named `transfer` that transfers a specified amount of money from one account to another.

```plsql
create or replace procedure transfer(
   sender int,
   receiver int,
   amount dec
)
language plpgsql
as $$
begin
    -- subtracting the amount from the sender's account
    update accounts
    set balance = balance - amount
    where id = sender;

    -- adding the amount to the receiver's account
    update accounts
    set balance = balance + amount
    where id = receiver;

    commit;
end;$$;
```

## Calling a stored procedure

To call a stored procedure, you use the `CALL` statement as follows:

```plsql
call stored_procedure_name(argument_list);
```

For example, this statement invokes the `transfer` stored procedure to transfer `$1,000` from Bob's account to Alice's account.

```plsql
call transfer(1,2,1000);
```

The following statement verifies the data in the `accounts` table after the transfer:

```plsql
SELECT * FROM accounts;
```

Output:

```
 id | name  | balance
----+-------+----------
  1 | Bob   |  9000.00
  2 | Alice | 11000.00
(2 rows)
```

The output shows that the transfer has been successful.

## Summary

- Use `create procedure` statement to define a new stored procedure.
- Use the `call` statement to invoke a stored procedure.

---

## Related docs (PostgreSQL PL/pgSQL)

- [Introduction](https://neon.com/postgresql/postgresql-plpgsql/introduction-to-postgresql-stored-procedures)
- [Block Structure](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-block-structure)
- [Variables](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-variables)
- [Constants](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-constants)
- [IF-ELSE Statements](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-if-else-statements)
- [CASE Statement](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-case-statement)
- [Loop Statements](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-loop-statements)
- [FOR Loop](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-for-loop)
- [WHILE Loop](https://neon.com/postgresql/postgresql-plpgsql/pl-pgsql-while-loop)
- [EXIT Statement](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-exit)
- [CONTINUE Statement](https://neon.com/postgresql/postgresql-plpgsql/pl-pgsql-continue)
- [Cursor](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-cursor)
- [Function Parameters](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-function-parameters)
- [Function Overloading](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-function-overloading)
- [Function Returns Table](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-function-returns-a-table)
- [Returns SETOF](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-returns-setof)
- [Record Types](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-record-types)
- [Row Types](https://neon.com/postgresql/postgresql-plpgsql/pl-pgsql-row-types)
- [Error Messages](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-errors-messages)
- [Exception Handling](https://neon.com/postgresql/postgresql-plpgsql/postgresql-exception)
- [CREATE FUNCTION](https://neon.com/postgresql/postgresql-plpgsql/postgresql-create-function)
- [DROP FUNCTION](https://neon.com/postgresql/postgresql-plpgsql/postgresql-drop-function)
- [DROP PROCEDURE](https://neon.com/postgresql/postgresql-plpgsql/postgresql-drop-procedure)
- [Stored Procedure with INOUT Parameters](https://neon.com/postgresql/postgresql-plpgsql/postgresql-stored-procedure-with-inout-parameters)
- [Introduction to Stored Procedures](https://neon.com/postgresql/postgresql-plpgsql/introduction-to-postgresql-stored-procedures)
- [SELECT INTO](https://neon.com/postgresql/postgresql-plpgsql/pl-pgsql-select-into)
- [ASSERT Statement](https://neon.com/postgresql/postgresql-plpgsql/pl-pgsql-assert)
- [Dollar-Quoted String Constants](https://neon.com/postgresql/postgresql-plpgsql/dollar-quoted-string-constants)