[ https://issues.apache.org/jira/browse/HAWQ-1383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15905463#comment-15905463 ]
ASF GitHub Bot commented on HAWQ-1383: -------------------------------------- Github user dyozie commented on a diff in the pull request: https://github.com/apache/incubator-hawq-docs/pull/101#discussion_r105444363 --- Diff: markdown/plext/using_plpgsql.html.md.erb --- @@ -19,143 +19,278 @@ software distributed under the License is distributed on an KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. ---> +--> -SQL is the language of most other relational databases use as query language. It is portable and easy to learn. But every SQL statement must be executed individually by the database server. +PL/pgSQL is a trusted procedural language that is automatically installed and registered in all HAWQ databases. With PL/pgSQL, you can: -PL/pgSQL is a loadable procedural language. PL/SQL can do the following: +- Create functions +- Add control structures to the SQL language +- Perform complex computations +- Use all of the data types, functions, and operators defined in SQL -- create functions -- add control structures to the SQL language -- perform complex computations -- inherit all user-defined types, functions, and operators -- be trusted by the server +SQL is the language most relational databases use as a query language. While it is portable and easy to learn, every SQL statement is individually executed by the database server. Your client application sends each query to the database server, waits for it to be processed, receives and processes the results, does some computation, then sends further queries to the server. This back-and-forth requires interprocess communication and incurs network overhead if your client is on a different host than the HAWQ master. -You can use functions created with PL/pgSQL with any database that supports built-in functions. For example, it is possible to create complex conditional computation functions and later use them to define operators or use them in index expressions. +PL/pgSQL does not have these limitations. When creating functions with the PL/pgSQL language, you can group computation blocks and queries inside the database server, combining the power of a procedural language and the ease of use of SQL, but with considerable savings of client/server communication overhead. With PL/pgSQL: -Every SQL statement must be executed individually by the database server. Your client application must send each query to the database server, wait for it to be processed, receive and process the results, do some computation, then send further queries to the server. This requires interprocess communication and incurs network overhead if your client is on a different machine than the database server. +- Extra round trips between client and server are eliminated +- Intermediate, and perhaps unneeded, results do not have to be marshaled or transferred between the server and client +- You avoid multiple rounds of query parsing + -With PL/pgSQL, you can group a block of computation and a series of queries inside the database server, thus having the power of a procedural language and the ease of use of SQL, but with considerable savings of client/server communication overhead. +## <a id="plpgsql_structure"></a>PL/pgSQL Function Syntax -- Extra round trips between client and server are eliminated -- Intermediate results that the client does not need do not have to be marshaled or transferred between server and client -- Multiple rounds of query parsing can be avoided +PL/pgSQL is a block-structured language. The complete text of a function definition must be a block, which is defined as: -This can result in a considerable performance increase as compared to an application that does not use stored functions. +``` sql +[ <label> ] +[ DECLARE + declarations ] +BEGIN + statements +END [ label ]; +``` -PL/pgSQL supports all the data types, operators, and functions of SQL. +Each declaration and each statement within a block is terminated by a semicolon. A block that appears within another block must have a semicolon after `END`, as shown above; however the final `END` that concludes a function body does not require a semicolon. + +You can specify all key words and identifiers in mixed upper and lower case. Identifiers are implicitly converted to lowercase unless double-quoted. + +PL/pgSQL supports two types of comments. 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 you can enclose double dash comments into a block comment and a double dash can hide the block comment delimiters `/*` and `*/`. + +This example PL/pgSQL function adds thirteen to an integer: + +``` sql +=> CREATE FUNCTION add_thirteen(i integer) RETURNS integer AS + $$ + DECLARE + incvalue integer := 13; + BEGIN + -- add thirteen to i + RETURN i + incvalue; + END; + $$ LANGUAGE plpgsql; +=> SELECT add_thirteen( 11 ); + increment + ----------- + 24 + (1 row) +``` -**Note:** PL/pgSQL is automatically installed and registered in all HAWQ databases. +**Note**: Do not to confuse the use of `BEGIN/END` for grouping statements in PL/pgSQL with the database commands for transaction control. PL/pgSQL's BEGIN/END are only for statement grouping; they do not start or end a transaction. -## <a id="supportedargumentandresultdatatypes"></a>Supported Data Types for Arguments and Results -Functions written in PL/pgSQL accept as arguments any scalar or array data type supported by the server, and they can return a result containing this data type. They can also accept or return any composite type (row type) specified by name. It is also possible to declare a PL/pgSQL function as returning record, which means that the result is a row type whose columns are determined by specification in the calling query. See <a href="#tablefunctions" class="xref">Table Functions</a>. +## <a id="plpgsql_structure"></a>PL/pgSQL Statements and Control Structures -PL/pgSQL functions can be declared to accept a variable number of arguments by using the VARIADIC marker. This works exactly the same way as for SQL functions. See <a href="#sqlfunctionswithvariablenumbersofarguments" class="xref">SQL Functions with Variable Numbers of Arguments</a>. +Refer to the PostgreSQL documentation for detailed information on the statements and control structures supported by the PL/pgSQL language: -PL/pgSQLfunctions can also be declared to accept and return the polymorphic typesanyelement,anyarray,anynonarray, and anyenum. The actual data types handled by a polymorphic function can vary from call to call, as discussed in <a href="http://www.postgresql.org/docs/8.4/static/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC" class="xref">Section 34.2.5</a>. An example is shown in <a href="http://www.postgresql.org/docs/8.4/static/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES" class="xref">Section 38.3.1</a>. +- You can execute SQL commands in PL/pgSQL functions using `EXECUTE`, `PERFORM`, and `SELECT ... INTO` statements. Refer to [Basic Statements](https://www.postgresql.org/docs/8.2/static/plpgsql-statements.html) for PL/pgSQL specifics in this area. -PL/pgSQL functions can also be declared to return a "set" (or table) of any data type that can be returned as a single instance. Such a function generates its output by executing RETURN NEXT for each desired element of the result set, or by using RETURN QUERY to output the result of evaluating a query. +- [Control Structures](https://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html) identifies the data manipulation constructs supported by PL/pgSQL. -Finally, a PL/pgSQL function can be declared to return void if it has no useful return value. -PL/pgSQL functions can also be declared with output parameters in place of an explicit specification of the return type. This does not add any fundamental capability to the language, but it is often convenient, especially for returning multiple values. The RETURNS TABLE notation can also be used in place of RETURNS SETOF . +## <a id="supportedargumentandresultdatatypes"></a>PL/pgSQL Argument and Result Data Types -This topic describes the following PL/pgSQLconcepts: +Functions written in PL/pgSQL accept as arguments any base or array data type supported by the server, and they can return a result containing any of these data types. PL/pgSQL functions can also accept and return any composite type (row-type) specified by name. -- [Table Functions](#tablefunctions) -- [SQL Functions with Variable number of Arguments](#sqlfunctionswithvariablenumbersofarguments) -- [Polymorphic Types](#polymorphictypes) +You can declare PL/pgSQL functions to accept and return the polymorphic types `anyelement` and `anyarray` types. PL/pgSQL functions can also be declared to return a set (or table) of any data type that can be returned as a single instance. Finally, you can declare a PL/pgSQL function to return `void` if it has no useful return value. --- End diff -- Change "the polymorphic types `anyelement` and `anyarray` types." -> "the polymorphic `anyelement` and `anyarray` types." > docs - pl/pgsql page cleanup > ---------------------------- > > Key: HAWQ-1383 > URL: https://issues.apache.org/jira/browse/HAWQ-1383 > Project: Apache HAWQ > Issue Type: Improvement > Components: Documentation > Reporter: Lisa Owen > Assignee: David Yozie > > various updates to pl/pgsql page: > - move polymorphic types discussion to a more general section > - remove dblink reference > - use actual pl/pgsql examples, not SQL examples > - other miscellanous clean-up and clarifications -- This message was sent by Atlassian JIRA (v6.3.15#6346)