From 347dd6a1cf4ea78e37982ade15496b454c54cf4c Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 6 Apr 2008 23:43:29 +0000 Subject: Make plpgsql support FOR over a query specified by a cursor declaration, for improved compatibility with Oracle. Pavel Stehule, with some fixes by me. --- doc/src/sgml/plpgsql.sgml | 273 +++++++++++++++++++++++++++------------------- 1 file changed, 163 insertions(+), 110 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 512cb7657ce..8eac671fabf 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,6 +1,6 @@ - + - + <application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language @@ -195,7 +195,7 @@ END label ; A label is only needed if you want to - identify the block for use + identify the block for use in an EXIT statement, or to qualify the names of the variables declared in the block. If a label is given after END, it must match the label at the block's beginning. @@ -285,10 +285,12 @@ $$ LANGUAGE plpgsql; All variables used in a block must be declared in the - declarations section of the block. - (The only exception is that the loop variable of a FOR loop + declarations section of the block. + (The only exceptions are that the loop variable of a FOR loop iterating over a range of integer values is automatically declared as an - integer variable.) + integer variable, and likewise the loop variable of a FOR loop + iterating over a cursor's result is automatically declared as a + record variable.) @@ -317,7 +319,7 @@ arow RECORD; The DEFAULT clause, if given, specifies the initial value assigned to the variable when the block is entered. If the DEFAULT clause is not given then the variable is initialized to the - SQL null value. + SQL null value. The CONSTANT option prevents the variable from being assigned to, so that its value remains constant for the duration of the block. If NOT NULL @@ -750,7 +752,7 @@ PREPARE statement_name(integer, integer) AS SELECT $1 < $2; data type, or the variable has a specific size/precision (like char(20)), the result value will be implicitly converted by the PL/pgSQL interpreter using - the result type's output-function and + the result type's output-function and the variable type's input-function. Note that this could potentially result in run-time errors generated by the input function, if the string form of the result value is not acceptable to the input function. @@ -1049,7 +1051,7 @@ EXECUTE command-string INT If the STRICT option is given, an error is reported unless the query produces exactly one row. - + The command string can use parameter values, which are referenced in the command as $1, $2, etc. @@ -1082,8 +1084,8 @@ EXECUTE 'SELECT count(*) FROM ' An EXECUTE with a simple constant command string and some USING parameters, as in the first example above, is - functionally equivalent to just writing the command directly in - PL/pgSQL and allowing replacement of + functionally equivalent to just writing the command directly in + PL/pgSQL and allowing replacement of PL/pgSQL variables to happen automatically. The important difference is that EXECUTE will re-plan the command on each execution, generating a plan that is specific @@ -1198,7 +1200,7 @@ EXECUTE 'UPDATE tbl SET ' not deliver unintended results. For example the WHERE clause 'WHERE key = ' || quote_nullable(keyvalue) - + will never succeed if keyvalue is null, because the result of using the equality operator = with a null operand is always null. If you wish null to work like an ordinary key value, @@ -1281,50 +1283,51 @@ GET DIAGNOSTICS integer_var = ROW_COUNT; - A SELECT INTO statement sets - FOUND true if a row is assigned, false if no - row is returned. + A SELECT INTO statement sets + FOUND true if a row is assigned, false if no + row is returned. - A PERFORM statement sets FOUND - true if it produces (and discards) one or more rows, false if - no row is produced. + A PERFORM statement sets FOUND + true if it produces (and discards) one or more rows, false if + no row is produced. - UPDATE, INSERT, and DELETE - statements set FOUND true if at least one - row is affected, false if no row is affected. + UPDATE, INSERT, and DELETE + statements set FOUND true if at least one + row is affected, false if no row is affected. - A FETCH statement sets FOUND - true if it returns a row, false if no row is returned. + A FETCH statement sets FOUND + true if it returns a row, false if no row is returned. - A MOVE statement sets FOUND - true if it successfully repositions the cursor, false otherwise. + A MOVE statement sets FOUND + true if it successfully repositions the cursor, false otherwise. - A FOR statement sets FOUND true - if it iterates one or more times, else false. This applies to - all three variants of the FOR statement (integer - FOR loops, record-set FOR loops, and - dynamic record-set FOR - loops). FOUND is set this way when the - FOR loop exits; inside the execution of the loop, - FOUND is not modified by the - FOR statement, although it might be changed by the - execution of other statements within the loop body. + A FOR statement sets FOUND true + if it iterates one or more times, else false. This applies to + all four variants of the FOR statement (integer + FOR loops, record-set FOR loops, + dynamic record-set FOR loops, and cursor + FOR loops). + FOUND is set this way when the + FOR loop exits; inside the execution of the loop, + FOUND is not modified by the + FOR statement, although it might be changed by the + execution of other statements within the loop body. @@ -1391,7 +1394,7 @@ NULL; important) part of PL/pgSQL. With PL/pgSQL's control structures, you can manipulate PostgreSQL data in a very - flexible and powerful way. + flexible and powerful way. @@ -1649,7 +1652,7 @@ END IF; -IF v_count > 0 THEN +IF v_count > 0 THEN INSERT INTO users_count (count) VALUES (v_count); RETURN 't'; ELSE @@ -1721,7 +1724,7 @@ END IF; IF number = 0 THEN result := 'zero'; -ELSIF number > 0 THEN +ELSIF number > 0 THEN result := 'positive'; ELSIF number < 0 THEN result := 'negative'; @@ -1879,7 +1882,7 @@ LOOP -- some computations EXIT WHEN count > 100; CONTINUE WHEN count < 50; - -- some computations for count IN [50 .. 100] + -- some computations for count IN [50 .. 100] END LOOP; @@ -1935,16 +1938,16 @@ END LOOP label ; This form of FOR creates a loop that iterates over a range - of integer values. The variable + of integer values. The variable name is automatically defined as type integer and exists only inside the loop (any existing definition of the variable name is ignored within the loop). The two expressions giving the lower and upper bound of the range are evaluated once when entering - the loop. If the BY clause isn't specified the iteration - step is 1, otherwise it's the value specified in the BY + the loop. If the BY clause isn't specified the iteration + step is 1, otherwise it's the value specified in the BY clause, which again is evaluated once on loop entry. - If REVERSE is specified then the step value is + If REVERSE is specified then the step value is subtracted, rather than added, after each iteration. @@ -2045,7 +2048,7 @@ $$ LANGUAGE plpgsql; rows: <<label>> -FOR target IN EXECUTE text_expression USING expression , ... LOOP +FOR target IN EXECUTE text_expression USING expression , ... LOOP statements END LOOP label ; @@ -2057,6 +2060,12 @@ END LOOP label ; As with EXECUTE, parameter values can be inserted into the dynamic command via USING. + + + Another way to specify the query whose results should be iterated + through is to declare it as a cursor. This is described in + . + @@ -2293,6 +2302,14 @@ DECLARE cursor variables while the third uses a bound cursor variable. + + + Bound cursors can also be used without explicitly opening them, + via the FOR statement described in + . + + + <command>OPEN FOR</command> <replaceable>query</replaceable> @@ -2640,7 +2657,7 @@ END; BEGIN; SELECT reffunc2(); - reffunc2 + reffunc2 -------------------- <unnamed cursor 1> (1 row) @@ -2676,6 +2693,36 @@ COMMIT; + + + Looping Through a Cursor's Result + + + There is a variant of the FOR statement that allows + iterating through the rows returned by a cursor. The syntax is: + + + <<label>> +FOR recordvar IN bound_cursor ( argument_values ) LOOP + statements +END LOOP label ; + + + The cursor variable must have been bound to some query when it was + declared, and it cannot be open already. The + FOR statement automatically opens the cursor, and it closes + the cursor again when the loop exits. 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 in the query, in just + the same way as during an OPEN. + The variable recordvar is automatically + defined as type record and exists only inside the loop (any + existing definition of the variable name is ignored within the loop). + Each row returned by the cursor is successively assigned to this + record variable and the loop body is executed. + + + @@ -2716,8 +2763,8 @@ RAISE level 'PL/pgSQL interpreter parses the function's source text and produces an internal binary instruction tree the first time the function is called (within each session). The instruction tree - fully translates the + fully translates the PL/pgSQL statement structure, but individual SQL expressions and SQL commands used in the function are not translated immediately. @@ -3410,7 +3457,7 @@ $$ LANGUAGE plpgsql; start a new database session so that populate() will be compiled afresh, before it will work again. You can avoid this problem by using CREATE OR REPLACE FUNCTION - when updating the definition of + when updating the definition of my_function, since when a function is replaced, its OID is not changed. @@ -3527,10 +3574,10 @@ $$ LANGUAGE plpgsql; - In the case of logfunc2, the + In the case of logfunc2, the PostgreSQL main parser does not know - what type 'now' should become and therefore - it returns a data value of type text containing the string + what type 'now' should become and therefore + it returns a data value of type text containing the string now. During the ensuing assignment to the local variable curtime, the PL/pgSQL interpreter casts this @@ -3710,11 +3757,11 @@ a_output := a_output || $$ AND name LIKE 'foobar'$$ . For example: -a_output := a_output || '' if v_'' || - referrer_keys.kind || '' like '''''''''' - || referrer_keys.key_string || '''''''''' - then return '''''' || referrer_keys.referrer_type - || ''''''; end if;''; +a_output := a_output || '' if v_'' || + referrer_keys.kind || '' like '''''''''' + || referrer_keys.key_string || '''''''''' + then return '''''' || referrer_keys.referrer_type + || ''''''; end if;''; The value of a_output would then be: @@ -3726,8 +3773,8 @@ if v_... like ''...'' then return ''...''; end if; a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$ || referrer_keys.key_string || $$' - then return '$$ || referrer_keys.referrer_type - || $$'; end if;$$; + then return '$$ || referrer_keys.referrer_type + || $$'; end if;$$; where we assume we only need to put single quote marks into a_output, because it will be re-quoted before use. @@ -3796,14 +3843,6 @@ a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$ - - - No need for cursors in PL/pgSQL, just put the - query in the FOR statement. (See .) - - - In PostgreSQL the function body must be written as @@ -3823,7 +3862,7 @@ a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$ Since there are no packages, there are no package-level variables - either. This is somewhat annoying. You can keep per-session state + either. This is somewhat annoying. You can keep per-session state in temporary tables instead. @@ -3840,6 +3879,23 @@ a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$ + + + FOR loops over queries (other than cursors) also work + differently: the target variable(s) must have been declared, + whereas PL/SQL always declares them implicitly. + An advantage of this is that the variable values are still accessible + after the loop exits. + + + + + + There are various notational differences for the use of cursor + variables. + + + @@ -3939,36 +3995,34 @@ $$ LANGUAGE plpgsql; The following procedure grabs rows from a SELECT statement and builds a large function with the results in IF statements, for the - sake of efficiency. Notice particularly the differences in the - cursor and the FOR loop. + sake of efficiency. This is the Oracle version: CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS - CURSOR referrer_keys IS - SELECT * FROM cs_referrer_keys + CURSOR referrer_keys IS + SELECT * FROM cs_referrer_keys ORDER BY try_order; - - func_cmd VARCHAR(4000); -BEGIN + func_cmd VARCHAR(4000); +BEGIN func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR, - v_domain IN VARCHAR, v_url IN VARCHAR) RETURN VARCHAR IS BEGIN'; + v_domain IN VARCHAR, v_url IN VARCHAR) RETURN VARCHAR IS BEGIN'; - FOR referrer_key IN referrer_keys LOOP + FOR referrer_key IN referrer_keys LOOP func_cmd := func_cmd || ' IF v_' || referrer_key.kind || ' LIKE ''' || referrer_key.key_string || ''' THEN RETURN ''' || referrer_key.referrer_type - || '''; END IF;'; - END LOOP; + || '''; END IF;'; + END LOOP; - func_cmd := func_cmd || ' RETURN NULL; END;'; + func_cmd := func_cmd || ' RETURN NULL; END;'; - EXECUTE IMMEDIATE func_cmd; -END; -/ + EXECUTE IMMEDIATE func_cmd; +END; +/ show errors; @@ -3978,29 +4032,28 @@ show errors; CREATE OR REPLACE FUNCTION cs_update_referrer_type_proc() RETURNS void AS $func$ DECLARE - referrer_key RECORD; -- declare a generic record to be used in a FOR + CURSOR referrer_keys IS + SELECT * FROM cs_referrer_keys + ORDER BY try_order; func_body text; func_cmd text; -BEGIN +BEGIN func_body := 'BEGIN'; - -- Notice how we scan through the results of a query in a FOR loop - -- using the FOR <record> construct. - - FOR referrer_key IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP + FOR referrer_key IN referrer_keys LOOP func_body := func_body || ' IF v_' || referrer_key.kind || ' LIKE ' || quote_literal(referrer_key.key_string) || ' THEN RETURN ' || quote_literal(referrer_key.referrer_type) || '; END IF;' ; - END LOOP; + END LOOP; func_body := func_body || ' RETURN NULL; END;'; func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar, v_domain varchar, - v_url varchar) + v_url varchar) RETURNS varchar AS ' || quote_literal(func_body) || ' LANGUAGE plpgsql;' ; @@ -4364,7 +4417,7 @@ $$ LANGUAGE plpgsql STRICT IMMUTABLE; -- -- instr functions that mimic Oracle's counterpart -- Syntax: instr(string1, string2, [n], [m]) where [] denotes optional parameters. --- +-- -- Searches string1 beginning at the nth character for the mth occurrence -- of string2. If n is negative, search backwards. If m is not passed, -- assume 1 (search starts at first character). -- cgit v1.2.3