GreenPlum(postgreSQL)自定义查询函数
具体可以参考postgreSQL的官网:自定义函数and others
1 基本语法如下
CREATE [OR REPLACE] FUNCTION name
( [ [argmode] [argname] argtype [ { DEFAULT | = } defexpr ] [, ...] ] )
[ RETURNS { [ SETOF ] rettype
| TABLE ([{ argname argtype | LIKE other table }
[, ...]])
} ]
{ LANGUAGE langname
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER
| COST execution_cost
| SET configuration_parameter { TO value | = value | FROM CURRENT }
| AS 'definition'
| AS 'obj_file', 'link_symbol' } ...
[ WITH ({ DESCRIBE = describe_function
} [, ...] ) ]
gp的自定义函数可以接收基本数据类型和复合类型的参数、也可以返回基本数据类型和符合类型的值,查询函数只能在查询语句中使用,实际上gp还支持c语言函数、程序函数、内置函数等4种类型的函数自定义,具体可以参考pg函数,这里我们只讨论 select query function,用在查询语句中。函数默认返回
具体用法如下:
--创建一个函数clean_up(),这是一个不需要参数的函数,也没有返回值,language指定为SQL
CREATE FUNCTION clean_emp() RETURNS void AS '
DELETE FROM emp
WHERE salary < 0;
' LANGUAGE SQL;
--使用自定义函数查询
SELECT clean_emp();
clean_emp
-----------
(1 row)
1.1 自定义函数要求
pg的自定函数除了关键字以外,函数主体需要被包含在**’ '中或者**,
--以下2种方式都可以创建一个函数
CREATE FUNCTION one() RETURNS integer AS $$
SELECT 1 AS result;
$$ LANGUAGE SQL;
-- Alternative syntax for string literal:
CREATE FUNCTION one() RETURNS integer AS '
SELECT 1 AS result;
' LANGUAGE SQL;
SELECT one();
2 自定义查询函数的类型
2.1 基本数据类型
--只返回查询的第一行
CREATE FUNCTION test_add(v1 integer,v2 integer) returns integer AS $$
SELECT $1 + $2; --其中$1代表第一个参数
$$ LANGUAGE SQL;
SELECT test_ass(1,2)
>>3
2.2 复合数据类型
--通过表来定义一个复合类型
CREATE TABLE fake_body(
name varchar,
id int2,
salary numeric
);
--创建函数
CREATE FUNCTION new_fake_body() RETURNS fake_body --OUT声明的是返回的类型
AS $$
SELECT
"none" AS name,
12 AS id,
12.2 AS salary;
--or
SELECT ROW("none",12,12.3)::fake_body;
$$ LANGUAGE SQL;
--使用函数,返回的参数不需要指定
SELECT new_fake_body();
2.3 带输出参数的类型
CREATE FUNCTION FUNC1(IN a integer,IN b integer,OUT c integer,OUT d long) AS $$
SELECT a+b , a*b;
$$ LANGUAGE SQL;
SELECT func1(1,2);
>>>>>>
3 | 2
2.4 以table为数据源的类型,类似于子查询
CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES (1, 1, 'Joe');
INSERT INTO foo VALUES (1, 2, 'Ed');
INSERT INTO foo VALUES (2, 1, 'Mary');
CREATE FUNCTION getfoo(int) RETURNS foo AS $$
SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
SELECT *, upper(fooname) FROM getfoo(1) AS t1;
>>>>>>>>>>>>>>>>>>>>
fooid | foosubid | fooname | upper
-------+----------+---------+-------
1 | 1 | Joe | JOE
(1 row)
2.5 返回数据集Sets的类型
如果在创建函数的时候,不指定RETURNS SETOF record
,那么默认值返回查询到的第一行数据,如果没有就是null,如果我们只需要查询一个字段的多行,我们只需要将record换成该行的数据类型如RETURNS SETOF integer
。
CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
>>>>>>>>>>>>
fooid | foosubid | fooname
-------+----------+---------
1 | 1 | Joe
1 | 2 | Ed
(2 rows)
--显示所有name的子集
SELECT * FROM nodes;
name | parent
-----------+--------
Top |
Child1 | Top
Child2 | Top
Child3 | Top
SubChild1 | Child1
SubChild2 | Child1
(6 rows)
CREATE FUNCTION listchildren(text) RETURNS SETOF text AS $$
SELECT name FROM nodes WHERE parent = $1
$$ LANGUAGE SQL STABLE;
SELECT * FROM listchildren('Top');
listchildren
--------------
Child1
Child2
Child3
(3 rows)
SELECT name, child FROM nodes, LATERAL listchildren(name) AS child;
name | child
--------+-----------
Top | Child1
Top | Child2
Top | Child3
Child1 | SubChild1
Child1 | SubChild2
(5 rows)
2.6 接收、返回多态类型anyelement,anyarray,anynoarray,anyenum
需要返回一个多态类型的参数,必须包含至少1个多态类型的入参
CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$
SELECT $1 > $2;
$$ LANGUAGE SQL;
SELECT is_greater(1, 2);
is_greater
------------
f
(1 row)
CREATE FUNCTION invalid_func() RETURNS anyelement AS $$
SELECT 1;
$$ LANGUAGE SQL;
ERROR: cannot determine result data type
DETAIL: A function returning a polymorphic type must have at least one polymorphic argument.
CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray)
AS 'select $1, array[$1,$1]' LANGUAGE sql;
SELECT * FROM dup(22);
f2 | f3
----+---------
22 | {22,22}
(1 row)
3 自定义组合查询函数
CREATE FUNCTION func() RETURNS [SETOF] record AS $$
DECLARE --声明光标(变量),可以在这里初始化,也可以不初始化 :DECLARE
name varchar;
id int2;
salary := 2020020;
a int2;
b int2;
--开始函数的方法体
BEGIN
--这里使用声明的变量
FOR a,b IN (SELECT a,b FROM table_name);
--然后对a,b的值进行循环
LOOP
--这个循环是针对上面的FOR的,可以通过RAISE NOTICE + % 的方式进行提示,还可以使用,输出等,%是占位符
RAISE NOTICE 'a = % b = %' ,a,b ;
--在函数体中任何位置还可以使用IF ..THEN.. END IF;
IF 1=1 THEN
a=1;
[ELSE
a=2;]
END IF;
--在循环中还可以嵌套循环
END LOOP; --结束循环,每个循环对应一个结束的标记 :END LOOP;
RETURN;--跳出函数
END;--结束方法体,
$$ LANGUAGE plpgsql
SET statement_mem='1024MB' --设置该函数的内存
--VOLATILE EXECUTE ON ALL SEGMENTS --在所有的segment上执行
; --标记function的语言种类