From cfd2728100b1493cea54e2d0ae0f3d545c27805c Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Sun, 10 Nov 2002 00:35:58 +0000 Subject: This patch makes a minor cleanup to the implementation of PERFORM in PL/PgSQL. Previously, it had been bundled together with the assign statement implementation, for some reason that wasn't clear to me (they certainly don't share any code with one another). So I separated them and made PERFORM a statement like any other. No changes in functionality. Along the way, I added some regression tests for PERFORM, added a bunch more SGML tags to the PL/PgSQL docs, and removed an obsolete comment relating to the implementation of RETURN NEXT. Neil Conway --- doc/src/sgml/plpgsql.sgml | 210 ++++++++++++++++++++++++---------------------- 1 file changed, 109 insertions(+), 101 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 2e7f2a9e583..80764df092e 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,5 +1,5 @@ @@ -102,7 +102,7 @@ END; If you execute the above function, it will reference the OID for my_function() in the query plan produced for - the PERFORM statement. Later, if you + the PERFORM statement. Later, if you drop and re-create my_function(), then populate() will not be able to find my_function() anymore. You would then have to @@ -117,17 +117,19 @@ END; same tables and fields on every execution; that is, you cannot use a parameter as the name of a table or field in a query. To get around this restriction, you can construct dynamic queries using - the PL/pgSQL EXECUTE statement --- at - the price of constructing a new query plan on every execution. + the PL/pgSQL EXECUTE + statement --- at the price of constructing a new query plan on + every execution. - The PL/pgSQL EXECUTE statement is not - related to the EXECUTE statement supported by the + The PL/pgSQL + EXECUTE statement is not related to the + EXECUTE statement supported by the PostgreSQL backend. The backend - EXECUTE statement cannot be used within PL/pgSQL functions (and - is not needed). + EXECUTE statement cannot be used within + PL/pgSQL functions (and is not needed). @@ -173,13 +175,12 @@ END; - That means that your client application must send each - query to the database server, wait for it to process it, - receive the results, do some computation, then send - other queries to the server. All this incurs inter-process communication - and may also incur network - overhead if your client is on a different machine than - the database server. + That means that your client application must send each query to + the database server, wait for it to process it, receive the + results, do some computation, then send other queries to the + server. All this incurs inter-process communication and may also + incur network overhead if your client is on a different machine + than the database server. @@ -753,14 +754,14 @@ CREATE FUNCTION logfunc2 (TEXT) RETURNS TIMESTAMP AS ' The mutable nature of record variables presents a problem in this - connection. When fields of a record variable are used in expressions or - statements, the data types of the - fields must not change between calls of one and the same expression, - since the expression will be planned using the data type that is present - when the expression is first reached. - Keep this in mind when writing trigger procedures that handle events - for more than one table. (EXECUTE can be used to get around this - problem when necessary.) + connection. When fields of a record variable are used in + expressions or statements, the data types of the fields must not + change between calls of one and the same expression, since the + expression will be planned using the data type that is present + when the expression is first reached. Keep this in mind when + writing trigger procedures that handle events for more than one + table. (EXECUTE can be used to get around + this problem when necessary.) @@ -904,10 +905,11 @@ END; Executing an expression or query with no result - Sometimes one wishes to evaluate an expression or query but discard - the result (typically because one is calling a function that has - useful side-effects but no useful result value). To do this in - PL/pgSQL, use the PERFORM statement: + Sometimes one wishes to evaluate an expression or query but + discard the result (typically because one is calling a function + that has useful side-effects but no useful result value). To do + this in PL/pgSQL, use the + PERFORM statement: PERFORM query; @@ -922,11 +924,12 @@ PERFORM query; - - One might expect that SELECT with no INTO clause would accomplish - this result, but at present the only accepted way to do it is PERFORM. - - + + One might expect that SELECT with no INTO + clause would accomplish this result, but at present the only + accepted way to do it is PERFORM. + + An example: @@ -940,13 +943,13 @@ PERFORM create_mv(''cs_session_page_requests_mv'', my_query); Executing dynamic queries - Oftentimes you will want to generate dynamic queries inside - your PL/pgSQL functions, that is, - queries that will involve different tables or different data types - each time they are executed. PL/pgSQL's + Oftentimes you will want to generate dynamic queries inside your + PL/pgSQL functions, that is, queries + that will involve different tables or different data types each + time they are executed. PL/pgSQL's normal attempts to cache plans for queries will not work in such - scenarios. To handle this sort of problem, the EXECUTE statement - is provided: + scenarios. To handle this sort of problem, the + EXECUTE statement is provided: EXECUTE query-string; @@ -973,20 +976,22 @@ EXECUTE query-string; Unlike all other queries in PL/pgSQL, a - query run by an EXECUTE statement is - not prepared and saved just once during the life of the server. - Instead, the query is prepared each - time the statement is run. The - query-string can be dynamically - created within the procedure to perform actions on variable - tables and fields. + query run by an + EXECUTE statement is not prepared and saved + just once during the life of the server. Instead, the + query is prepared each time the + statement is run. The query-string can + be dynamically created within the procedure to perform actions on + variable tables and fields. - The results from SELECT queries are discarded by EXECUTE, and - SELECT INTO is not currently supported within EXECUTE. So, the - only way to extract a result from a dynamically-created SELECT is - to use the FOR-IN-EXECUTE form described later. + The results from SELECT queries are discarded + by EXECUTE, and SELECT INTO + is not currently supported within EXECUTE. + So, the only way to extract a result from a dynamically-created + SELECT is to use the FOR-IN-EXECUTE form + described later. @@ -1017,7 +1022,8 @@ EXECUTE ''UPDATE tbl SET '' - Here is a much larger example of a dynamic query and EXECUTE: + Here is a much larger example of a dynamic query and + EXECUTE: CREATE FUNCTION cs_update_referrer_type_proc() RETURNS INTEGER AS ' DECLARE @@ -1159,9 +1165,9 @@ GET DIAGNOSTICS variable = itemexpression; - RETURN with an expression is used to return from a - PL/pgSQL function that does not return a set. - The function terminates and the value of + RETURN with an expression is used to return + from a PL/pgSQL function that does not return a + set. The function terminates and the value of expression is returned to the caller. @@ -1176,22 +1182,24 @@ RETURN expression; - The return value of a function cannot be left undefined. If control - reaches the end of the top-level block of - the function without hitting a RETURN statement, a run-time error - will occur. + The return value of a function cannot be left undefined. If + control reaches the end of the top-level block of the function + without hitting a RETURN statement, a run-time + error will occur. When a PL/pgSQL function is declared to return SETOF sometype, the procedure to follow is slightly different. In that case, the individual - items to return are specified in RETURN NEXT commands, and then a - final RETURN command with no arguments is used to indicate that - the function has finished executing. RETURN NEXT can be used with - both scalar and composite data types; in the later case, an - entire "table" of results will be returned. Functions that use - RETURN NEXT should be called in the following fashion: + items to return are specified in RETURN NEXT + commands, and then a final RETURN command with + no arguments is used to indicate that the function has finished + executing. RETURN NEXT can be used with both + scalar and composite data types; in the later case, an entire + "table" of results will be returned. Functions that use + RETURN NEXT should be called in the following + fashion: SELECT * FROM some_func(); @@ -1203,19 +1211,19 @@ SELECT * FROM some_func(); RETURN NEXT expression; - RETURN NEXT does not actually return from the function; it simply - saves away the value of the expression (or record or row variable, - as appropriate for the data type being returned). - Execution then continues with the next statement in the - PL/pgSQL function. As successive RETURN NEXT - commands are executed, the result set is built up. A final - RETURN, which need have no argument, causes control to exit - the function. + RETURN NEXT does not actually return from the + function; it simply saves away the value of the expression (or + record or row variable, as appropriate for the data type being + returned). Execution then continues with the next statement in + the PL/pgSQL function. As successive + RETURN NEXT commands are executed, the result + set is built up. A final RETURN, which need + have no argument, causes control to exit the function. - The current implementation of RETURN NEXT for + The current implementation of RETURN NEXT for PL/pgSQL stores the entire result set before returning from the function, as discussed above. That means that if a PL/pgSQL function produces a very large result set, @@ -1586,12 +1594,12 @@ FOR record | row IN EXECUTE text_express statements END LOOP; - This is like the previous form, except that the source SELECT - statement is specified as a string expression, which is evaluated - and re-planned on each entry to the FOR loop. This allows the - programmer to choose the speed of a pre-planned query or the - flexibility of a dynamic query, just as with a plain EXECUTE - statement. + This is like the previous form, except that the source + SELECT statement is specified as a string + expression, which is evaluated and re-planned on each entry to + the FOR loop. This allows the programmer to choose the speed of + a pre-planned query or the flexibility of a dynamic query, just + as with a plain EXECUTE statement. @@ -1700,18 +1708,18 @@ 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 in the EXECUTE command. - As usual, this gives flexibility so the query can vary - from one run to the next. + 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 in the EXECUTE + command. As usual, this gives flexibility so the query can vary + from one run to the next. OPEN curs1 FOR EXECUTE ''SELECT * FROM '' || quote_ident($1); @@ -1722,19 +1730,18 @@ 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 - in the query. - The query plan for a bound cursor is always considered - cacheable --- there is no equivalent of EXECUTE in this case. + 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 in the query. + The query plan for a bound cursor is always considered cacheable + --- there is no equivalent of EXECUTE in this case. OPEN curs2; @@ -1771,16 +1778,17 @@ OPEN curs3(42); 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 like SELECT INTO. As with - SELECT INTO, the special variable FOUND may be - checked to see whether a row was obtained or not. + 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 like + 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; -- cgit v1.2.3