You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@hawq.apache.org by lisakowen <gi...@git.apache.org> on 2017/03/09 23:43:46 UTC
[GitHub] incubator-hawq-docs pull request #101: HAWQ-1383 - plpgsql page cleanup, res...
GitHub user lisakowen opened a pull request:
https://github.com/apache/incubator-hawq-docs/pull/101
HAWQ-1383 - plpgsql page cleanup, restructure, more examples
many changes to the plpgsql page:
- restructured and added some general info
- added links to postgres docs where appropriate
- added several concepts sections with examples
- moved polymorphic types section to data types page. also pared down the info, hawq doesn't appear to support anynonarray and anyenum
- added polymorphic example
- removed variadic function argument section - tried several incantations and couldn't get it to work. either hawq doesn't support variadic functions (hard to tell from the code) or user error. if the latter i can add a section/example back in.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/lisakowen/incubator-hawq-docs feature/HAWQ-1383-plpgsql-cleanup
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/incubator-hawq-docs/pull/101.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #101
----
commit d48c238a690a3e102f789a01e9ea3e6e62861310
Author: Lisa Owen <lo...@pivotal.io>
Date: 2017-03-09T02:49:31Z
HAWQ-1383 - plpgsql page cleanup, restructure, more examples
----
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hawq-docs pull request #101: HAWQ-1383 - plpgsql page cleanup, res...
Posted by dyozie <gi...@git.apache.org>.
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."
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hawq-docs pull request #101: HAWQ-1383 - plpgsql page cleanup, res...
Posted by dyozie <gi...@git.apache.org>.
Github user dyozie commented on a diff in the pull request:
https://github.com/apache/incubator-hawq-docs/pull/101#discussion_r105447834
--- 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.
+In place of an explicit specification of the return type, you can declare PL/pgSQL functions with output parameters. This does not add any fundamental capability to the language, but it is often convenient, especially when returning multiple values.
-## <a id="tablefunctions"></a>Table Functions
+Upcoming sections provide specific PL/pgSQL examples using base, composite, and polymorphic argument and return types.
-Table functions are functions that produce a set of rows, made up of either base data types (scalar types) or composite data types (table rows). They are used like a table, view, or subquery in the FROM clause of a query. Columns returned by table functions can be included in SELECT, JOIN, or WHERE clauses in the same manner as a table, view, or subquery column.
+### <a id="plpgsql_namingargs"></a>Naming PL/pgSQL Function Arguments
-If a table function returns a base data type, the single result column name matches the function name. If the function returns a composite type, the result columns get the same names as the individual attributes of the type.
+Arguments passed to PL/pgSQL functions are named with identfiers `$1`, `$2`, `$3`, etc. If you chose, you can also declare aliases for the `$<n>` argument names.
-A table function can be aliased in the FROM clause, but it also can be left unaliased. If a function is used in the FROM clause with no alias, the function name is used as the resulting table name.
+One way to declare an alias is to give the argument a name in the PL/pgSQL function signature. In the following example, the single input argument (`$1`) is named `subtotal`. `subtotal` is used by name in the sales tax calculation in the body of the function.
-Some examples:
+``` sql
+=> CREATE FUNCTION calculate_sales_tax(subtotal real) RETURNS real AS $$
+ BEGIN
+ RETURN subtotal * 0.06;
+ END;
+ $$ LANGUAGE plpgsql;
+=> SELECT calculate_sales_tax( 123.45 );
+ calculate_sales_tax
+ ---------------------
+ 7.407
+ (1 row)
+```
-```sql
-CREATE TABLE foo (fooid int, foosubid int, fooname text);
+You can also explicitly use the `DECLARE` block to declare an alias for a function argument:
+
+``` sql
+DECLARE
+ subtotal ALIAS FOR $1;
+```
-CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
- SELECT * FROM foo WHERE fooid = $1;
-$$ LANGUAGE SQL;
+### <a id="plpgsql_inoutargs"></a>Input and Output PL/pgSQL Function Arguments
-SELECT * FROM getfoo(1) AS t1;
+You can declare PL/pgSQL functions with both input (default) and output arguments. Output arguments provide a convenient way of defining functions that return several values or columns.
-SELECT * FROM foo
- WHERE foosubid IN (
- SELECT foosubid
- FROM getfoo(foo.fooid) z
- WHERE z.fooid = foo.fooid
- );
+Output arguments are named (`$<n>`) and aliased in the same way as input arguments. You identify output arguments in the function signature using the `OUT` keyword.
-CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+In this example, you re-write the `calculate_sales_tax()` function to return the tax in an output argument.
-SELECT * FROM vw_getfoo;
+``` sql
+=> CREATE FUNCTION calculate_sales_tax(subtotal real, OUT tax real) AS $$
+ BEGIN
+ tax := subtotal * 0.06;
+ END;
+ $$ LANGUAGE plpgsql;
+=> SELECT calculate_sales_tax( 123.45 );
```
-In some cases, it is useful to define table functions that can return different column sets depending on how they are invoked. To support this, the table function can be declared as returning the pseudotype record. When such a function is used in a query, the expected row structure must be specified in the query itself, so that the system can know how to parse and plan the query. Consider this example:
+Notice that you do not include the output arguments when you invoke the `calculate_sales_tax()` function. HAWQ considers only the input arguments to define the function's calling signature.
-```sql
-SELECT *
- FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
- AS t1(proname name, prosrc text)
- WHERE proname LIKE 'bytea%';
+## <a id="plpgsqltypes"></a>Identifying Column and Row Data Types
+
+You may need your PL/pgSQL function to operate on column or row data of which you do not know the data types. PL/pgSQL provides `%TYPE` and `%ROWTYPE` keywords for this purpose.
+
+For additional information on PL/pgSQL declarations, see [Declarations](https://www.postgresql.org/docs/8.2/static/plpgsql-declarations.html) in the PostgreSQL documentation.
+
+### <a id="plpgsqltypes_column"></a>Column Type
+
+Use the `<variable>%TYPE` notation to access the data type of a variable. You would use this syntax when you want to declare a variable with the same type as a specific table column.
+
+For example, if you have a column named `order_id` in your `orders` table and you want to declare a variable with the same data type as `orders.order_id`:
+
+``` sql
+DECLARE
+ local_order_id orders.order_id%TYPE
```
-The `dblink` function executes a remote query (see `contrib/dblink`). It is declared to return `record` since it might be used for any kind of query. The actual column set must be specified in the calling query so that the parser knows, for example, what `*` should expand to.
+`%TYPE` is particularly valuable in polymorphic functions, as the data types required for internal variables may change from one function invocation to the next.
+### <a id="plpgsqltypes_row"></a>Row Type
-## <a id="sqlfunctionswithvariablenumbersofarguments"></a>SQL Functions with Variable Numbers of Arguments
+A variable of a composite type is called a row-type variable. Row-type variables can hold a whole row of a query result, providing that the query's column set matches the declared type of the variable.
-SQL functions can be declared to accept variable numbers of arguments, so long as all the "optional" arguments are of the same data type. The optional arguments will be passed to the function as an array. The function is declared by marking the last parameter as VARIADIC; this parameter must be declared as being of an array type. For example:
+You can declare a row-type variable to have the same type as the rows of an existing table or view using the `<table_name>%ROWTYPE` notation. The fields of the row-type variable inherit the table's field sizes and precisions. You access the individual fields of a row-type variable using dot notation, for example `<row_variable>.<field>`.
-```sql
-CREATE FUNCTION mleast(VARIADIC numeric[]) RETURNS numeric AS $$
- SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
-$$ LANGUAGE SQL;
-
-SELECT mleast(10, -1, 5, 4.4);
- mleast
---------
- -1
-(1 row)
+When a function argument is a composite type, the corresponding argument `$<n>` is a row-type variable; you would use the `$<n>.<field>` syntax to access a specific field or column in the row.
+
+**Note**: Only user-defined columns of a table row are accessible via a row-type variable; system and OID columns are not available.
+
+An example using a row-type variable follows. `table1` has integer fields named `order_id` and `order_item_id` and a text field named `product_name`. You will create and execute a `get_order_product()` function to return the order item and product concatenated together in text string.
--- End diff --
"in text string" -> "in a text string"
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hawq-docs pull request #101: HAWQ-1383 - plpgsql page cleanup, res...
Posted by dyozie <gi...@git.apache.org>.
Github user dyozie commented on a diff in the pull request:
https://github.com/apache/incubator-hawq-docs/pull/101#discussion_r105436546
--- 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:
--- End diff --
I see what you mean here, but am concerned about the blanket statement "Pl/pgsql does not have these limitations." You're comparing db-executed functions with a client/server architecture, but the above statement is contrasting with a very general statement about SQL itself. I don't think pgsql gets you around any inherent limitations of SQL as you've stated them.
I guess my concern comes from the recent limitations added regarding cursor support in PL languages. It's natural to assume you can open a cursor and move through a query directly in the language, but really the cursor operations (and other SQL) are still exec'd on the db.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hawq-docs pull request #101: HAWQ-1383 - plpgsql page cleanup, res...
Posted by lisakowen <gi...@git.apache.org>.
Github user lisakowen closed the pull request at:
https://github.com/apache/incubator-hawq-docs/pull/101
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hawq-docs pull request #101: HAWQ-1383 - plpgsql page cleanup, res...
Posted by lisakowen <gi...@git.apache.org>.
Github user lisakowen commented on a diff in the pull request:
https://github.com/apache/incubator-hawq-docs/pull/101#discussion_r105472315
--- 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:
--- End diff --
good point. i will reword.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hawq-docs pull request #101: HAWQ-1383 - plpgsql page cleanup, res...
Posted by sansanichfb <gi...@git.apache.org>.
Github user sansanichfb commented on a diff in the pull request:
https://github.com/apache/incubator-hawq-docs/pull/101#discussion_r105477434
--- Diff: markdown/plext/using_plpgsql.html.md.erb ---
@@ -19,143 +19,283 @@ 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.
+The PL/pgSQL language addresses some of these limitations. When creating functions with PL/pgSQL, 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
+- Re-using prepared queries avoids 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 ];
+```
--- End diff --
Maybe add EXCEPTION block as well.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hawq-docs pull request #101: HAWQ-1383 - plpgsql page cleanup, res...
Posted by dyozie <gi...@git.apache.org>.
Github user dyozie commented on a diff in the pull request:
https://github.com/apache/incubator-hawq-docs/pull/101#discussion_r105446543
--- 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.
+In place of an explicit specification of the return type, you can declare PL/pgSQL functions with output parameters. This does not add any fundamental capability to the language, but it is often convenient, especially when returning multiple values.
-## <a id="tablefunctions"></a>Table Functions
+Upcoming sections provide specific PL/pgSQL examples using base, composite, and polymorphic argument and return types.
-Table functions are functions that produce a set of rows, made up of either base data types (scalar types) or composite data types (table rows). They are used like a table, view, or subquery in the FROM clause of a query. Columns returned by table functions can be included in SELECT, JOIN, or WHERE clauses in the same manner as a table, view, or subquery column.
+### <a id="plpgsql_namingargs"></a>Naming PL/pgSQL Function Arguments
-If a table function returns a base data type, the single result column name matches the function name. If the function returns a composite type, the result columns get the same names as the individual attributes of the type.
+Arguments passed to PL/pgSQL functions are named with identfiers `$1`, `$2`, `$3`, etc. If you chose, you can also declare aliases for the `$<n>` argument names.
-A table function can be aliased in the FROM clause, but it also can be left unaliased. If a function is used in the FROM clause with no alias, the function name is used as the resulting table name.
+One way to declare an alias is to give the argument a name in the PL/pgSQL function signature. In the following example, the single input argument (`$1`) is named `subtotal`. `subtotal` is used by name in the sales tax calculation in the body of the function.
-Some examples:
+``` sql
+=> CREATE FUNCTION calculate_sales_tax(subtotal real) RETURNS real AS $$
+ BEGIN
+ RETURN subtotal * 0.06;
+ END;
+ $$ LANGUAGE plpgsql;
+=> SELECT calculate_sales_tax( 123.45 );
+ calculate_sales_tax
+ ---------------------
+ 7.407
+ (1 row)
+```
-```sql
-CREATE TABLE foo (fooid int, foosubid int, fooname text);
+You can also explicitly use the `DECLARE` block to declare an alias for a function argument:
+
+``` sql
+DECLARE
+ subtotal ALIAS FOR $1;
+```
-CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
- SELECT * FROM foo WHERE fooid = $1;
-$$ LANGUAGE SQL;
+### <a id="plpgsql_inoutargs"></a>Input and Output PL/pgSQL Function Arguments
-SELECT * FROM getfoo(1) AS t1;
+You can declare PL/pgSQL functions with both input (default) and output arguments. Output arguments provide a convenient way of defining functions that return several values or columns.
-SELECT * FROM foo
- WHERE foosubid IN (
- SELECT foosubid
- FROM getfoo(foo.fooid) z
- WHERE z.fooid = foo.fooid
- );
+Output arguments are named (`$<n>`) and aliased in the same way as input arguments. You identify output arguments in the function signature using the `OUT` keyword.
--- End diff --
Again, I'd lose the parens here.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hawq-docs pull request #101: HAWQ-1383 - plpgsql page cleanup, res...
Posted by lisakowen <gi...@git.apache.org>.
Github user lisakowen commented on a diff in the pull request:
https://github.com/apache/incubator-hawq-docs/pull/101#discussion_r105474539
--- 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.
+In place of an explicit specification of the return type, you can declare PL/pgSQL functions with output parameters. This does not add any fundamental capability to the language, but it is often convenient, especially when returning multiple values.
-## <a id="tablefunctions"></a>Table Functions
+Upcoming sections provide specific PL/pgSQL examples using base, composite, and polymorphic argument and return types.
-Table functions are functions that produce a set of rows, made up of either base data types (scalar types) or composite data types (table rows). They are used like a table, view, or subquery in the FROM clause of a query. Columns returned by table functions can be included in SELECT, JOIN, or WHERE clauses in the same manner as a table, view, or subquery column.
+### <a id="plpgsql_namingargs"></a>Naming PL/pgSQL Function Arguments
-If a table function returns a base data type, the single result column name matches the function name. If the function returns a composite type, the result columns get the same names as the individual attributes of the type.
+Arguments passed to PL/pgSQL functions are named with identfiers `$1`, `$2`, `$3`, etc. If you chose, you can also declare aliases for the `$<n>` argument names.
--- End diff --
i added an example using ALIASing of $n.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hawq-docs pull request #101: HAWQ-1383 - plpgsql page cleanup, res...
Posted by dyozie <gi...@git.apache.org>.
Github user dyozie commented on a diff in the pull request:
https://github.com/apache/incubator-hawq-docs/pull/101#discussion_r105444745
--- 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.
+In place of an explicit specification of the return type, you can declare PL/pgSQL functions with output parameters. This does not add any fundamental capability to the language, but it is often convenient, especially when returning multiple values.
-## <a id="tablefunctions"></a>Table Functions
+Upcoming sections provide specific PL/pgSQL examples using base, composite, and polymorphic argument and return types.
-Table functions are functions that produce a set of rows, made up of either base data types (scalar types) or composite data types (table rows). They are used like a table, view, or subquery in the FROM clause of a query. Columns returned by table functions can be included in SELECT, JOIN, or WHERE clauses in the same manner as a table, view, or subquery column.
+### <a id="plpgsql_namingargs"></a>Naming PL/pgSQL Function Arguments
-If a table function returns a base data type, the single result column name matches the function name. If the function returns a composite type, the result columns get the same names as the individual attributes of the type.
+Arguments passed to PL/pgSQL functions are named with identfiers `$1`, `$2`, `$3`, etc. If you chose, you can also declare aliases for the `$<n>` argument names.
--- End diff --
chose -> choose
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hawq-docs pull request #101: HAWQ-1383 - plpgsql page cleanup, res...
Posted by dyozie <gi...@git.apache.org>.
Github user dyozie commented on a diff in the pull request:
https://github.com/apache/incubator-hawq-docs/pull/101#discussion_r105448115
--- 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.
+In place of an explicit specification of the return type, you can declare PL/pgSQL functions with output parameters. This does not add any fundamental capability to the language, but it is often convenient, especially when returning multiple values.
-## <a id="tablefunctions"></a>Table Functions
+Upcoming sections provide specific PL/pgSQL examples using base, composite, and polymorphic argument and return types.
-Table functions are functions that produce a set of rows, made up of either base data types (scalar types) or composite data types (table rows). They are used like a table, view, or subquery in the FROM clause of a query. Columns returned by table functions can be included in SELECT, JOIN, or WHERE clauses in the same manner as a table, view, or subquery column.
+### <a id="plpgsql_namingargs"></a>Naming PL/pgSQL Function Arguments
-If a table function returns a base data type, the single result column name matches the function name. If the function returns a composite type, the result columns get the same names as the individual attributes of the type.
+Arguments passed to PL/pgSQL functions are named with identfiers `$1`, `$2`, `$3`, etc. If you chose, you can also declare aliases for the `$<n>` argument names.
-A table function can be aliased in the FROM clause, but it also can be left unaliased. If a function is used in the FROM clause with no alias, the function name is used as the resulting table name.
+One way to declare an alias is to give the argument a name in the PL/pgSQL function signature. In the following example, the single input argument (`$1`) is named `subtotal`. `subtotal` is used by name in the sales tax calculation in the body of the function.
-Some examples:
+``` sql
+=> CREATE FUNCTION calculate_sales_tax(subtotal real) RETURNS real AS $$
+ BEGIN
+ RETURN subtotal * 0.06;
+ END;
+ $$ LANGUAGE plpgsql;
+=> SELECT calculate_sales_tax( 123.45 );
+ calculate_sales_tax
+ ---------------------
+ 7.407
+ (1 row)
+```
-```sql
-CREATE TABLE foo (fooid int, foosubid int, fooname text);
+You can also explicitly use the `DECLARE` block to declare an alias for a function argument:
+
+``` sql
+DECLARE
+ subtotal ALIAS FOR $1;
+```
-CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
- SELECT * FROM foo WHERE fooid = $1;
-$$ LANGUAGE SQL;
+### <a id="plpgsql_inoutargs"></a>Input and Output PL/pgSQL Function Arguments
-SELECT * FROM getfoo(1) AS t1;
+You can declare PL/pgSQL functions with both input (default) and output arguments. Output arguments provide a convenient way of defining functions that return several values or columns.
-SELECT * FROM foo
- WHERE foosubid IN (
- SELECT foosubid
- FROM getfoo(foo.fooid) z
- WHERE z.fooid = foo.fooid
- );
+Output arguments are named (`$<n>`) and aliased in the same way as input arguments. You identify output arguments in the function signature using the `OUT` keyword.
-CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+In this example, you re-write the `calculate_sales_tax()` function to return the tax in an output argument.
-SELECT * FROM vw_getfoo;
+``` sql
+=> CREATE FUNCTION calculate_sales_tax(subtotal real, OUT tax real) AS $$
+ BEGIN
+ tax := subtotal * 0.06;
+ END;
+ $$ LANGUAGE plpgsql;
+=> SELECT calculate_sales_tax( 123.45 );
```
-In some cases, it is useful to define table functions that can return different column sets depending on how they are invoked. To support this, the table function can be declared as returning the pseudotype record. When such a function is used in a query, the expected row structure must be specified in the query itself, so that the system can know how to parse and plan the query. Consider this example:
+Notice that you do not include the output arguments when you invoke the `calculate_sales_tax()` function. HAWQ considers only the input arguments to define the function's calling signature.
-```sql
-SELECT *
- FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
- AS t1(proname name, prosrc text)
- WHERE proname LIKE 'bytea%';
+## <a id="plpgsqltypes"></a>Identifying Column and Row Data Types
+
+You may need your PL/pgSQL function to operate on column or row data of which you do not know the data types. PL/pgSQL provides `%TYPE` and `%ROWTYPE` keywords for this purpose.
+
+For additional information on PL/pgSQL declarations, see [Declarations](https://www.postgresql.org/docs/8.2/static/plpgsql-declarations.html) in the PostgreSQL documentation.
+
+### <a id="plpgsqltypes_column"></a>Column Type
+
+Use the `<variable>%TYPE` notation to access the data type of a variable. You would use this syntax when you want to declare a variable with the same type as a specific table column.
+
+For example, if you have a column named `order_id` in your `orders` table and you want to declare a variable with the same data type as `orders.order_id`:
+
+``` sql
+DECLARE
+ local_order_id orders.order_id%TYPE
```
-The `dblink` function executes a remote query (see `contrib/dblink`). It is declared to return `record` since it might be used for any kind of query. The actual column set must be specified in the calling query so that the parser knows, for example, what `*` should expand to.
+`%TYPE` is particularly valuable in polymorphic functions, as the data types required for internal variables may change from one function invocation to the next.
+### <a id="plpgsqltypes_row"></a>Row Type
-## <a id="sqlfunctionswithvariablenumbersofarguments"></a>SQL Functions with Variable Numbers of Arguments
+A variable of a composite type is called a row-type variable. Row-type variables can hold a whole row of a query result, providing that the query's column set matches the declared type of the variable.
-SQL functions can be declared to accept variable numbers of arguments, so long as all the "optional" arguments are of the same data type. The optional arguments will be passed to the function as an array. The function is declared by marking the last parameter as VARIADIC; this parameter must be declared as being of an array type. For example:
+You can declare a row-type variable to have the same type as the rows of an existing table or view using the `<table_name>%ROWTYPE` notation. The fields of the row-type variable inherit the table's field sizes and precisions. You access the individual fields of a row-type variable using dot notation, for example `<row_variable>.<field>`.
-```sql
-CREATE FUNCTION mleast(VARIADIC numeric[]) RETURNS numeric AS $$
- SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
-$$ LANGUAGE SQL;
-
-SELECT mleast(10, -1, 5, 4.4);
- mleast
---------
- -1
-(1 row)
+When a function argument is a composite type, the corresponding argument `$<n>` is a row-type variable; you would use the `$<n>.<field>` syntax to access a specific field or column in the row.
+
+**Note**: Only user-defined columns of a table row are accessible via a row-type variable; system and OID columns are not available.
+
+An example using a row-type variable follows. `table1` has integer fields named `order_id` and `order_item_id` and a text field named `product_name`. You will create and execute a `get_order_product()` function to return the order item and product concatenated together in text string.
+
+``` sql
+=> CREATE TABLE table1 (order_id integer, order_item_id integer, product_name text );
+=> INSERT INTO table1 VALUES( 1, 13, 'Paper Towels' );
+=> INSERT INTO table1 VALUES( 1, 17, 'Pencils' );
+=> INSERT INTO table1 VALUES( 2, 19, 'Light Bulbs' );
+=> CREATE FUNCTION get_order_product( oid integer ) RETURNS text AS $$
+ DECLARE
+ table1_row table1%ROWTYPE;
+ BEGIN
+ SELECT * FROM table1 WHERE table1.order_id = oid INTO table1_row;
+ RETURN table1_row.order_item_id || table1_row.product_name;
+ END;
+ $$ LANGUAGE plpgsql;
+=> SELECT get_order_product(2);
+ get_order_product
+ -------------------
+ 19Light Bulbs
+ (1 row)
```
-Effectively, all the actual arguments at or beyond the VARIADIC position are gathered up into a one-dimensional array, as if you had written
+## <a id="plpgsqlexamples"></a>PL/pgSQL Functions as Table Sources
+
+You can use PL/pgSQL functions in the same way you specify a table, view, or subquery in the `FROM` clause of a query. These functions are referred to as table functions, and can return both base and composite types. Functions that return base types produce a one-column table. Functions that return composite types produce a table column for each attribute of the composite type. You can use the columns returned by table functions in `SELECT`, `JOIN`, or `WHERE` clauses in the same manner as you would a table, view or subquery column.
--- End diff --
"table, view" -> "table, view,"
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hawq-docs pull request #101: HAWQ-1383 - plpgsql page cleanup, res...
Posted by lisakowen <gi...@git.apache.org>.
Github user lisakowen commented on a diff in the pull request:
https://github.com/apache/incubator-hawq-docs/pull/101#discussion_r105483070
--- Diff: markdown/plext/using_plpgsql.html.md.erb ---
@@ -19,143 +19,283 @@ 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.
+The PL/pgSQL language addresses some of these limitations. When creating functions with PL/pgSQL, 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
+- Re-using prepared queries avoids 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 ];
+```
--- End diff --
thanks for reviewing, @sansanichfb! i used the block definition identified in the postgres pl/pgsql docs. i will add a blurb about exceptions and error handling. (i didn't want to duplicate all of the postgres info on this page, my goal was to provide some introductory info and examples to get the user up and running with using pl/pgsql in common use scenarios.)
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hawq-docs pull request #101: HAWQ-1383 - plpgsql page cleanup, res...
Posted by dyozie <gi...@git.apache.org>.
Github user dyozie commented on a diff in the pull request:
https://github.com/apache/incubator-hawq-docs/pull/101#discussion_r105445495
--- 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.
+In place of an explicit specification of the return type, you can declare PL/pgSQL functions with output parameters. This does not add any fundamental capability to the language, but it is often convenient, especially when returning multiple values.
-## <a id="tablefunctions"></a>Table Functions
+Upcoming sections provide specific PL/pgSQL examples using base, composite, and polymorphic argument and return types.
-Table functions are functions that produce a set of rows, made up of either base data types (scalar types) or composite data types (table rows). They are used like a table, view, or subquery in the FROM clause of a query. Columns returned by table functions can be included in SELECT, JOIN, or WHERE clauses in the same manner as a table, view, or subquery column.
+### <a id="plpgsql_namingargs"></a>Naming PL/pgSQL Function Arguments
-If a table function returns a base data type, the single result column name matches the function name. If the function returns a composite type, the result columns get the same names as the individual attributes of the type.
+Arguments passed to PL/pgSQL functions are named with identfiers `$1`, `$2`, `$3`, etc. If you chose, you can also declare aliases for the `$<n>` argument names.
--- End diff --
It seems strange that none of the examples actually use the `$<n>` convention. Maybe the first example should use it before introducing aliases? Just a thought, probably not a big deal.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hawq-docs pull request #101: HAWQ-1383 - plpgsql page cleanup, res...
Posted by dyozie <gi...@git.apache.org>.
Github user dyozie commented on a diff in the pull request:
https://github.com/apache/incubator-hawq-docs/pull/101#discussion_r105446890
--- 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.
+In place of an explicit specification of the return type, you can declare PL/pgSQL functions with output parameters. This does not add any fundamental capability to the language, but it is often convenient, especially when returning multiple values.
-## <a id="tablefunctions"></a>Table Functions
+Upcoming sections provide specific PL/pgSQL examples using base, composite, and polymorphic argument and return types.
-Table functions are functions that produce a set of rows, made up of either base data types (scalar types) or composite data types (table rows). They are used like a table, view, or subquery in the FROM clause of a query. Columns returned by table functions can be included in SELECT, JOIN, or WHERE clauses in the same manner as a table, view, or subquery column.
+### <a id="plpgsql_namingargs"></a>Naming PL/pgSQL Function Arguments
-If a table function returns a base data type, the single result column name matches the function name. If the function returns a composite type, the result columns get the same names as the individual attributes of the type.
+Arguments passed to PL/pgSQL functions are named with identfiers `$1`, `$2`, `$3`, etc. If you chose, you can also declare aliases for the `$<n>` argument names.
-A table function can be aliased in the FROM clause, but it also can be left unaliased. If a function is used in the FROM clause with no alias, the function name is used as the resulting table name.
+One way to declare an alias is to give the argument a name in the PL/pgSQL function signature. In the following example, the single input argument (`$1`) is named `subtotal`. `subtotal` is used by name in the sales tax calculation in the body of the function.
-Some examples:
+``` sql
+=> CREATE FUNCTION calculate_sales_tax(subtotal real) RETURNS real AS $$
+ BEGIN
+ RETURN subtotal * 0.06;
+ END;
+ $$ LANGUAGE plpgsql;
+=> SELECT calculate_sales_tax( 123.45 );
+ calculate_sales_tax
+ ---------------------
+ 7.407
+ (1 row)
+```
-```sql
-CREATE TABLE foo (fooid int, foosubid int, fooname text);
+You can also explicitly use the `DECLARE` block to declare an alias for a function argument:
+
+``` sql
+DECLARE
+ subtotal ALIAS FOR $1;
+```
-CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
- SELECT * FROM foo WHERE fooid = $1;
-$$ LANGUAGE SQL;
+### <a id="plpgsql_inoutargs"></a>Input and Output PL/pgSQL Function Arguments
-SELECT * FROM getfoo(1) AS t1;
+You can declare PL/pgSQL functions with both input (default) and output arguments. Output arguments provide a convenient way of defining functions that return several values or columns.
-SELECT * FROM foo
- WHERE foosubid IN (
- SELECT foosubid
- FROM getfoo(foo.fooid) z
- WHERE z.fooid = foo.fooid
- );
+Output arguments are named (`$<n>`) and aliased in the same way as input arguments. You identify output arguments in the function signature using the `OUT` keyword.
-CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+In this example, you re-write the `calculate_sales_tax()` function to return the tax in an output argument.
-SELECT * FROM vw_getfoo;
+``` sql
+=> CREATE FUNCTION calculate_sales_tax(subtotal real, OUT tax real) AS $$
+ BEGIN
+ tax := subtotal * 0.06;
+ END;
+ $$ LANGUAGE plpgsql;
+=> SELECT calculate_sales_tax( 123.45 );
```
-In some cases, it is useful to define table functions that can return different column sets depending on how they are invoked. To support this, the table function can be declared as returning the pseudotype record. When such a function is used in a query, the expected row structure must be specified in the query itself, so that the system can know how to parse and plan the query. Consider this example:
+Notice that you do not include the output arguments when you invoke the `calculate_sales_tax()` function. HAWQ considers only the input arguments to define the function's calling signature.
-```sql
-SELECT *
- FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
- AS t1(proname name, prosrc text)
- WHERE proname LIKE 'bytea%';
+## <a id="plpgsqltypes"></a>Identifying Column and Row Data Types
+
+You may need your PL/pgSQL function to operate on column or row data of which you do not know the data types. PL/pgSQL provides `%TYPE` and `%ROWTYPE` keywords for this purpose.
--- End diff --
"know the data types" -> "know the data type"
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hawq-docs pull request #101: HAWQ-1383 - plpgsql page cleanup, res...
Posted by dyozie <gi...@git.apache.org>.
Github user dyozie commented on a diff in the pull request:
https://github.com/apache/incubator-hawq-docs/pull/101#discussion_r105437227
--- 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.
--- End diff --
edit: "unless **they are** double-quoted."
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hawq-docs pull request #101: HAWQ-1383 - plpgsql page cleanup, res...
Posted by dyozie <gi...@git.apache.org>.
Github user dyozie commented on a diff in the pull request:
https://github.com/apache/incubator-hawq-docs/pull/101#discussion_r105438584
--- 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.
--- End diff --
Do we need to show an example of using BEGIN/END for transaction management in a block?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hawq-docs pull request #101: HAWQ-1383 - plpgsql page cleanup, res...
Posted by dyozie <gi...@git.apache.org>.
Github user dyozie commented on a diff in the pull request:
https://github.com/apache/incubator-hawq-docs/pull/101#discussion_r105445401
--- 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.
+In place of an explicit specification of the return type, you can declare PL/pgSQL functions with output parameters. This does not add any fundamental capability to the language, but it is often convenient, especially when returning multiple values.
-## <a id="tablefunctions"></a>Table Functions
+Upcoming sections provide specific PL/pgSQL examples using base, composite, and polymorphic argument and return types.
-Table functions are functions that produce a set of rows, made up of either base data types (scalar types) or composite data types (table rows). They are used like a table, view, or subquery in the FROM clause of a query. Columns returned by table functions can be included in SELECT, JOIN, or WHERE clauses in the same manner as a table, view, or subquery column.
+### <a id="plpgsql_namingargs"></a>Naming PL/pgSQL Function Arguments
-If a table function returns a base data type, the single result column name matches the function name. If the function returns a composite type, the result columns get the same names as the individual attributes of the type.
+Arguments passed to PL/pgSQL functions are named with identfiers `$1`, `$2`, `$3`, etc. If you chose, you can also declare aliases for the `$<n>` argument names.
-A table function can be aliased in the FROM clause, but it also can be left unaliased. If a function is used in the FROM clause with no alias, the function name is used as the resulting table name.
+One way to declare an alias is to give the argument a name in the PL/pgSQL function signature. In the following example, the single input argument (`$1`) is named `subtotal`. `subtotal` is used by name in the sales tax calculation in the body of the function.
--- End diff --
Maybe remove parens from (`$1`) Seems a little cumbersome when you're dealing with specific syntax like this.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hawq-docs pull request #101: HAWQ-1383 - plpgsql page cleanup, res...
Posted by sansanichfb <gi...@git.apache.org>.
Github user sansanichfb commented on a diff in the pull request:
https://github.com/apache/incubator-hawq-docs/pull/101#discussion_r105483266
--- Diff: markdown/plext/using_plpgsql.html.md.erb ---
@@ -19,143 +19,283 @@ 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.
+The PL/pgSQL language addresses some of these limitations. When creating functions with PL/pgSQL, 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
+- Re-using prepared queries avoids 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 ];
+```
--- End diff --
got it
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hawq-docs pull request #101: HAWQ-1383 - plpgsql page cleanup, res...
Posted by dyozie <gi...@git.apache.org>.
Github user dyozie commented on a diff in the pull request:
https://github.com/apache/incubator-hawq-docs/pull/101#discussion_r105436857
--- 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
--- End diff --
Maybe edit to "Re-using prepared queries avoids multiple..."
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hawq-docs pull request #101: HAWQ-1383 - plpgsql page cleanup, res...
Posted by lisakowen <gi...@git.apache.org>.
Github user lisakowen commented on a diff in the pull request:
https://github.com/apache/incubator-hawq-docs/pull/101#discussion_r105472939
--- 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.
--- End diff --
i don't believe you can use begin/end in a plpgsql block. i think the whole function is run in a transaction.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---