Repository: incubator-hawq-docs Updated Branches: refs/heads/develop 3df5448c8 -> 0b4e1daf8
add file discussing hawq built-in languages Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/commit/504c662b Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/tree/504c662b Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/diff/504c662b Branch: refs/heads/develop Commit: 504c662be21dc344a161b81a9c627a8f6d7861cd Parents: e169704 Author: Lisa Owen <[email protected]> Authored: Wed Oct 5 14:33:36 2016 -0700 Committer: Lisa Owen <[email protected]> Committed: Wed Oct 5 14:33:36 2016 -0700 ---------------------------------------------------------------------- plext/builtin_langs.html.md.erb | 104 +++++++++++++++++++++++++++++++++++ 1 file changed, 104 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/blob/504c662b/plext/builtin_langs.html.md.erb ---------------------------------------------------------------------- diff --git a/plext/builtin_langs.html.md.erb b/plext/builtin_langs.html.md.erb new file mode 100644 index 0000000..8b408a9 --- /dev/null +++ b/plext/builtin_langs.html.md.erb @@ -0,0 +1,104 @@ +--- +title: Using HAWQ Built-In Languages +--- + +This section provides an introduction to using the HAWQ built-in languages. + +HAWQ supports user-defined functions created with the SQL and C built-in languages. HAWQ also supports user-defined aliases for internal functions. + + +## <a id="enablebuiltin"></a>Enabling Built-in Language Support + +Support for SQL, internal, and C language user-defined functions is enabled by default for all HAWQ databases. + +## <a id="builtinsql"></a>SQL + +SQL functions execute an arbitrary list of SQL statements. The SQL statements in the body of an SQL function must be separated by semicolons. The final statement in a non-void-returning SQL function must be a `SELECT` that returns data of the type specified by the function's return type. The function will return a single or set of rows corresponding to this last SQL query. + +The following example creates and calls an SQL function to count the number of rows of the database named `orders`: + +``` sql +gpadmin=# CREATE FUNCTION count_orders() RETURNS bigint AS $$ + SELECT count(*) FROM orders; +$$ LANGUAGE SQL; +CREATE FUNCTION +gpadmin=# select count_orders(); + my_count +---------- + 830513 +(1 row) +``` + +For additional information on creating SQL functions, refer to [Query Language (SQL) Functions](https://www.postgresql.org/docs/8.2/static/xfunc-sql.html) in the PostgreSQL documentation. + +## <a id="builtininternal"></a>Internal + +Many HAWQ internal functions are written in C. These functions are declared during initialization of the database cluster and statically linked to the HAWQ server. + +While users cannot define new internal functions, they can create aliases for existing internal functions. + +The following example creates a new function named `to_upper` that will be an alias for the `upper` internal HAWQ function: + + +``` sql +gpadmin=# CREATE FUNCTION to_upper (text) RETURNS text AS 'upper' + LANGUAGE internal STRICT; +CREATE FUNCTION +gpadmin=# SELECT to_upper('change me'); + to_upper +----------- + CHANGE ME +(1 row) + +``` + +For more information on aliasing internal functions, refer to [Internal Functions](https://www.postgresql.org/docs/8.2/static/xfunc-internal.html) in the PostgreSQL documentation. + +## <a id="builtininternal"></a>C + +User-defined functions written in C must be compiled into shared libraries to be loaded by the HAWQ server on demand. This dynamic loading distinguishes C language functions from internal functions that are written in C. + +The `CREATE FUNCTION` call for a user-defined C function must include both the name of the shared library and the name of the function. + +If an absolute path to the shared library is not provided, an attempt is made to locate the library relative to: the HAWQ PostgreSQL library directory (obtained via the `pg_config --pkglibdir` command), the `dynamic_library_path` configuration value, and the current working directory, in that order. + +Example: + +``` c +#include "postgres.h" +#include "fmgr.h" + +#ifdef PG_MODULE_MAGIC +PG_MODULE_MAGIC; +#endif + +PG_FUNCTION_INFO_V1(double_it); + +Datum +double_it(PG_FUNCTION_ARGS) +{ + int32 arg = PG_GETARG_INT32(0); + + PG_RETURN_INT64(arg + arg); +} +``` + +If the above function is compiled into a shared object named `libdoubleit.so` located in `/share/libs`, you would register and invoke the function with HAWQ as follows: + +``` sql +gpadmin=# CREATE FUNCTION double_it(integer) RETURNS integer + AS '/share/libs/libdoubleit', 'double_it' + LANGUAGE C STRICT; +CREATE FUNCTION +gpadmin=# SELECT double_it(27); + double_it +----------- + 54 +(1 row) + +``` + +The shared library `.so` extension may be omitted. + +For additional information on using the C language to create functions, refer to [C-Language Functions](https://www.postgresql.org/docs/8.2/static/xfunc-c.html) in the PostgreSQL documentation. +
