Introduction to Postgres Stored Procedure

Postgres does not directly seem to support stored procedures. However, it supports it through functions and cursors. Here is an example how to write a stored procedures in PostgreSQL:

1. Create a function that returns a cursor.

CREATE OR REPLACE FUNCTION
    get_employees(emp_cursor refcursor, dept_id int)
RETURNS refcursor AS 
$BODY$
    DECLARE
        sql_statement character varying;
    BEGIN

        sql_statement = 'SELECT id, name FROM emp WHERE dept_id = ' || CAST(dept_id AS character varying);
        
        OPEN emp_cursor FOR EXECUTE sql_statement;
        RETURN emp_cursor;
        
    END
$BODY$
LANGUAGE 'plpgsql' ;

That's all. Your stored procedure is ready to be accessed from your application code.

2. To call the sp from your code, you need to run two sql statements consecutively within a single transaction. Given below is sample JAVA code (A similar approach/workaround should be available in other languages too):

/*
Create your connection object here.
*/

connection.setAutoCommit(false);

/*
The above is required since every statement will be committed automatically by default. If your language does not support the above methodology, try database level transaction with BEGIN END statements.
*/

Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT get_employees('emp_cursor', 1);");
resultSet = statement.executeQuery("FETCH ALL IN emp_cursor;");

/*
Do whatever stuff you want to do with the result set.
*/

connection.commit(); //Commit the transaction
connection.close();