Index: doc/src/sgml/plpgsql.sgml =================================================================== RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/plpgsql.sgml,v retrieving revision 1.6 diff -c -r1.6 plpgsql.sgml *** doc/src/sgml/plpgsql.sgml 1 Sep 2002 16:28:05 -0000 1.6 --- doc/src/sgml/plpgsql.sgml 13 Sep 2002 19:12:35 -0000 *************** *** 70,87 **** As each expression and SQL query is first used ! in the function, the PL/pgSQL interpreter creates a ! prepared execution plan (using the SPI manager's ! SPI_prepare and ! SPI_saveplan functions). Subsequent visits ! to that expression or query re-use the prepared plan. Thus, a function ! with conditional code that contains many statements for which execution ! plans might be required, will only prepare and save those plans ! that are really used during the lifetime of the database ! connection. This can provide a considerable savings of parsing ! activity. A disadvantage is that errors in a specific expression ! or query may not be detected until that part of the function is ! reached in execution. Once PL/pgSQL has made a query plan for a particular --- 70,88 ---- As each expression and SQL query is first used ! in the function, the PL/pgSQL interpreter creates ! a prepared execution plan (using the SPI ! manager's SPI_prepare and ! SPI_saveplan functions). Subsequent visits ! to that expression or query re-use the prepared plan. Thus, a ! function with conditional code that contains many statements for ! which execution plans might be required will only prepare and save ! those plans that are really used during the lifetime of the ! database connection. This can substantially reduce the total ! amount of time required to parse, and generate query plans for the ! statements in a procedural language function. A disadvantage is ! that errors in a specific expression or query may not be detected ! until that part of the function is reached in execution. Once PL/pgSQL has made a query plan for a particular *************** *** 110,123 **** ! Because PL/pgSQL saves execution plans in this way, queries that appear ! directly in a PL/pgSQL function must refer to the 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. Except for input/output conversion and calculation functions for user defined types, anything that can be defined in C language --- 111,136 ---- ! Because PL/pgSQL saves execution plans ! in this way, queries that appear directly in a ! PL/pgSQL function must refer to the ! 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 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). + + + Except for input/output conversion and calculation functions for user defined types, anything that can be defined in C language *************** *** 152,162 **** Better Performance ! SQL is the language PostgreSQL (and ! most other Relational Databases) use as query ! language. It's portable and easy to learn. But every ! SQL statement must be executed ! individually by the database server. --- 165,175 ---- Better Performance ! SQL is the language ! PostgreSQL (and most other relational databases) ! use as query language. It's portable and easy to learn. But ! every SQL statement must be executed ! individually by the database server. *************** *** 195,203 **** Portability ! Because PL/pgSQL functions run inside PostgreSQL, these ! functions will run on any platform where PostgreSQL ! runs. Thus you can reuse code and have less development costs. --- 208,217 ---- Portability ! Because PL/pgSQL functions run inside ! PostgreSQL, these functions will run on any ! platform where PostgreSQL runs. Thus you can ! reuse code and have reduce development costs. *************** *** 227,242 **** ! One good way to develop in PL/pgSQL is to simply use the text ! editor of your choice to create your functions, and in another ! console, use psql (PostgreSQL's interactive monitor) to load ! those functions. If you are doing it this way, it is a good ! idea to write the function using CREATE OR REPLACE ! FUNCTION. That way you can reload the file to update ! the function definition. For example: CREATE OR REPLACE FUNCTION testfunc(INTEGER) RETURNS INTEGER AS ' ! .... end; ' LANGUAGE 'plpgsql'; --- 241,257 ---- ! One good way to develop in PL/pgSQL is to simply ! use the text editor of your choice to create your functions, and ! in another console, use psql ! (PostgreSQL's interactive monitor) to load those ! functions. If you are doing it this way, it is a good idea to ! write the function using CREATE OR REPLACE ! FUNCTION. That way you can reload the file to update the ! function definition. For example: CREATE OR REPLACE FUNCTION testfunc(INTEGER) RETURNS INTEGER AS ' ! .... end; ' LANGUAGE 'plpgsql'; *************** *** 645,653 **** ! RENAME appears to be broken as of PostgreSQL 7.2. Fixing this is ! of low priority, since ALIAS covers most of the practical uses of ! RENAME. --- 660,668 ---- ! RENAME appears to be broken as of PostgreSQL ! 7.2. Fixing this is of low priority, since ALIAS covers most of ! the practical uses of RENAME. *************** *** 898,904 **** PERFORM query; ! This executes a SELECT query and discards the result. PL/pgSQL variables are substituted in the query as usual. Also, the special variable --- 913,919 ---- PERFORM query; ! This executes a SELECT query and discards the result. PL/pgSQL variables are substituted in the query as usual. Also, the special variable *************** *** 1044,1049 **** --- 1059,1068 ---- Obtaining result status + There are several ways to determine the effect of a command. The + first method is to use the GET DIAGNOSTICS, + which has the form: + GET DIAGNOSTICS variable = item , ... ; *************** *** 1166,1175 **** When a PL/pgSQL function is declared to return SETOF sometype, the procedure ! to follow is slightly different. The individual items to be returned ! are specified in RETURN NEXT commands, and then a final RETURN with ! no argument is given to indicate that the function is done generating ! items. RETURN NEXT expression; --- 1185,1204 ---- 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 program 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(); ! ! ! (Namely, a column list such as * must be ! specified). RETURN NEXT expression; *************** *** 1184,1189 **** --- 1213,1236 ---- RETURN, which need have no argument, causes control to exit the function. + + + + 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, performance may be poor: data + will be buffered to disk to avoid memory exhaustion, but the + function itself will not return any results until the entire + result set has been generated. A future version of PL/PgSQL may + allow users to allow users to define set-returning functions + that do not have this limitation. Currently, the point at which + data is buffered to disk is controlled by the + + *************** *** 1904,1916 **** Trigger Procedures ! PL/pgSQL can be used to define trigger ! procedures. A trigger procedure is created with the CREATE ! FUNCTION command as a function with no arguments and a return ! type of TRIGGER. Note that the function must be declared ! with no arguments even if it expects to receive arguments specified ! in CREATE TRIGGER --- trigger arguments are passed via ! TG_ARGV, as described below. --- 1951,1964 ---- Trigger Procedures ! PL/pgSQL can be used to define trigger ! procedures. A trigger procedure is created with the ! CREATE FUNCTION command as a function with no ! arguments and a return type of TRIGGER. Note that ! the function must be declared with no arguments even if it expects ! to receive arguments specified in CREATE TRIGGER --- ! trigger arguments are passed via TG_ARGV, as described ! below. *************** *** 2106,2119 **** ! One painful detail in writing functions in PL/pgSQL is the handling ! of single quotes. The function's source text in CREATE FUNCTION must ! be a literal string. Single quotes inside of literal strings must be ! either doubled or quoted with a backslash. We are still looking for ! an elegant alternative. In the meantime, doubling the single quotes ! as in the examples below should be used. Any solution for this ! in future versions of PostgreSQL will be ! forward compatible. --- 2154,2168 ---- ! One painful detail in writing functions in ! PL/pgSQL is the handling of single ! quotes. The function's source text in CREATE FUNCTION ! must be a literal string. Single quotes inside of literal strings ! must be either doubled or quoted with a backslash. We are still ! looking for an elegant alternative. In the meantime, doubling the ! single quotes as in the examples below should be used. Any ! solution for this in future versions of ! PostgreSQL will be forward compatible. *************** *** 2504,2510 **** 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 cursors, FOR loops, and the need to escape --- 2553,2559 ---- 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 cursors, FOR loops, and the need to escape *************** *** 2735,2741 **** ! If you do a LOCK TABLE in PL/pgSQL, the lock will not be released until the calling transaction is finished. --- 2784,2790 ---- ! If you do a LOCK TABLE in PL/pgSQL, the lock will not be released until the calling transaction is finished. *************** *** 2746,2752 **** entire function (and other functions called from therein) is executed in a transaction and PostgreSQL rolls back the results if something goes wrong. Therefore only one ! BEGIN statement is allowed. --- 2795,2801 ---- entire function (and other functions called from therein) is executed in a transaction and PostgreSQL rolls back the results if something goes wrong. Therefore only one ! BEGIN statement is allowed. *************** *** 2895,2901 **** EXECUTE ! The PostgreSQL version of EXECUTE works nicely, but you have to remember to use quote_literal(TEXT) and quote_string(TEXT) as described in EXECUTE ! The PostgreSQL version of EXECUTE works nicely, but you have to remember to use quote_literal(TEXT) and quote_string(TEXT) as described in