TutorialDBA - Support | Training | Consultant

Both stored procedures and user-defined functions are created with CREATE FUNCTION statement in PostgreSQL. To return one or more result sets (cursors in terms of PostgreSQL), you have to use  refcursor  return type. Quick Example : -- Procedure that returns a single result set (cursor) CREATE OR REPLACE FUNCTION show_cities ( ) RETURNS refcursor AS $$ DECLARE ref refcursor; -- Declare a cursor variable BEGIN OPEN ref FOR SELECT city , state FROM cities; -- Open a cursor RETURN ref; -- Return the cursor to the caller END ; $$ LANGUAGE plpgsql; Overview : Return Multiple Result Sets Cursor Lifetime Until the  end  of transaction Auto-commit Must be  off Transaction must be active so the caller can see a result set Important Note : The cursor remains open until the end of transaction, an...

A stored procedure and user-defined function (UDF) is a set of  SQL  and procedural statements (declarations, assignments, loops, flow-of-control etc.) that stored on the database server and can be invoked using the  SQL  interface. Quick Example : -- Function increments the input value by 1 CREATE OR REPLACE FUNCTION increment ( i INT ) RETURNS INT AS $$ BEGIN RETURN i + 1 ; END ; $$ LANGUAGE plpgsql;   -- An example how to use the function (Returns: 11) SELECT increment ( 10 ) ; In PostgreSQL,  both  stored procedures and user-defined functions are created with CREATE FUNCTION statement. There are differences between  the notion  of stored procedures and functions in database systems: Stored Procedure Function Use in an expression Return a value Return values as OUT parameters Return a single result set  (as a table function) Return multiple result sets So in most cases, the ...