PL/pgSQL CURSOR support
От | Jan Wieck |
---|---|
Тема | PL/pgSQL CURSOR support |
Дата | |
Msg-id | 200105211506.LAA13866@jupiter.jw.home обсуждение исходный текст |
Список | pgsql-hackers |
Hi folks, I just committed changes to the SPI manager and PL/pgSQL, providing full CURSOR support. A detailed description is attached as a Postscript file. Brief description follows. Enhancement of SPI: There are a couple of new functions and internal changes to the SPI memory management. SPI now creates separate memory contexts for prepared and saved plans and tuple result sets. The contexts are children of where the allocations used to happen, so it's fully upgrade compatible. New functions SPI_freeplan(plan) and SPI_freetuptable(tuptab) allow to simply destroy the contexts when no longer needed. The other new functions deal with portals: Portal SPI_cursor_find(char *name); Get an existing portal by name Portal SPI_cursor_open(char *name, void *plan, Datum *Values, char *Nulls); Use a prepared or saved SPI plan to create a new portal. if <name> is NULL, the function willmake up a unique name inside the backend. A portal created by this can be accessed by the main applicationas well if SPI_cursor_open() was called inside of an explicit transaction block. void SPI_cursor_fetch(Portal portal, bool forward, int count); Fetch at max <count> tuples from <portal> into the well known SPI_tuptable and set SPI_processed. <portal> could be any existing portal, even one created by the main application using DECLARE ... CURSOR. void SPI_cursor_move(Portal portal, bool forward, int count); Same as fetch but suppress tuples. void SPI_cursor_close(Portal portal); Close the given portal. Doesn't matter who created it (SPI or main application). New datatype "refcursor" A new datatype "refcursor" is created as a basetype, which is equivalent to "text". This is required below. Enhancement of PL/pgSQL Explicit cursor can be declared as: DECLARE ... curname CURSOR [(argname type [, ...])] IS <select_stmt>; ... The <select_stmt> can use any so far declared variable or positional function arguments (possibly aliased). These will be evaluated at OPEN time. Explicit cursor can be opened with: BEGIN ... OPEN curname [(expr [, ...])]; ... The expression list is required if and only if the explicit cursor declaration contains an argument list. The created portal will be named 'curname' and is accessible globally. Reference cursor can be declared as: DECLARE ... varname REFCURSOR; ... and opened with BEGIN ... OPEN varname FOR <select_stmt>; -- or OPEN varname FOREXECUTE <string expression>; ... The type "refcursor" is a datatype like text, and the variables "value" controls the "name" argument to SPI_cursor_open(). Defaulting to NULL, the resulting portal will get a generic, unique name and thevariable will be set to that name at OPEN. If the function assigns a value before OPEN, that'll be used as theportal name. Cursors (of both types) are used with: BEGIN ... FETCH cursorvar INTO {record | row | var [, ...]}; ... CLOSE cursorvar; FETCH sets the global variable FOUND to flag if another row is available. A typical loop thus looks like this: BEGIN OPEN myrefcur FOR SELECT * FROM mytab; LOOP FETCH myrefcur INTO myrow; EXIT WHEN NOT FOUND; -- Process one row END LOOP; CLOSE myrefcur; The "refcursor" type can be used for function arguments or return values as well. So one function can call anotherto open a cursor, assigning it's return value to a "refcursor", pass that down to other functionsand - you get the idea. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
В списке pgsql-hackers по дате отправления:
Предыдущее
От: "Richard Huxton"Дата:
Сообщение: Re: [GENERAL] Queries across multiple databases (was: SELECT from a table in another database).