[
https://issues.apache.org/jira/browse/HAWQ-1383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15905660#comment-15905660
]
ASF GitHub Bot commented on HAWQ-1383:
--------------------------------------
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.)
> docs - pl/pgsql page cleanup
> ----------------------------
>
> Key: HAWQ-1383
> URL: https://issues.apache.org/jira/browse/HAWQ-1383
> Project: Apache HAWQ
> Issue Type: Improvement
> Components: Documentation
> Reporter: Lisa Owen
> Assignee: David Yozie
>
> various updates to pl/pgsql page:
> - move polymorphic types discussion to a more general section
> - remove dblink reference
> - use actual pl/pgsql examples, not SQL examples
> - other miscellanous clean-up and clarifications
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
