AWS redshift->hdb pg(Greenplum)， plpython, pljava UDF 以及upload library

作者

digoal

日期

2018-10-17

标签

PostgreSQL , redshift , greenplum , library , plpython , pljava , udf

背景

AWS redshift UDF 支持sql, plpython编写。

阿里云hdb pg UDF支持plpgsql, sql, plpython, pljava编写。

aws redshift通过create library导入外部LIB。

阿里云hdb pg同样通过CREATE LIBRARY导入外部LIB。

redshift 创建UDF函数

https://docs.aws.amazon.com/redshift/latest/dg/udf-creating-a-scalar-udf.html

```
CREATE [ OR REPLACE ] FUNCTION f_function_name
( { [py_arg_name py_arg_data_type |
sql_arg_data_type } [ , ... ] ] )
RETURNS data_type
{ VOLATILE | STABLE | IMMUTABLE }
AS $$
{ python_program | SELECT_clause }
$$ LANGUAGE { plpythonu | sql }

create function f_py_greater (a float, b float)
returns float
stable
as $$
if a > b:
return a
return b
$$ language plpythonu;
```

redshift 导入python lib

CREATE [ OR REPLACE ] LIBRARY library_name LANGUAGE plpythonu FROM { 'https://file_url' | 's3://bucketname/file_name' authorization [ REGION [AS] 'aws_region'] }

阿里云hdb pg创建UDF函数

https://help.aliyun.com/document_detail/50594.html

```
create extension pljava;

create library example language java from 'oss://oss-cn-hangzhou.aliyuncs.com filepath=analytics.jar id=xxx key=yyy bucket=zzz';

create table temp (a varchar) distributed randomly;
insert into temp values ('my string');
create or replace function java_substring(varchar, int, int) returns varchar as 'Test.substring' language java;
select java_substring(a, 1, 5) from temp;
```

阿里云hdb pg导入java lib

https://help.aliyun.com/document_detail/50595.html

CREATE LIBRARY library_name LANGUAGE [JAVA] FROM oss_location OWNER ownername CREATE LIBRARY library_name LANGUAGE [JAVA] VALUES file_content_hex OWNER ownername DROP LIBRARY library_name

create library example language java from 'oss://oss-cn-hangzhou.aliyuncs.com filepath=analytics.jar id=xxx key=yyy bucket=zzz';

参考

https://help.aliyun.com/document_detail/50595.html

https://help.aliyun.com/document_detail/50594.html

https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_LIBRARY.html

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.