PostgreSQL Cursor PDF

PL/pgSQL Cursor  Cursor A PL/pgSQL cursor allows you to encapsulate a query and process each individual row at a time. Typically, you use cursors when you want to divide a large result set into parts and process each part individually. If you process it at once, you may have a memory over...

PL/pgSQL Cursor  Cursor A PL/pgSQL cursor allows you to encapsulate a query and process each individual row at a time. Typically, you use cursors when you want to divide a large result set into parts and process each part individually. If you process it at once, you may have a memory overflow error. On top of that, you can develop a function that returns a reference to a cursor. This is an effective way to return a large result set from a function. The caller of the function can process the result set based on the cursor reference. The following diagram illustrates how to use a cursor in PostgreSQL:  Declaring cursors To access to a cursor, you need to declare a cursor variable in the declaration section of a block. PostgreSQL provides you with a special type called REFCURSOR to declare a cursor variable. declare my_cursor refcursor; You can also declare a cursor that bounds to a query by using the following syntax: cursor_name [ [no] scroll ] cursor [( name datatype, name data type,...)] for query; The following example illustrates how to declare cursors: Declare unbounded cur_fil,s cursor; cursor cur_films cursor for Regular select * bounded from film; cursor cur_films2 cursor (year integer) for Parameteri select * zed from film bounded where release_year = year; cursor  Opening unbound cursors Cursors must be opened before they can be used to query rows. PostgreSQL provides the syntax for opening an unbound and bound cursor. Opening unbound cursors You open an unbound cursor using the following syntax: OPEN unbound_cursor_variable [ [ NO ] SCROLL ] FOR query; Code language: SQL (Structured Query Language) (sql) Because the unbound cursor variable is not bounded to any query when we declared it, we have to specify the query when we open it. See the following example: open my_cursor for select * from city where country = p_country; PostgreSQL allows you to open a cursor and bound it to a dynamic query. Here is the syntax: open unbound_cursor_variable[ [ no ] scroll ] for execute query_string [using expression [,... ] ]; In the following example, we build a dynamic query that sorts rows based on a sort_field parameter and open the cursor that executes the dynamic query. >> query := 'select * from city order by $1’; >> open cur_city for execute query using sort_field; Opening bound cursors Because a bound cursor already bounds to a query when we declared it, so when we open it, we just need to pass the arguments to the query if necessary. open cursor_variable[ (name:=value,name:=value,...)]; In the following example, we open bound cursors cur_films and cur_films2 that we declared above: >> open cur_films; >> open cur_films2(year:=2005); Using cursors After opening a cursor, we can manipulate it using FETCH, MOVE, UPDATE, or DELETE statement. Fetching the next row fetch [ direction { from | in } ] cursor_variable into target_variable; The FETCH statement gets the next row from the cursor and assigns it a target_variable, which could be a record, a row variable, or a comma-separated list of variables. If no more row found, the target_variable is set to NULL(s). By default, a cursor gets the next row if you don’t specify the direction explicitly. The following is the valid values for the direction of cursor: NEXT See the following examples of fetching cursors. LAST PRIOR >> fetch cur_films into row_film; FIRST Row_film is defined as a record. ABSOLUTE count >> fetch last from row_film into title, RELATIVE count release_year; FORWARD/ BACKWARD Note that FORWARD and BACKWARD directions are only for cursors declared with Moving the cursor move [ direction { from | in } ] cursor_variable; Code language: SQL (Structured Query Language) (sql) If you want to move the cursor only without retrieving any row, you use the MOVE statement. The direction accepts the same value as the FETCH statement. >> move cur_films2; >> move last from cur_films; >> move relative -1 from cur_films; >> move forward 3 from cur_films; Deleting or updating the row Once a cursor is positioned, we can delete or update row identifying by the cursor using DELETE WHERE CURRENT OF or UPDATE WHERE CURRENT OF statement as follows: update table_name set column = value,... where current of cursor_variable; delete from table_name where current of cursor_variable; Closing cursors To close an opening cursor, we use CLOSE statement as follows: close cursor_variable; The CLOSE statement releases resources or frees up cursor variable to allow it to be opened again using OPEN statement. PL/pgSQL cursors – putting it all together create or replace function get_film_titles(p_year integer) returns text as $$ declare titles text default ‘’; rec_film record; cur_films cursor(p_year integer) for select title, release_year from film where release_year = p_year; begin open cur_films(p_year); -- open the cursor loop fetch cur_films into rec_film; -- fetch row into the film exit when not found; -- exit when no more row to fetch -- build the output if rec_film.title like '%ful%' then titles := titles || ',' || rec_film.title || ':' || rec_film.release_year; end if; end loop; Call the function as below: -- close the cursor >> select get_film_titles(2006); close cur_films; Output: return titles; ,Grosse Wonderful:2006,Day Unfaithful:2006,Reap Unfaithful: end; $$ 2006,Unfaithful Kill:2006,Wonderful Drop:2006 language plpgsql;