[jira] [Closed] (HAWQ-1380) Keep hawq_toolkit schema check in HAWQ native side
[ https://issues.apache.org/jira/browse/HAWQ-1380?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Hongxu Ma closed HAWQ-1380. --- Resolution: Fixed fixed > Keep hawq_toolkit schema check in HAWQ native side > -- > > Key: HAWQ-1380 > URL: https://issues.apache.org/jira/browse/HAWQ-1380 > Project: Apache HAWQ > Issue Type: Sub-task > Components: Security >Reporter: Hongxu Ma >Assignee: Hongxu Ma > Fix For: 2.2.0.0-incubating > > > Given that *hawq_toolkit* is an administrative schema, let's keep it in HAWQ > Native, outside of Ranger. -- This message was sent by Atlassian JIRA (v6.3.15#6346)
[jira] [Closed] (HAWQ-1381) Core dump when execute 'select * from hawq_toolkit.__hawq_log_master_ext;' on macOS
[
https://issues.apache.org/jira/browse/HAWQ-1381?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Hongxu Ma closed HAWQ-1381.
---
Resolution: Fixed
fixed
> Core dump when execute 'select * from hawq_toolkit.__hawq_log_master_ext;' on
> macOS
> ---
>
> Key: HAWQ-1381
> URL: https://issues.apache.org/jira/browse/HAWQ-1381
> Project: Apache HAWQ
> Issue Type: Bug
>Reporter: Hongxu Ma
>Assignee: Hongxu Ma
> Fix For: 2.2.0.0-incubating
>
>
> macOS 10.12.1
> {code}
> Thread 0 Crashed:: Dispatch queue: com.apple.main-thread
> 0 libsystem_kernel.dylib 0x7fffded60dda __pthread_kill + 10
> 1 libsystem_pthread.dylib 0x7fffdee4c787 pthread_kill + 90
> 2 libsystem_c.dylib 0x7fffdecc6420 abort + 129
> 3 libsystem_c.dylib 0x7fffdecc6592 abort_report_np + 181
> 4 libsystem_c.dylib 0x7fffdececf28 __chk_fail + 48
> 5 libsystem_c.dylib 0x7fffdececef8 __chk_fail_overflow +
> 16
> 6 libsystem_c.dylib 0x7fffdeced413 __sprintf_chk + 199
> 7 postgres0x00010e39e394 external_set_env_vars
> + 916
> 8 postgres0x00010e39c335
> open_external_readable_source + 181
> 9 postgres0x00010e39cba6 external_getnext + 70
> 10 postgres0x00010e62a42e ExternalNext + 110
> 11 postgres0x00010e606518 ExecScan + 72
> 12 postgres0x00010e62a3af ExecExternalScan + 31
> 13 postgres0x00010e5f4bd3 ExecProcNode + 739
> 14 postgres0x00010e5e88e2 ExecutePlan + 722
> 15 postgres0x00010e5e82af ExecutorRun + 1471
> 16 postgres0x00010e83531d PortalRunSelect + 317
> 17 postgres0x00010e834dc8 PortalRun + 952
> 18 postgres0x00010e82ad0f exec_simple_query +
> 2367
> 19 postgres0x00010e828eeb PostgresMain + 7979
> 20 postgres0x00010e7c4198 BackendRun + 1048
> 21 postgres0x00010e7c0f35 BackendStartup + 373
> 22 postgres0x00010e7bde10 ServerLoop + 1248
> 23 postgres0x00010e7bc3cb PostmasterMain + 4859
> 24 postgres0x00010e69f22c main + 940
> 25 libdyld.dylib 0x7fffdec32255 start + 1
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Closed] (HAWQ-1372) doc ambari hawq config change procedure that does not require cluster restart
[ https://issues.apache.org/jira/browse/HAWQ-1372?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Lisa Owen closed HAWQ-1372. --- > doc ambari hawq config change procedure that does not require cluster restart > - > > Key: HAWQ-1372 > URL: https://issues.apache.org/jira/browse/HAWQ-1372 > Project: Apache HAWQ > Issue Type: Improvement > Components: Documentation >Reporter: Lisa Owen >Assignee: David Yozie >Priority: Minor > Fix For: 2.2.0.0-incubating > > > document the workaround for updating hawq configuration via ambari (for > ambari-managed clusters) in cases where a complete cluster restart cannot be > tolerated: > update config via ambari, do not restart > update config via "hawq config -c xxx -v xxx" > hawq stop cluster --reload -- This message was sent by Atlassian JIRA (v6.3.15#6346)
[jira] [Resolved] (HAWQ-1372) doc ambari hawq config change procedure that does not require cluster restart
[ https://issues.apache.org/jira/browse/HAWQ-1372?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Lisa Owen resolved HAWQ-1372. - Resolution: Fixed Fix Version/s: 2.2.0.0-incubating PR merged; resolving and closing. > doc ambari hawq config change procedure that does not require cluster restart > - > > Key: HAWQ-1372 > URL: https://issues.apache.org/jira/browse/HAWQ-1372 > Project: Apache HAWQ > Issue Type: Improvement > Components: Documentation >Reporter: Lisa Owen >Assignee: David Yozie >Priority: Minor > Fix For: 2.2.0.0-incubating > > > document the workaround for updating hawq configuration via ambari (for > ambari-managed clusters) in cases where a complete cluster restart cannot be > tolerated: > update config via ambari, do not restart > update config via "hawq config -c xxx -v xxx" > hawq stop cluster --reload -- This message was sent by Atlassian JIRA (v6.3.15#6346)
[jira] [Resolved] (HAWQ-1376) docs - better describe the pxf host and port settings
[ https://issues.apache.org/jira/browse/HAWQ-1376?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Lisa Owen resolved HAWQ-1376. - Resolution: Fixed Fix Version/s: 2.2.0.0-incubating PR merged; resolving and closing. > docs - better describe the pxf host and port settings > - > > Key: HAWQ-1376 > URL: https://issues.apache.org/jira/browse/HAWQ-1376 > Project: Apache HAWQ > Issue Type: Bug > Components: Documentation >Reporter: Lisa Owen >Assignee: David Yozie > Fix For: 2.2.0.0-incubating > > > the pxf host and port settings as described some places in the docs as hdfs > namenode and port. this is confusing - the host does not have to be the > namenode and the port should be the pxf port. > clarify the docs in this area. -- This message was sent by Atlassian JIRA (v6.3.15#6346)
[jira] [Closed] (HAWQ-1376) docs - better describe the pxf host and port settings
[ https://issues.apache.org/jira/browse/HAWQ-1376?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Lisa Owen closed HAWQ-1376. --- > docs - better describe the pxf host and port settings > - > > Key: HAWQ-1376 > URL: https://issues.apache.org/jira/browse/HAWQ-1376 > Project: Apache HAWQ > Issue Type: Bug > Components: Documentation >Reporter: Lisa Owen >Assignee: David Yozie > Fix For: 2.2.0.0-incubating > > > the pxf host and port settings as described some places in the docs as hdfs > namenode and port. this is confusing - the host does not have to be the > namenode and the port should be the pxf port. > clarify the docs in this area. -- This message was sent by Atlassian JIRA (v6.3.15#6346)
[jira] [Commented] (HAWQ-1372) doc ambari hawq config change procedure that does not require cluster restart
[ https://issues.apache.org/jira/browse/HAWQ-1372?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15905739#comment-15905739 ] ASF GitHub Bot commented on HAWQ-1372: -- Github user lisakowen closed the pull request at: https://github.com/apache/incubator-hawq-docs/pull/97 > doc ambari hawq config change procedure that does not require cluster restart > - > > Key: HAWQ-1372 > URL: https://issues.apache.org/jira/browse/HAWQ-1372 > Project: Apache HAWQ > Issue Type: Improvement > Components: Documentation >Reporter: Lisa Owen >Assignee: David Yozie >Priority: Minor > > document the workaround for updating hawq configuration via ambari (for > ambari-managed clusters) in cases where a complete cluster restart cannot be > tolerated: > update config via ambari, do not restart > update config via "hawq config -c xxx -v xxx" > hawq stop cluster --reload -- This message was sent by Atlassian JIRA (v6.3.15#6346)
[jira] [Comment Edited] (HAWQ-1270) Plugged storage back-ends for HAWQ
[
https://issues.apache.org/jira/browse/HAWQ-1270?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15901627#comment-15901627
]
Kyle R Dunn edited comment on HAWQ-1270 at 3/10/17 9:27 PM:
>From what I can tell, [this |
>https://github.com/apache/incubator-hawq/blob/master/src/bin/gpfilesystem/hdfs/gpfshdfs.c]
> IS the interface.
When you look at the {{pg_filesystems}} table, it lists the exact functions
required for a new backend:
{code}
SELECT * from pg_filesystem ;
-[ RECORD 1 ]--+--
fsysname | hdfs
fsysconnfn | gpfs_hdfs_connect
fsysdisconnfn | gpfs_hdfs_disconnect
fsysopenfn | gpfs_hdfs_openfile
fsysclosefn| gpfs_hdfs_closefile
fsysseekfn | gpfs_hdfs_seek
fsystellfn | gpfs_hdfs_tell
fsysreadfn | gpfs_hdfs_read
fsyswritefn| gpfs_hdfs_write
fsysflushfn| gpfs_hdfs_sync
fsysdeletefn | gpfs_hdfs_delete
fsyschmodfn| gpfs_hdfs_chmod
fsysmkdirfn| gpfs_hdfs_createdirectory
fsystruncatefn | gpfs_hdfs_truncate
fsysgetpathinfofn | gpfs_hdfs_getpathinfo
fsysfreefileinfofn | gpfs_hdfs_freefileinfo
fsyslibfile| $libdir/gpfshdfs.so
fsysowner | 10
fsystrusted| f
fsysacl|
{code}
was (Author: kdunn926):
>From what I can tell, [this |
>https://github.com/apache/incubator-hawq/blob/master/src/bin/gpfilesystem/hdfs/gpfshdfs.c]
> IS the interface.
When you look at the {{pg_filesystems}} table, it lists the exact functions
requires for a new backend:
{code}
SELECT * from pg_filesystem ;
-[ RECORD 1 ]--+--
fsysname | hdfs
fsysconnfn | gpfs_hdfs_connect
fsysdisconnfn | gpfs_hdfs_disconnect
fsysopenfn | gpfs_hdfs_openfile
fsysclosefn| gpfs_hdfs_closefile
fsysseekfn | gpfs_hdfs_seek
fsystellfn | gpfs_hdfs_tell
fsysreadfn | gpfs_hdfs_read
fsyswritefn| gpfs_hdfs_write
fsysflushfn| gpfs_hdfs_sync
fsysdeletefn | gpfs_hdfs_delete
fsyschmodfn| gpfs_hdfs_chmod
fsysmkdirfn| gpfs_hdfs_createdirectory
fsystruncatefn | gpfs_hdfs_truncate
fsysgetpathinfofn | gpfs_hdfs_getpathinfo
fsysfreefileinfofn | gpfs_hdfs_freefileinfo
fsyslibfile| $libdir/gpfshdfs.so
fsysowner | 10
fsystrusted| f
fsysacl|
{code}
> Plugged storage back-ends for HAWQ
> --
>
> Key: HAWQ-1270
> URL: https://issues.apache.org/jira/browse/HAWQ-1270
> Project: Apache HAWQ
> Issue Type: Improvement
>Reporter: Dmitry Buzolin
>Assignee: Ed Espino
>
> Since HAWQ only depends on Hadoop and Parquet for columnar format support, I
> would like to propose pluggable storage backend design for Hawq. Hadoop is
> already supported but there is Ceph - a distributed, storage system which
> offers standard Posix compliant file system, object and a block storage. Ceph
> is also data location aware, written in C++. and is more sophisticated
> storage backend compare to Hadoop at this time. It provides replicated and
> erasure encoded storage pools, Other great features of Ceph are: snapshots
> and an algorithmic approach to map data to the nodes rather than having
> centrally managed namenodes. I don't think HDFS offers any of these features.
> In terms of performance, Ceph should be faster than HFDS since it is written
> on C++ and because it doesn't have scalability limitations when mapping data
> to storage pools, compare to Hadoop, where name node is such point of
> contention.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Commented] (HAWQ-1383) docs - pl/pgsql page cleanup
[ https://issues.apache.org/jira/browse/HAWQ-1383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15905661#comment-15905661 ] ASF GitHub Bot commented on HAWQ-1383: -- 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. +## 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 +[ ] +[ DECLARE +declarations ] +BEGIN +statements +END [ label ]; +``` --- End diff -- got it > 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)
[jira] [Commented] (HAWQ-1383) docs - pl/pgsql page cleanup
[ https://issues.apache.org/jira/browse/HAWQ-1383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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. +## 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 +[ ] +[ 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 >
[jira] [Commented] (HAWQ-1383) docs - pl/pgsql page cleanup
[ https://issues.apache.org/jira/browse/HAWQ-1383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15905621#comment-15905621 ] ASF GitHub Bot commented on HAWQ-1383: -- 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. +## 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 +[ ] +[ DECLARE +declarations ] +BEGIN +statements +END [ label ]; +``` --- End diff -- Maybe add EXCEPTION block as well. > 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] [Commented] (HAWQ-1376) docs - better describe the pxf host and port settings
[ https://issues.apache.org/jira/browse/HAWQ-1376?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15905614#comment-15905614 ] ASF GitHub Bot commented on HAWQ-1376: -- Github user lisakowen closed the pull request at: https://github.com/apache/incubator-hawq-docs/pull/99 > docs - better describe the pxf host and port settings > - > > Key: HAWQ-1376 > URL: https://issues.apache.org/jira/browse/HAWQ-1376 > Project: Apache HAWQ > Issue Type: Bug > Components: Documentation >Reporter: Lisa Owen >Assignee: David Yozie > > the pxf host and port settings as described some places in the docs as hdfs > namenode and port. this is confusing - the host does not have to be the > namenode and the port should be the pxf port. > clarify the docs in this area. -- This message was sent by Atlassian JIRA (v6.3.15#6346)
[jira] [Commented] (HAWQ-1383) docs - pl/pgsql page cleanup
[ https://issues.apache.org/jira/browse/HAWQ-1383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15905607#comment-15905607 ] 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_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. +## 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 +[ ] +[ 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 `*/`.
[jira] [Commented] (HAWQ-1383) docs - pl/pgsql page cleanup
[ https://issues.apache.org/jira/browse/HAWQ-1383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15905594#comment-15905594 ] 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_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. > 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)
[jira] [Commented] (HAWQ-1383) docs - pl/pgsql page cleanup
[ https://issues.apache.org/jira/browse/HAWQ-1383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15905602#comment-15905602 ] 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_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. +## 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 +[ ] +[ 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 `*/`.
[jira] [Commented] (HAWQ-1383) docs - pl/pgsql page cleanup
[ https://issues.apache.org/jira/browse/HAWQ-1383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15905466#comment-15905466 ] ASF GitHub Bot commented on HAWQ-1383: -- Github user dyozie commented on a diff in the pull request: https://github.com/apache/incubator-hawq-docs/pull/101#discussion_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. +## 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 +[ ] +[ 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." > docs - pl/pgsql page cleanup > > > Key: HAWQ-1383 > URL:
[jira] [Commented] (HAWQ-1383) docs - pl/pgsql page cleanup
[ https://issues.apache.org/jira/browse/HAWQ-1383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15905463#comment-15905463 ] ASF GitHub Bot commented on HAWQ-1383: -- Github user dyozie commented on a diff in the pull request: https://github.com/apache/incubator-hawq-docs/pull/101#discussion_r105444363 --- Diff: markdown/plext/using_plpgsql.html.md.erb --- @@ -19,143 +19,278 @@ software distributed under the License is distributed on an KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. ---> +--> -SQL is the language of most other relational databases use as query language. It is portable and easy to learn. But every SQL statement must be executed individually by the database server. +PL/pgSQL is a trusted procedural language that is automatically installed and registered in all HAWQ databases. With PL/pgSQL, you can: -PL/pgSQL is a loadable procedural language. PL/SQL can do the following: +- Create functions +- Add control structures to the SQL language +- Perform complex computations +- Use all of the data types, functions, and operators defined in SQL -- create functions -- add control structures to the SQL language -- perform complex computations -- inherit all user-defined types, functions, and operators -- be trusted by the server +SQL is the language most relational databases use as a query language. While it is portable and easy to learn, every SQL statement is individually executed by the database server. Your client application sends each query to the database server, waits for it to be processed, receives and processes the results, does some computation, then sends further queries to the server. This back-and-forth requires interprocess communication and incurs network overhead if your client is on a different host than the HAWQ master. -You can use functions created with PL/pgSQL with any database that supports built-in functions. For example, it is possible to create complex conditional computation functions and later use them to define operators or use them in index expressions. +PL/pgSQL does not have these limitations. When creating functions with the PL/pgSQL language, you can group computation blocks and queries inside the database server, combining the power of a procedural language and the ease of use of SQL, but with considerable savings of client/server communication overhead. With PL/pgSQL: -Every SQL statement must be executed individually by the database server. Your client application must send each query to the database server, wait for it to be processed, receive and process the results, do some computation, then send further queries to the server. This requires interprocess communication and incurs network overhead if your client is on a different machine than the database server. +- Extra round trips between client and server are eliminated +- Intermediate, and perhaps unneeded, results do not have to be marshaled or transferred between the server and client +- You avoid multiple rounds of query parsing + -With PL/pgSQL, you can group a block of computation and a series of queries inside the database server, thus having the power of a procedural language and the ease of use of SQL, but with considerable savings of client/server communication overhead. +## 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 +[ ] +[ 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 `*/`.
[jira] [Commented] (HAWQ-1383) docs - pl/pgsql page cleanup
[ https://issues.apache.org/jira/browse/HAWQ-1383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15905464#comment-15905464 ] ASF GitHub Bot commented on HAWQ-1383: -- Github user dyozie commented on a diff in the pull request: https://github.com/apache/incubator-hawq-docs/pull/101#discussion_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. +## 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 +[ ] +[ 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 `*/`.
[jira] [Commented] (HAWQ-1383) docs - pl/pgsql page cleanup
[ https://issues.apache.org/jira/browse/HAWQ-1383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15905465#comment-15905465 ] ASF GitHub Bot commented on HAWQ-1383: -- Github user dyozie commented on a diff in the pull request: https://github.com/apache/incubator-hawq-docs/pull/101#discussion_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. +## 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 +[ ] +[ 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 `*/`.
[jira] [Commented] (HAWQ-1383) docs - pl/pgsql page cleanup
[ https://issues.apache.org/jira/browse/HAWQ-1383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15905462#comment-15905462 ] ASF GitHub Bot commented on HAWQ-1383: -- Github user dyozie commented on a diff in the pull request: https://github.com/apache/incubator-hawq-docs/pull/101#discussion_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. +## 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 +[ ] +[ 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 `*/`.
[jira] [Commented] (HAWQ-1383) docs - pl/pgsql page cleanup
[ https://issues.apache.org/jira/browse/HAWQ-1383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15905456#comment-15905456 ] ASF GitHub Bot commented on HAWQ-1383: -- Github user dyozie commented on a diff in the pull request: https://github.com/apache/incubator-hawq-docs/pull/101#discussion_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. +## 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 +[ ] +[ 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 `*/`.
[jira] [Commented] (HAWQ-1383) docs - pl/pgsql page cleanup
[ https://issues.apache.org/jira/browse/HAWQ-1383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15905458#comment-15905458 ] ASF GitHub Bot commented on HAWQ-1383: -- Github user dyozie commented on a diff in the pull request: https://github.com/apache/incubator-hawq-docs/pull/101#discussion_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. +## 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 +[ ] +[ 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 `*/`.
[jira] [Commented] (HAWQ-1383) docs - pl/pgsql page cleanup
[ https://issues.apache.org/jira/browse/HAWQ-1383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15905455#comment-15905455 ] ASF GitHub Bot commented on HAWQ-1383: -- Github user dyozie commented on a diff in the pull request: https://github.com/apache/incubator-hawq-docs/pull/101#discussion_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. > 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)
[jira] [Commented] (HAWQ-1383) docs - pl/pgsql page cleanup
[ https://issues.apache.org/jira/browse/HAWQ-1383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15905457#comment-15905457 ] ASF GitHub Bot commented on HAWQ-1383: -- Github user dyozie commented on a diff in the pull request: https://github.com/apache/incubator-hawq-docs/pull/101#discussion_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. +## 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 +[ ] +[ 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 `*/`.
[jira] [Commented] (HAWQ-1383) docs - pl/pgsql page cleanup
[ https://issues.apache.org/jira/browse/HAWQ-1383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15905460#comment-15905460 ] ASF GitHub Bot commented on HAWQ-1383: -- Github user dyozie commented on a diff in the pull request: https://github.com/apache/incubator-hawq-docs/pull/101#discussion_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..." > 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)
