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();
