From 496373e2e4dc37a3789fa56d615b6665aa376c5b Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Wed, 21 Feb 2001 17:50:38 +0000 Subject: [PATCH] Fix markup. (A isn't what it is in SQL.) Add jungle of more markup. ;-) --- doc/src/sgml/plsql.sgml | 293 ++++++++++++++++++++-------------------- 1 file changed, 147 insertions(+), 146 deletions(-) diff --git a/doc/src/sgml/plsql.sgml b/doc/src/sgml/plsql.sgml index c7c7ce04f7..fdccda3af7 100644 --- a/doc/src/sgml/plsql.sgml +++ b/doc/src/sgml/plsql.sgml @@ -1,9 +1,9 @@ - PL/pgSQL - SQL Procedural Language + PL/pgSQL - <acronym>SQL</acronym> Procedural Language PL/pgSQL is a loadable procedural language for the @@ -64,8 +64,8 @@ $Header: /cvsroot/pgsql/doc/src/sgml/Attic/plsql.sgml,v 2.20 2001/02/19 19:49:52 For all expressions and SQL statements used in the function, the PL/pgSQL bytecode interpreter creates a - prepared execution plan using the SPI manager's SPI_prepare() and - SPI_saveplan() functions. This is done the first time the individual + prepared execution plan using the SPI manager's SPI_prepare() and + SPI_saveplan() functions. This is done the first time the individual statement is processed in the PL/pgSQL function. Thus, a function with conditional code that contains many statements for which execution plans would be required, will only prepare and save those plans @@ -107,14 +107,14 @@ $Header: /cvsroot/pgsql/doc/src/sgml/Attic/plsql.sgml,v 2.20 2001/02/19 19:49:52 PL/pgSQL is a block oriented language. A block is defined as - -[<<label>>] -[DECLARE - declarations] + +<<label>> +DECLARE + declarations BEGIN statements END; - + @@ -141,12 +141,12 @@ END; Comments - There are two types of comments in PL/pgSQL. A double dash '--' - starts a comment that extends to the end of the line. A '/*' - starts a block comment that extends to the next occurrence of '*/'. + There are two types of comments in PL/pgSQL. A double dash -- + starts a comment that extends to the end of the line. A /* + starts a block comment that extends to the next occurrence of */. Block comments cannot be nested, but double dash comments can be enclosed into a block comment and a double dash can hide - the block comment delimiters '/*' and '*/'. + the block comment delimiters /* and */. @@ -160,16 +160,16 @@ END; sub-blocks must be declared in the declarations section of a block, except for the loop variable of a FOR-loop iterating over a range of integer values. Parameters given to a PL/pgSQL function are - automatically declared with the usual identifiers $n. + automatically declared with the usual identifiers $1, $2, etc. The declarations have the following syntax: -name [ CONSTANT ] -type [ NOT NULL ] [ DEFAULT | := - value ]; +name CONSTANT +type NOT NULL DEFAULT | := + value ; @@ -182,7 +182,7 @@ END; The default value is evaluated every time the block is entered. So - assigning 'now' to a variable of type + assigning 'now' to a variable of type timestamp causes the variable to have the time of the actual function call, not when the function was precompiled into its bytecode. @@ -208,7 +208,7 @@ END; The fields of the rowtype inherit the table's field sizes - or precision for char() etc. data types. + or precision for char() etc. data types. @@ -246,7 +246,7 @@ END; This aliasing is required for composite types given as arguments to - a function. The dot notation $1.salary as in SQL functions is not + a function. The dot notation $1.salary as in SQL functions is not allowed in PL/pgSQL. @@ -311,16 +311,16 @@ RENAME oldname TO newname; Using the table.field%TYPE causes PL/pgSQL to look up the attributes definitions at the first call to the function during the lifetime of a backend. - Suppose we have a table with a char(20) attribute and some PL/pgSQL functions + Suppose we have a table with a char(20) attribute and some PL/pgSQL functions that deal with its content in local variables. Now someone - decides that char(20) isn't enough, dumps the table, drops it, + decides that char(20) is not enough, dumps the table, drops it, recreates it now with the attribute in question defined as - char(40) and restores the data. Ha - he forgot about the + char(40) and restores the data. Hah - he forgot about the functions. The computations inside them will truncate the values to 20 characters. But if they are defined using the table.field%TYPE declarations, they will automagically handle the size change or - if the new table schema defines the attribute as text type. + if the new table schema defines the attribute as text type. @@ -332,15 +332,15 @@ RENAME oldname TO newname; All expressions used in PL/pgSQL statements are processed using the backend's executor. Expressions that appear to contain - constants may in fact require run-time evaluation (e.g. 'now' for the + constants may in fact require runtime evaluation (e.g., 'now' for the timestamp type) so it is impossible for the PL/pgSQL parser to identify real constant values other than the NULL keyword. All expressions are evaluated internally by executing a query - - SELECT expression - - using the SPI manager. In the expression, occurrences of variable + +SELECT expression + + using the SPI manager. In the expression, occurrences of variable identifiers are substituted by parameters and the actual values from the variables are passed to the executor in the parameter array. All expressions used in a PL/pgSQL function are only prepared and @@ -353,7 +353,7 @@ RENAME oldname TO newname; effects to the interpretation of constant values. In detail there is a difference between what the two functions - + CREATE FUNCTION logfunc1 (text) RETURNS timestamp AS ' DECLARE logtxt ALIAS FOR $1; @@ -362,11 +362,11 @@ CREATE FUNCTION logfunc1 (text) RETURNS timestamp AS ' RETURN ''now''; END; ' LANGUAGE 'plpgsql'; - + and - + CREATE FUNCTION logfunc2 (text) RETURNS timestamp AS ' DECLARE logtxt ALIAS FOR $1; @@ -377,31 +377,33 @@ CREATE FUNCTION logfunc2 (text) RETURNS timestamp AS ' RETURN curtime; END; ' LANGUAGE 'plpgsql'; - - - do. In the case of logfunc1(), the Postgres - main parser - knows when preparing the plan for the INSERT, that the string 'now' - should be interpreted as timestamp because the target field of logtable - is of that type. Thus, it will make a constant from it at this time - and this constant value is then used in all invocations of logfunc1() - during the lifetime of the backend. Needless to say that this isn't what the - programmer wanted. + + + do. In the case of logfunc1(), the + Postgres main parser knows when + preparing the plan for the INSERT, that the string + 'now' should be interpreted as + timestamp because the target field of logtable is of + that type. Thus, it will make a constant from it at this time and + this constant value is then used in all invocations of + logfunc1() during the lifetime of the + backend. Needless to say that this isn't what the programmer + wanted. - In the case of logfunc2(), the Postgres + In the case of logfunc2(), the Postgres main parser does not know - what type 'now' should become and therefore it returns a data type of - text containing the string 'now'. During the assignment + what type 'now' should become and therefore it returns a data type of + text containing the string 'now'. During the assignment to the local variable curtime, the PL/pgSQL interpreter casts this - string to the timestamp type by calling the text_out() and timestamp_in() + string to the timestamp type by calling the text_out() and timestamp_in() functions for the conversion. This type checking done by the Postgres main parser got implemented after PL/pgSQL was nearly done. It is a difference between 6.3 and 6.4 and affects all functions - using the prepared plan feature of the SPI manager. + using the prepared plan feature of the SPI manager. Using a local variable in the above manner is currently the only way in PL/pgSQL to get those values interpreted correctly. @@ -433,12 +435,12 @@ CREATE FUNCTION logfunc2 (text) RETURNS timestamp AS ' An assignment of a value to a variable or row/record field is written as - - identifier := expression; - + +identifier := expression; + If the expressions result data type doesn't match the variables data type, or the variable has a size/precision that is known - (as for char(20)), the result value will be implicitly casted by + (as for char(20)), the result value will be implicitly cast by the PL/pgSQL bytecode interpreter using the result types output- and the variables type input-functions. Note that this could potentially result in runtime errors generated by the types input functions. @@ -446,9 +448,9 @@ CREATE FUNCTION logfunc2 (text) RETURNS timestamp AS ' An assignment of a complete selection into a record or row can be done by - + SELECT INTO target expressions FROM ...; - + target can be a record, a row variable or a comma separated list of variables and record-/row-fields. Note that this is quite different from Postgres' normal interpretation of @@ -463,15 +465,15 @@ SELECT INTO target expressions - There is a special variable named FOUND of type bool that can be used + There is a special variable named FOUND of type boolean that can be used immediately after a SELECT INTO to check if an assignment had success. - + SELECT INTO myrec * FROM EMP WHERE empname = myname; IF NOT FOUND THEN RAISE EXCEPTION ''employee % not found'', myname; END IF; - + If the selection returns multiple rows, only the first is moved into the target fields. All others are silently discarded. @@ -488,11 +490,11 @@ END IF; is to execute a SELECT query or doing an assignment (resulting in a PL/pgSQL internal SELECT). But there are cases where someone is not interested in the function's result. - + PERFORM query - - executes a 'SELECT query' over the - SPI manager and discards the result. Identifiers like local + + executes a SELECT query over the + SPI manager and discards the result. Identifiers like local variables are still substituted into parameters. @@ -501,14 +503,13 @@ PERFORM query Executing dynamic queries - - EXECUTE - query-string - - - - where query-string is a string - of type TEXT containing the query to be executed. + + +EXECUTE query-string + + where query-string is a string of + type text containing the query to be + executed. @@ -557,17 +558,17 @@ EXECUTE ''UPDATE tbl SET '' Obtaining other results status - -GET DIAGNOSTICS variable = item [ , ... ] - + +GET DIAGNOSTICS variable = item , ... + This command allows retrieval of system status indicators. Each item is a keyword identifying a state value to be assigned to the specified variable (which should be of the right datatype to receive it). The currently available status - items are ROW_COUNT, the number of rows processed by - the last SQL query sent down to the SQL engine; and - RESULT_OID, the Oid of the last row inserted by the - most recent SQL query. Note that RESULT_OID is only + items are ROW_COUNT, the number of rows processed by + the last SQL query sent down to the SQL engine; and + RESULT_OID, the Oid of the last row inserted by the + most recent SQL query. Note that RESULT_OID is only useful after an INSERT query. @@ -577,9 +578,9 @@ GET DIAGNOSTICS variable = itemReturning from the function - + RETURN expression - + The function terminates and the value of expression will be returned to the upper executor. The return value of a function cannot be undefined. If control reaches the end of the top-level block @@ -600,10 +601,10 @@ RETURN expression As indicated in the above examples there is a RAISE statement that can throw messages into the Postgres elog mechanism. - -RAISE level 'format' [, identifier [...]]; - - Inside the format, "%" is used as a placeholder for the + +RAISE level 'format' , identifier ...; + + Inside the format, % is used as a placeholder for the subsequent comma-separated identifiers. Possible levels are DEBUG (silently suppressed in production running databases), NOTICE (written into the database log and forwarded to the client application) @@ -616,15 +617,15 @@ RAISE level '