Postgresql Stored Procedure PL/pgSQL

install the language plpgsql in the database before you start this tutorial

This is the solution for ERROR: language "plpgsql" does not exist

postgres@ubuntu:~$ createlang plpgsql test2;

to check whether the plpgsql is installed for your database

postgres@ubuntu:~$ createlang -l test2
Procedural Languages
  Name   | Trusted? 
---------+----------
 plpgsql | yes

Create Table

test2=# create table profile(
test2(# user_id SERIAL PRIMARY KEY,
test2(# user_firstname TEXT,
test2(# user_lastname TEXT,
test2(# user_age INT
test2(# );
NOTICE:  CREATE TABLE will create implicit sequence "profile_user_id_seq" for serial column "profile.user_id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "profile_pkey" for table "profile"
CREATE TABLE
test2=# 

Insert Record

test2=# INSERT INTO profile VALUES(1,'Bob','Mark',30),(2,'Jim','Jkim',32)
test2-# ,(3,'king','queen',29);
INSERT 0 3
test2=# SELECT * FROM profile;
 user_id | user_firstname | user_lastname | user_age 
---------+----------------+---------------+----------
       1 | Bob            | Mark          |       30
       2 | Jim            | Jkim          |       32
       3 | king           | queen         |       29
(3 rows

create basic stored procedure

postgres@ubuntu:~$ psql test2
psql (8.4.10)
Type "help" for help.

test2=# CREATE OR REPLACE FUNCTION hi() RETURNS text AS $hi$
test2$# DECLARE 
test2$#       hi text;
test2$# BEGIN
test2$#      hi := 'Welcome to the new world';
test2$#     RETURN hi;
test2$# END;
test2$# $hi$ LANGUAGE plpgsql;
CREATE FUNCTION

test the stored procedure

test2=# SELECT hi();
            hi            
--------------------------
 Welcome to the new world
(1 row)

how to insert record using stored procedure

test2=# CREATE OR REPLACE FUNCTION insert_record_person(INT, TEXT,TEXT,INT)
test2-# RETURNS void AS
test2-# $delimiter$
test2$#        INSERT INTO profile(user_id,user_firstname,user_lastname,user_age)
test2$#       VALUES($1,$2,$3,$4);
test2$# $delimiter$
test2-# LANGUAGE SQL;
CREATE FUNCTION

how to list all stored procedures in postgresql

test2=# \df
                                    List of functions
 Schema |         Name         | Result data type |     Argument data types      |  Type  
--------+----------------------+------------------+------------------------------+--------
 public | insert_person        | void             | integer, text, text, integer | normal
 public | insert_person        | void             | text, text, integer          | normal
 public | insert_record_person | void             | integer, text, text, integer | normal
(3 rows)

how to call a stored procedure

test2=# SELECT insert_record_person(4,'Jammi','weeks',23);
 insert_record_person 
----------------------
 
(1 row)

delete stored procedure in postgresql

This is going to delete the first stored procedures. insert_person(integer,text,text,integer)

                                   
test2=# DROP FUNCTION insert_person(integer,text,text,integer);
DROP FUNCTION

Using refcursor and for a cursor name return to the caller

test2=# CREATE FUNCTION user_age_id(refcursor) RETURNS refcursor AS '
test2'# BEGIN
test2'#    OPEN $1 FOR SELECT user_age, user_id FROM profile;
test2'#    RETURN $1;
test2'# END;
test2'# ' LANGUAGE plpgsql;
CREATE FUNCTION
test2=# BEGIN;
BEGIN
test2=# SELECT user_age_id('test_all_profile');
   user_age_id    
------------------
 test_all_profile
(1 row)

test2=# FETCH ALL IN test_all_profile;
 user_age | user_id 
----------+---------
       32 |       2
       29 |       3
       23 |       4
       45 |       1
       45 |      13
(5 rows)

test2=# COMMIT;
COMMIT