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, and since PostgreSQL works
Comments
Post a Comment