Creating Procedures

A procedure is a program that does some processing but doesn’t return a result. They are called using CALL command. A procedure is always defined in SQL standards.

CREATE PROCEDURE creates a new procedure. CREATE OR REPLACE PROCEDURE creates new or overwrites an existing procedure. The name of the new procedure should not match with the name of any existing function or procedure with the same input argument types in the same schema. However, functions and procedures of different argument types can share a name (this is called overloading).

The full syntax is here.

Creating a simple procedure – Multiply two integers using a SQL function

# CREATE OR REPLACE PROCEDURE add_city(city_id integer, city_name text, city_pop integer)
    LANGUAGE SQL
    AS $$
    INSERT INTO city_high VALUES(city_id, city_name, city_pop);
    $$;

# CALL add_City(107, 'Lko', 2100);

# SELECT * from CITY;

# \df

# \df add_city

# \x on

# \df+ add_city

appdb=> CREATE OR REPLACE PROCEDURE add_city(city_id integer, city_name text, city_pop integer)
appdb->     LANGUAGE SQL
appdb->     AS $$
appdb$>     INSERT INTO city_high VALUES(city_id, city_name, city_pop);
appdb$>     $$;
CREATE PROCEDURE
appdb=> CALL add_City(107, 'Lko', 2100);
CALL
appdb=> SELECT * from CITY;
 city_id | name  |   pop
---------+-------+---------
     101 | Blore |     100
     104 | Mum   | 1000002
     102 | Mum   | 1000001
     106 | Del   |   11000
     107 | Lko   |    2100
(5 rows)

appdb=> \df
                                             List of functions
 Schema |   Name   |      Result data type      |                Argument data types                | Type
--------+----------+----------------------------+---------------------------------------------------+------
 public | add_city |                            | city_id integer, city_name text, city_pop integer | proc
 public | dup      | record                     | integer, OUT f1 integer, OUT f2 text              | func
 public | dup2     | TABLE(f1 integer, f2 text) | integer                                           | func
 public | maths    | TABLE(f1 text, f2 integer) | integer, integer                                  | func
 public | multi    | integer                    | integer, integer                                  | func
 public | multipg  | integer                    | a integer, b integer                              | func
(6 rows)

appdb=> \df add_city
                                        List of functions
 Schema |   Name   | Result data type |                Argument data types                | Type
--------+----------+------------------+---------------------------------------------------+------
 public | add_city |                  | city_id integer, city_name text, city_pop integer | proc
(1 row)

appdb=> \x on
Expanded display is on.
appdb=> \df+ add_city
List of functions
-[ RECORD 1 ]-------+----------------------------------------------------------------
Schema              | public
Name                | add_city
Result data type    |
Argument data types | city_id integer, city_name text, city_pop integer
Type                | proc
Volatility          | volatile
Parallel            | unsafe
Owner               | manoj
Security            | invoker
Access privileges   |
Language            | sql
Source code         |                                                                +
                    |     INSERT INTO city_high VALUES(city_id, city_name, city_pop);+
                    |
Description         |

appdb=>