From fa09b6d7b2db9e494c763efc6306f0b5f537d41b Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 15 Nov 2001 23:32:39 +0000 Subject: [PATCH] Add documentation for new plpgsql cursor operations. Also, another round of editorial effort. --- doc/src/sgml/plsql.sgml | 495 ++++++++++++++++++++++++++++++++++-------------- 1 file changed, 355 insertions(+), 140 deletions(-) diff --git a/doc/src/sgml/plsql.sgml b/doc/src/sgml/plsql.sgml index b60b652e82..c32cc4158d 100644 --- a/doc/src/sgml/plsql.sgml +++ b/doc/src/sgml/plsql.sgml @@ -1,5 +1,5 @@ @@ -326,8 +326,14 @@ END; Lexical Details - All keywords and identifiers can be used in mixed upper and - lower-case. + Each statement and declaration within a block is terminated + by a semicolon. + + + + All keywords and identifiers can be written in mixed upper- and + lower-case. Identifiers are implicitly converted to lower-case + unless double-quoted. @@ -391,7 +397,7 @@ url VARCHAR; - The default value is evaluated every time the function is called. So, + The default value is evaluated every time the block is entered. So, for example, assigning 'now' to a variable of type timestamp causes the variable to have the time of the current function call, not when the function was @@ -408,13 +414,21 @@ user_id CONSTANT INTEGER := 10; - Aliases for Parameters Passed to Functions + Aliases for Function Parameters + + + +name ALIAS FOR $n; + + Parameters passed to functions are named with the identifiers $1, $2, - etc. Optionally, aliases can be declared for the $n - parameter names for increased readability. Some examples: + etc. Optionally, aliases can be declared for $n + parameter names for increased readability. Either the alias or the + numeric identifier can then be used to refer to the parameter value. + Some examples: CREATE FUNCTION sales_tax(REAL) RETURNS REAL AS ' DECLARE @@ -490,6 +504,12 @@ END; to, it has no substructure, and any attempt to access a field in it will draw a runtime error. + + + Note that RECORD is not a true datatype, only a placeholder. + Thus, for example, one cannot declare a function returning + RECORD. + @@ -555,7 +575,7 @@ BEGIN user_id := users_rec.user_id; ... -CREATE FUNCTION cs_refresh_one_mv(INTEGER) RETURNS INTEGER AS ' +CREATE FUNCTION does_view_exist(INTEGER) RETURNS bool AS ' DECLARE key ALIAS FOR $1; table_data cs_materialized_views%ROWTYPE; @@ -564,18 +584,10 @@ CREATE FUNCTION cs_refresh_one_mv(INTEGER) RETURNS INTEGER AS ' WHERE sort_key=key; IF NOT FOUND THEN - RAISE EXCEPTION ''View % not found'', key; - RETURN 0; + RETURN false; END IF; - - -- The mv_name column of cs_materialized_views stores view - -- names. - - EXECUTE ''TRUNCATE TABLE '' || quote_ident(table_data.mv_name); - EXECUTE ''INSERT INTO '' || quote_ident(table_data.mv_name) || '' '' || table_data.mv_query; - - return 1; -end; + RETURN true; + END; ' LANGUAGE 'plpgsql'; @@ -584,21 +596,21 @@ end; - - RENAME - + RENAME + +RENAME oldname TO newname; + + Using the RENAME declaration you can change the name of a variable, - record or row. This is useful if NEW or OLD should be referenced - by another name inside a trigger procedure. See also ALIAS. + record or row. This is primarily useful if NEW or OLD should be + referenced by another name inside a trigger procedure. See also ALIAS. - Syntax and examples: + Examples: -RENAME oldname TO newname; - RENAME id TO user_id; RENAME this_var TO that_var; @@ -702,18 +714,20 @@ CREATE FUNCTION logfunc2 (TEXT) RETURNS TIMESTAMP AS ' - Statements + Basic Statements - This section describes all the statement types that are explicitly - understood by PL/pgSQL. Anything not - recognized as one of these statement types is presumed to be an SQL - query, and is sent to the main database engine to execute (after - substitution for any PL/pgSQL variables + In this section and the following ones, we describe all the statement + types that are explicitly understood by + PL/pgSQL. + Anything not recognized as one of these statement types is presumed + to be an SQL query, and is sent to the main database engine to execute + (after substitution for any PL/pgSQL variables used in the statement). Thus, for example, SQL INSERT, UPDATE, and DELETE commands may be considered to be statements of - PL/pgSQL. + PL/pgSQL. But they are not specifically + listed here. @@ -750,8 +764,8 @@ tax := subtotal * 0.06; - - Query Assignments + + SELECT INTO The result of a SELECT command yielding multiple columns (but @@ -768,7 +782,7 @@ SELECT INTO target expressionsPL/pgSQL function, use the - equivalent syntax CREATE TABLE AS SELECT.) + syntax CREATE TABLE ... AS SELECT.) @@ -779,7 +793,7 @@ SELECT INTO target expressions - Except for the INTO clause, the SELECT command is the same as a normal + Except for the INTO clause, the SELECT statement is the same as a normal SQL SELECT query and can use the full power of SELECT. @@ -845,7 +859,7 @@ END; PL/pgSQL, use the PERFORM statement: -PERFORM query +PERFORM query; This executes a SELECT @@ -886,7 +900,7 @@ PERFORM create_mv(''cs_session_page_requests_mv'','' is provided: -EXECUTE query-string +EXECUTE query-string; where query-string is an expression @@ -995,7 +1009,7 @@ END; -GET DIAGNOSTICS variable = item , ... +GET DIAGNOSTICS variable = item , ... ; This command allows retrieval of system status indicators. Each @@ -1010,12 +1024,11 @@ GET DIAGNOSTICS variable = item + - - - - + Control Structures + Control structures are probably the most useful (and important) part of PL/pgSQL. With @@ -1024,13 +1037,14 @@ GET DIAGNOSTICS variable = item - + Returning from a function -RETURN expression +RETURN expression; + The function terminates and the value of expression will be returned to the upper executor. @@ -1044,26 +1058,28 @@ RETURN expression the function without hitting a RETURN statement, a runtime error will occur. - + - - Conditional Control: IF statements + + Conditionals - IF statements let you execute commands based on + IF statements let you execute commands based on certain conditions. PL/pgSQL has four forms of IF: IF-THEN, IF-THEN-ELSE, - IF-THEN-ELSE IF, IF-THEN-ELSIF-THEN-ELSE. + IF-THEN-ELSE IF, and IF-THEN-ELSIF-THEN-ELSE. - - - - IF-THEN - + + IF-THEN - + +IF boolean-expression THEN + statements +END IF; + + IF-THEN statements are the simplest form of IF. The statements between THEN and END IF will be executed if the condition is true. Otherwise, they are skipped. @@ -1074,19 +1090,23 @@ IF v_user_id <> 0 THEN END IF; - - + - - - IF-THEN-ELSE - + + IF-THEN-ELSE - + +IF boolean-expression THEN + statements +ELSE + statements +END IF; + + IF-THEN-ELSE statements add to IF-THEN by letting you - specify a group of statements that should be executed if the - condition evaluates to FALSE. + specify an alternative set of statements that should be executed if + the condition evaluates to FALSE. IF parentid IS NULL or parentid = '''' @@ -1105,15 +1125,11 @@ ELSE END IF; - - + - - - IF-THEN-ELSE IF - + + IF-THEN-ELSE IF - IF statements can be nested, as in the following example: @@ -1135,16 +1151,27 @@ END IF; This is workable but grows tedious when there are many alternatives to be checked. - - + - - - IF-THEN-ELSIF-ELSE - + + IF-THEN-ELSIF-ELSE - + +IF boolean-expression THEN + statements + ELSIF boolean-expression THEN + statements + ELSIF boolean-expression THEN + statements + ... + + + ELSE + statements +END IF; + + IF-THEN-ELSIF-ELSE provides a more convenient method of checking many alternatives in one statement. Formally it is equivalent to nested IF-THEN-ELSE-IF-THEN commands, but only one END IF @@ -1158,11 +1185,11 @@ END IF; IF number = 0 THEN result := ''zero''; ELSIF number < 0 THEN - result := ''negative''; + result := ''positive''; ELSIF number > 0 THEN result := ''negative''; ELSE - -- now it seems to be NULL + -- hmm, the only other possibility is that number IS NULL result := ''NULL''; END IF; @@ -1171,29 +1198,22 @@ END IF; The final ELSE section is optional. - - - - - + + - - Iterative Control: LOOP, WHILE, FOR and EXIT + + Simple Loops - With the LOOP, WHILE, FOR and EXIT statements, you can arrange + With the LOOP, EXIT, WHILE and FOR statements, you can arrange for your PL/pgSQL function to repeat a series of commands. - - - - LOOP - + + LOOP - <<label>> @@ -1201,37 +1221,36 @@ LOOP statements END LOOP; - An unconditional loop that must be terminated explicitly - by an EXIT statement. The optional label can be used by - EXIT statements of nested loops to specify which level of + + LOOP defines an unconditional loop that is repeated indefinitely + until terminated by an EXIT or RETURN statement. + The optional label can be used by + EXIT statements in nested loops to specify which level of nesting should be terminated. - - + - - - EXIT - + + EXIT - EXIT label WHEN expression ; + If no label is given, the innermost loop is terminated and the statement following END LOOP is executed next. If label is given, it - must be the label of the current or an outer level of nested loop - blocks. Then the named loop or block is terminated and control + must be the label of the current or some outer level of nested loop + or block. Then the named loop or block is terminated and control continues with the statement after the loop's/block's corresponding END. If WHEN is present, loop exit occurs only if the specified condition - is true. + is true, otherwise control passes to the statement after EXIT. @@ -1257,26 +1276,26 @@ BEGIN END; - - + - - - WHILE - + + WHILE - - With the WHILE statement, you can repeat a - sequence of statements so long as the condition expression - evaluates to true. The condition is checked just before - each entry to the loop body. <<label>> WHILE expression LOOP statements END LOOP; + + The WHILE statement repeats a + sequence of statements so long as the condition expression + evaluates to true. The condition is checked just before + each entry to the loop body. + + + For example: WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP @@ -1288,25 +1307,22 @@ WHILE NOT boolean_expression LOOP END LOOP; - - + - - - FOR - + + FOR (integer for-loop) - - <<label>> FOR name IN REVERSE expression .. expression LOOP statements END LOOP; - A loop that iterates over a range of integer values. The variable - name is automatically created as type + + This form of FOR creates a loop that iterates over a range of integer + values. The variable + name is automatically defined as type integer and exists only inside the loop. The two expressions giving the lower and upper bound of the range are evaluated once when entering the loop. The iteration step is normally 1, but is -1 when REVERSE is @@ -1327,18 +1343,16 @@ FOR i IN REVERSE 10..1 LOOP END LOOP; - - - - + + - - Iterating Through Records + + Looping Through Query Results Using a different type of FOR loop, you can iterate through the results of a query and manipulate that data - accordingly. The syntax is as follows: + accordingly. The syntax is: <<label>> FOR record | row IN select_query LOOP @@ -1402,13 +1416,214 @@ END LOOP; declared as a record/row variable. If not, it's presumed to be an integer FOR loop. This can cause rather unintuitive error messages when the true problem is, say, that one has - misspelled the FOR variable. + misspelled the FOR variable name. - + + Cursors + + + Rather than executing a whole query at once, it is possible to + set up a cursor that encapsulates the query, and + then read the query result a few rows at a time. One reason + for doing this is to avoid memory overrun when the result contains + a large number of rows. (However, PL/pgSQL users + don't normally need to worry about that, since FOR loops automatically + use a cursor internally to avoid memory problems.) A more interesting + possibility is that a function can return a reference to a cursor + that it has set up, allowing the caller to read the rows. This + provides one way of returning a rowset from a function. + + + + Declaring Cursor Variables + + + All access to cursors in PL/pgSQL goes through + cursor variables, which are always of the special datatype + refcursor. One way to create a cursor variable + is just to declare it as a variable of type refcursor. + Another way is to use the cursor declaration syntax, + which in general is: + +name CURSOR ( arguments ) FOR select_query ; + + (FOR may be replaced by IS for Oracle + compatibility.) arguments, if any, + are a comma-separated list of name + datatype pairs that define names to + be replaced by parameter values in the given query. The actual + values to substitute for these names will be specified later, + when the cursor is opened. + + + Some examples: + +DECLARE + curs1 refcursor; + curs2 CURSOR FOR SELECT * from tenk1; + curs3 CURSOR (key int) IS SELECT * from tenk1 where unique1 = key; + + All three of these variables have the datatype refcursor, + but the first may be used with any query, while the second has + a fully specified query already bound to it, and the last + has a parameterized query bound to it. (key will be + replaced by an integer parameter value when the cursor is opened.) + The variable curs1 + is said to be unbound since it is not bound to + any particular query. + + + + + Opening Cursors + + + Before a cursor can be used to retrieve rows, it must be + opened. (This is the equivalent action to + the SQL command DECLARE CURSOR.) + PL/pgSQL has four forms of the OPEN statement, + two of which are for use with unbound cursor variables + and the other two for use with bound cursor variables. + + + + OPEN FOR SELECT + + + +OPEN unbound-cursor FOR SELECT ...; + + + The cursor variable is opened and given the specified query + to execute. The cursor cannot be open already, and it must + have been declared as an unbound cursor (that is, as a simple + refcursor variable). The SELECT query is treated + in the same way as other SELECTs in PL/pgSQL: + PL/pgSQL variable names are substituted for, + and the query plan is cached for possible re-use. + + +OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey; + + + + + + OPEN FOR EXECUTE + + + +OPEN unbound-cursor FOR EXECUTE query-string; + + + The cursor variable is opened and given the specified query + to execute. The cursor cannot be open already, and it must + have been declared as an unbound cursor (that is, as a simple + refcursor variable). The query is specified as a + string expression in the same way as for the EXECUTE command. + As usual, this gives flexibility for the query to vary + from one run to the next. + + +OPEN curs1 FOR EXECUTE ''SELECT * FROM '' || quote_ident($1); + + + + + + OPENing a bound cursor + + + +OPEN bound-cursor ( argument_values ) ; + + + This form of OPEN is used to open a cursor variable whose query + was bound to it when it was declared. + The cursor cannot be open already. A list of actual argument + value expressions must appear if and only if the cursor was + declared to take arguments. These values will be substituted + into the query. + The query plan for a bound cursor is always considered + cacheable --- there is no equivalent of EXECUTE in this case. + + +OPEN curs2; +OPEN curs3(42); + + + + + + + Using Cursors + + + Once a cursor has been opened, it can be manipulated with the + statements described here. + + + + These manipulations need not occur in the same function that + opened the cursor to begin with. You can return a refcursor + value out of a function and let the caller operate on the cursor. + (Internally, a refcursor value is simply the string name + of a Portal containing the active query for the cursor. This name + can be passed around, assigned to other refcursor variables, + and so on, without disturbing the Portal.) + + + + All Portals are implicitly closed at end of transaction. Therefore + a refcursor value is useful to reference an open cursor + only until the end of the transaction. + + + + FETCH + + + +FETCH cursor INTO target; + + + FETCH retrieves the next row from the cursor into a target, + which may be a row variable, a record variable, or a comma-separated + list of simple variables, just as for SELECT INTO. As with + SELECT INTO, the special variable FOUND may be checked to see + whether a row was obtained or not. + + +FETCH curs1 INTO rowvar; +FETCH curs2 INTO foo,bar,baz; + + + + + + CLOSE + + + +CLOSE cursor; + + + CLOSE closes the Portal underlying an open cursor. + This can be used to release resources earlier than end of + transaction, or to free up the cursor variable to be opened again. + + +CLOSE curs1; + + + + + + Errors and Messages -- 2.11.0